MySQL8.0锁等待排查的实现_Mysql

前言

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 是精简过的，只保留了常用的字段：

select engine_transaction_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;

session 1：锁定 test_semi 全表。

begin;
select * from test_semi for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |    1 |  123 |
| 11 |    2 |  123 |
| 12 |    1 |  123 |
| 13 |    2 |  123 |
| 14 |    1 |  123 |
+----+------+------+

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

engine_transaction_id	object_schema	object_name	index_name	lock_type	lock_mode	lock_status	lock_data
6711	test	test_semi	null	table	ix	granted	null
6711	test	test_semi	primary	record	x,rec_not_gap	granted	10
6711	test	test_semi	primary	record	x,rec_not_gap	granted	11
6711	test	test_semi	primary	record	x,rec_not_gap	granted	12
6711	test	test_semi	primary	record	x,rec_not_gap	granted	13
6711	test	test_semi	primary	record	x,rec_not_gap	granted	14

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：阻塞锁在内存中的地址。

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

select 
   trx.trx_id as waiting_trx_id,
   trx.trx_mysql_thread_id as waiting_thread_id,
   trx.trx_state as waiting_trx_state,
   trx.trx_query as waiting_query,
   lk.blocking_engine_transaction_id as blocking_trx_id,
   lk.blocking_thread_id as blocking_thread_id,
   trx.trx_wait_started as trx_wait_started,
   timestampdiff(second, trx.trx_wait_started, current_timestamp) as wait_second
from 
  performance_schema.data_lock_waits as lk 
  join information_schema.innodb_trx as trx on lk.requesting_engine_transaction_id = trx.trx_id;

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 表的使用难度，该库里面提供一个视图，可以查到非常详细的锁堵塞信息。

*************************** 1. row ***************************
                wait_started: 2024-08-06 15:37:36
                    wait_age: 00:00:11
               wait_age_secs: 11
                locked_table: `test`.`test_semi`
         locked_table_schema: test
           locked_table_name: test_semi
      locked_table_partition: null
   locked_table_subpartition: null
                locked_index: primary
                 locked_type: record
              waiting_trx_id: 421847145074688
         waiting_trx_started: 2024-08-06 15:37:36
             waiting_trx_age: 00:00:11
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 818473
               waiting_query: select * from test_semi for share
             waiting_lock_id: 140372168364032:10:4:2:140372080646752
           waiting_lock_mode: s,rec_not_gap
             blocking_trx_id: 6711
                blocking_pid: 819104
              blocking_query: null
            blocking_lock_id: 140372168364840:10:4:2:140372080652768
          blocking_lock_mode: x,rec_not_gap
        blocking_trx_started: 2024-08-06 14:35:20
            blocking_trx_age: 01:02:27
    blocking_trx_rows_locked: 5
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: kill query 819104
sql_kill_blocking_connection: kill 819104

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

4. 状态变量

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

innodb_row_lock_current_waits：当前正在等待行锁的操作数。
innodb_row_lock_time：获取行锁花费的总时间，单位毫秒。
innodb_row_lock_time_avg：获取行锁花费的平均时间，单位毫秒。
innodb_row_lock_time_max：获取行锁花费的最大时间，单位毫秒。

下面我们来做一个实验：

root@mysql 14:38:  [(none)]>show status like '%innodb_row_lock%';
+-------------------------------+-------+
| variable_name                 | value |
+-------------------------------+-------+
| innodb_row_lock_current_waits | 0     |
| innodb_row_lock_time          | 33165 |
| innodb_row_lock_time_avg      | 16582 |
| innodb_row_lock_time_max      | 28845 |
| innodb_row_lock_waits         | 2     |
+-------------------------------+-------+

session 1	session 2
begin;
delete from score where id = 5;
	update score set number = 66 where id = 5; – 等待行锁

root@mysql 14:41:  [test]>show status like '%innodb_row_lock%';
+-------------------------------+-------+
| variable_name                 | value |
+-------------------------------+-------+
| innodb_row_lock_current_waits | 1     |
| innodb_row_lock_time          | 33165 |
| innodb_row_lock_time_avg      | 11055 |
| innodb_row_lock_time_max      | 28845 |
| innodb_row_lock_waits         | 3     |
+-------------------------------+-------+

此时可以发现 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