MySQL8.0锁期待排查的实现

目录

• 前言
• 1. data_locks
• 2. data_lock_waits
• 3. sys.innodb_lock_waits
• 4. 状态变量
• 5. 状态变量 bug
• 总结

前言

MySQL 5.7 版本的时间锁期待排查用的元数据，重要存储在 information_schema 库下的 INNODB_LOCKS 和 INNODB_LOCK_WAITS 表，8.0 版本这两张表删除了，在 performance_schema 提供新的锁相关的表，本篇文章将联合这些改动，先容 MySQL 8.0 版本如何排查锁期待题目。

1. data_locks

performance_schema 库中的 data_locks 可以观测 MySQL 中的锁，对于 InnoDB 引擎可以观测到表锁、行锁、Gap 锁、Next-key 锁。值得注意的是 data_locks 表无论锁是否处理期待状态，都会记录，以是有利于用户通过该表测试 MySQL 的加锁逻辑。

• ENGINE：持有锁的存储引擎。
• ENGINE_LOCK_ID：内部格式，用户可忽略。
• ENGINE_TRANSACTION_ID：事件 ID 可以与 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段关联起来。
• THREAD_ID：创建锁的线程 ID，一般用不着，通过事件 ID 就可以定位到会话连接。
• EVENT_ID：与 THREAD_ID 组合利用，可以从 events 表中查到 SQL 语句。
• OBJECT_SCHEMA：锁定的数据库名称。
• OBJECT_NAME：锁定表的名称。
• PARTITION_NAME：锁定分区的名称，如果不是分区表为 NULL。
• SUBPARTITION_NAME：锁定子分区的名称，如果不是分区表为 NULL。
• INDEX_NAME：索引的名称。
• OBJECT_INSTANCE_BEGIN：锁在内存中的地点。
• LOCK_TYPE：锁的范例，对于 InnoDB，允许的值是 RECORD 行级锁， TABLE 对于表级锁。
• LOCK_MODE：锁的举动，用来标记是意向锁、写锁、读锁、间隙锁、Next-key 锁。
• LOCK_STATUS：锁哀求的状态，对于 InnoDB 引擎有 GRANTED 已持有和 WAITING 正在期待锁，两种状态。
• LOCK_DATA：如果是在主键加锁，表现主键值，如果是二级索引加锁表现二级索引的值和对应主键的值。

下面的 SQL 是精简过的，只保存了常用的字段：

Session 1：锁定 test_semi 全表。

Session 2：由下表可以看出 test_semi 表中每行都在主键上加写锁，在 test_semi 表上参加 IX 意向锁。

2. data_lock_waits

performance_schema 库中的 data_lock_waits 表可以观测锁期待的情况，只有发生堵塞的时间才会记录。如果你发现这张表的记录很多，分析目前数据库有很多锁期待的情况。

• ENGINE：存储引擎。
• REQUESTING_ENGINE_LOCK_ID：存储引擎哀求的锁的 ID。
• REQUESTING_ENGINE_TRANSACTION_ID：被堵塞的事件 ID 可以与 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段关联起来。
• REQUESTING_THREAD_ID：哀求锁的会话的线程 ID。
• REQUESTING_EVENT_ID：在哀求锁的会话中导致锁哀求的性能模式事件。
• REQUESTING_OBJECT_INSTANCE_BEGIN：哀求的锁在内存中的地点。
• BLOCKING_ENGINE_LOCK_ID：壅闭锁的 ID，可以与 data_locks 表的 ENGINE_LOCK_ID 字段举行关联。
• BLOCKING_ENGINE_TRANSACTION_ID：持有锁的事件 ID 可以与 INFORMATION_SCHEMA INNODB_TRX 表的 trx_id 字段关联起来。
• BLOCKING_THREAD_ID：持有壅闭锁的会话的线程 ID。
• BLOCKING_EVENT_ID：导致持有该锁的会话中出现壅闭锁的性能模式事件。
• BLOCKING_OBJECT_INSTANCE_BEGIN：壅闭锁在内存中的地点。

基于该表与事件表的关联可以得到当前堵塞的事件信息：

• waiting_trx_id：被堵塞的事件 ID。
• waiting_thread_id：被堵塞的线程 ID。
• waiting_trx_state：被堵塞事件的状态。
• waiting_query：被堵塞事件的语句。
• blocking_trx_id：堵塞该事件的事件 ID。
• blocking_thread_id：堵塞该事件的线程 ID，如果查询返回很多行，且大部分该值都相同，分析堵塞源都相同，可通过该 ID 查到会话 ID 并 kill 掉。
• trx_wait_started：被堵塞事件的开始时间。
• wait_second：锁堵塞的时间长，单元为秒。

3. sys.innodb_lock_waits

sys 库内里大部分表都是视图，MySQL 创建该库的缘故起因是为了简化 performance_schema 表的利用难度，该库内里提供一个视图，可以查到非常详细的锁堵塞信息。

结果聚集中还给出 kill 掉堵塞会话的 SQL，不过在云数据库上面 sys 库一般都没有给用户权限。

4. 状态变量

可通过下方状态变量相识数据库中的行锁信息：

• Innodb_row_lock_current_waits：当前正在期待行锁的操作数。
• Innodb_row_lock_time：获取行锁花费的总时间，单元毫秒。
• Innodb_row_lock_time_avg：获取行锁花费的平均时间，单元毫秒。
• Innodb_row_lock_time_max：获取行锁花费的最大时间，单元毫秒。

下面我们来做一个实验：

此时可以发现 Innodb_row_lock_waits 和 Innodb_row_lock_current_waits 都增长了，time 相关的变量需要等事件竣过后才会举行计算。

5. 状态变量 bug

Innodb_row_lock_current_waits 从文档形貌来看，反映的是当前数据库行锁的操作数，不过该值有时会出现不准的情况。有位研发问我某云的监控上表现当前数据库的行锁有 20 亿个，当前数据库还正常吗？当时吓了一跳，会话没有任何异常，而且利用刚才先容的锁排查方法，都没有异常。末了发现监控采集的是 Innodb_row_lock_current_waits 的值，末了发现该值非常不准，有 Bug，以是各人如果碰到此类题目，可以先忽略，自制锁期待监控可以查 data_lock_waits 表，但是频次不发起太高。

Innodb_row_lock_current_waits Bug：https://bugs.mysql.com/bug.php?id=71520

总结

MySQL 5.7 一些锁监控表，在 8.0 都发生了厘革，不过 sys 库的 innodb_lock_waits 两个版本都通用，其实该表就是一个视图，在两个版本中的实现方式不一样，作用都相同。

到此这篇关于MySQL8.0锁期待排查的实现的文章就先容到这了,更多相关MySQL8.0锁期待排查内容请搜索脚本之家从前的文章或继续浏览下面的相关文章渴望各人以后多多支持脚本之家！