MySQL8.0 锁等待排查的问题解决_数据库_开发者

前言

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 表USdwrw无论锁是否处理等待状态，都会记录，所以有利于用户通过该表测试 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。
BLOCpythonKING_EVENT_ID：导致持有该锁的会话中出现阻塞锁的性能模式事件。
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞锁在内存中的地址。

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

select 
   trx.trx_idhttp://www.devze.com as waiting_trx_id,
   trx.trx_mysql_thread_id as waiting_thread_id,
   trx.trx_state as waiting_trphpx_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 表，但是频次不建议太高。