编程 blocking_query 是 NULL、一个等待事务出现两行:MySQL 8 的锁等待链到底怎么读

2026-11-21 21:05:36

MySQL 8.x InnoDB 锁等待链排查:从 data_lock_waits 到死锁现场

MySQL 8.0 起 INFORMATION_SCHEMA.INNODB_LOCKS 和 INNODB_LOCK_WAITS 已标注废弃,锁信息要从 performance_schema.data_locks 和 data_lock_waits 取。I_S 那两张锁表本质上是查询时临时构建的快照,高并发下可能过时,也拿不到事务执行的 SQL 文本和锁层级。

先打开 instrument 和 consumer

UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'wait/lock/metadata/sql/mdl%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_transactions%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_statements%';

行级锁等待关注 wait/lock/innodb/row_lock,MDL 问题看 wait/lock/metadata/sql/mdl。

两个会话的共享锁与锁升级

先打开死锁全量打印,准备两张表:

SET GLOBAL innodb_print_all_deadlocks = ON;
CREATE TABLE Animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
CREATE TABLE Birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
INSERT INTO Animals (name,value) VALUES ("Aardvark",10);
INSERT INTO Birds (name,value) VALUES ("Buzzard",20);

客户端 A:START TRANSACTION; SELECT value FROM Animals WHERE name='Aardvark' FOR SHARE;
客户端 B:START TRANSACTION; SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE;

此时 performance_schema.data_locks 显示:

Trx_IdTableIndexDataModeStatusType
421291106147544AnimalsNULLNULLISGRANTEDTABLE
421291106147544AnimalsPRIMARY'Aardvark'S,REC_NOT_GAPGRANTEDRECORD
421291106148352BirdsNULLNULLISGRANTEDTABLE
421291106148352BirdsPRIMARY'Buzzard'S,REC_NOT_GAPGRANTEDRECORD

客户端 B 执行 UPDATE Animals SET value=30 WHERE name='Aardvark'; 后阻塞,data_lock_waits 多出一行:

REQUESTING_ENGINE_LOCK_IDREQUESTING_ENGINE_TRANSACTION_IDBLOCKING_ENGINE_LOCK_IDBLOCKING_ENGINE_TRANSACTION_ID
139816129437696:27:4:2:13981601660124043260139816129436888:27:4:2:139816016594720421291106147544

data_locks 里 43260 持有 Birds 的 S,REC_NOT_GAP,等待 Animals 的 X,REC_NOT_GAP(WAITING);421291106147544 持有 Animals 的 S,REC_NOT_GAP。

注意 B 的事务 id 从 421291106148352 变成了 43260。InnoDB 只在事务尝试修改数据库时才分配顺序事务 id,之前的 421291106148352 是只读事务的非顺序 id。

LOCK_MODE 里:S 共享锁,X 排他锁,IS / IX 是表级意向锁,GAP 是间隙锁,S,REC_NOT_GAP 表示记录锁且不含间隙。

死锁现场

如果客户端 A 这时也 UPDATE Birds,两边互等,B 收到:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

InnoDB 回滚引发死锁的事务,B 的第一个更新可以继续。死锁计数:

SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks";

SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段落会给出两个事务各自的 HOLDS THE LOCK(S) / WAITING FOR THIS LOCK TO BE GRANTED、lock_mode、space id、page no、PHYSICAL RECORD 的十六进制内容和 asc 文本,最后是 WE ROLL BACK TRANSACTION (2)。

一个等待事务会按阻塞者分行出现

三会话 A/B/C 都执行 SELECT ... FOR UPDATE:

-- A
BEGIN; SELECT a FROM t FOR UPDATE; SELECT SLEEP(100);
-- B
SELECT b FROM t FOR UPDATE;
-- C
SELECT c FROM t FOR UPDATE;

查等待链:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

结果里 B(A4,线程 6)和 C(A5,线程 7)都在等 A(A3,线程 5),C 同时在等 B 和 A。data_lock_waits 里 C 出现两行,因为它按“每个阻塞者一行”记录,不是每个等待事务一行。

更省事的写法是 sys.innodb_lock_waits:

SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

blocking_query 为 NULL 时

blocking_query 取的是 innodb_trx.trx_query,为 NULL 常见两种情况:发起阻塞查询的会话已经闲置,当前没有在跑 SQL;或者阻塞者是只读事务——只读事务用非顺序 trx id,第一次修改才分配顺序 id,innodb_trx 里能看到但 trx_query 未必有内容。

按连接往下挖:

-- 1. 取 blocking_pid,例如 6
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
-- 2. 假设拿到 THREAD_ID = 28
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28\G

不够就看该线程最近 10 条:

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

PFS 表分组与边界

performance_schema 分几组:setup 表(setup_instruments、setup_consumers)、current events(events_waits_current / events_stages_current / events_statements_current / events_transactions_current)、history(每线程最近 10 条;history_long 总量 10000 条,大小由 performance_schema_events_waits_history_size / _history_long_size 控制)、summary、instance 表(mutex_instances / rwlock_instances / cond_instances / file_instances)和 misc。

data_locks / data_lock_waits 是运行时快照,不是历史,事务结束就没了;要回溯只能靠 events_statements_history 和错误日志里的死锁记录。

InnoDB 与 PFS 的集成可查 setup_instruments 里 NAME LIKE '%innodb%'(155 行),主要关注 wait/synch/mutex/innodb/*、wait/io/file/innodb/*、stage/innodb/alter table (*)。缓冲池相关的 mutex/rwlock 不在这套范围内,SHOW ENGINE INNODB MUTEX 同样看不到。

关键字段与 MDL

data_locks 主要看 ENGINE_TRANSACTION_ID、OBJECT_SCHEMA / OBJECT_NAME、LOCK_TYPE(RECORD/TABLE)、LOCK_MODE(IX/X/GAP 等)、LOCK_DATA(被锁的具体索引值)。data_lock_waits 看 REQUESTING_ENGINE_TRANSACTION_ID 与 BLOCKING_ENGINE_TRANSACTION_ID。

MDL 锁等待分析用 metadata_locks 结合 wait/lock/metadata/sql/mdl 事件,先把开头的 instrument 打开再查。

复制全文 生成海报 MySQL InnoDB 排障 锁

推荐文章

程序员茄子在线接单