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_Id | Table | Index | Data | Mode | Status | Type |
|---|---|---|---|---|---|---|
| 421291106147544 | Animals | NULL | NULL | IS | GRANTED | TABLE |
| 421291106147544 | Animals | PRIMARY | 'Aardvark' | S,REC_NOT_GAP | GRANTED | RECORD |
| 421291106148352 | Birds | NULL | NULL | IS | GRANTED | TABLE |
| 421291106148352 | Birds | PRIMARY | 'Buzzard' | S,REC_NOT_GAP | GRANTED | RECORD |
客户端 B 执行 UPDATE Animals SET value=30 WHERE name='Aardvark'; 后阻塞,data_lock_waits 多出一行:
| REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID |
|---|---|---|---|
| 139816129437696:27:4:2:139816016601240 | 43260 | 139816129436888:27:4:2:139816016594720 | 421291106147544 |
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 打开再查。