MySQL 事务、锁与死锁诊断 100 条命令

DBA100

100 条命令系列文章专栏

MySQL 事务、锁与死锁诊断 100 条命令

前言

MySQL 请求突然卡住，先分清它是等 InnoDB 记录锁、表锁，还是等元数据锁。InnoDB 的等待链可以从 data_lock_waits 找到请求锁和挡住它的持锁事务；光看 SHOW PROCESSLIST 的状态，往往还不知道锁落在哪张表、哪个索引上。

这篇按“等待事务 → 持锁事务 → 锁对象 → 死锁记录”的顺序整理命令。业务报超时、批量更新堵住在线请求时，直接翻对应位置即可。建议收藏，也欢迎分享给负责事务代码的同事。

MySQL InnoDB 锁等待链示意图 MySQL InnoDB 锁等待链示意图

示例采用 MySQL 8.4、InnoDB。锁视图是实时截面；查询 INNODB_TRX 需要 PROCESS 权限。结束事务或杀连接会回滚业务工作，必须确认影响范围后再执行。

先看谁在等

1. 查看当前连接和运行状态

SQL

1SHOW FULL PROCESSLIST;

先记录连接 ID、用户、来源和当前 SQL。Sleep 不等于没有影响：连接可能有未提交的事务，仍持有锁；下一条要看 InnoDB 事务状态。

2. 查看 InnoDB 当前事务

SQL

1SELECT trx_id, trx_state, trx_started, trx_wait_started,
2       trx_mysql_thread_id, trx_rows_locked, trx_rows_modified,
3       trx_query
4FROM information_schema.INNODB_TRX
5ORDER BY trx_started;

trx_state='LOCK WAIT' 说明事务在等锁。trx_query 是当前执行的语句，持锁者若已经空闲可能为 NULL；trx_mysql_thread_id 才是客户端连接 ID。

3. 查看正在等待的 InnoDB 数据锁

SQL

1SELECT ENGINE_TRANSACTION_ID, THREAD_ID,
2       OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
3       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
4FROM performance_schema.data_locks
5WHERE LOCK_STATUS = 'WAITING';

LOCK_TYPE='RECORD' 是记录相关锁，TABLE 是 InnoDB 表级意向锁等。LOCK_DATA 可能为空，也可能显示 supremum pseudo-record；不能把它当作每次都有的业务主键值。

4. 查看请求事务与阻塞事务的对应关系

SQL

1SELECT REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
2       BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
3       REQUESTING_THREAD_ID AS waiting_thread,
4       BLOCKING_THREAD_ID AS blocking_thread
5FROM performance_schema.data_lock_waits;

一笔请求可能被多把已持有的锁挡住，结果是多对多关系。这里的 THREAD_ID 是 Performance Schema 内部线程 ID，不是 SHOW PROCESSLIST 的连接 ID。

5. 把等待锁和持有锁的对象对在一起

SQL

1SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
2       w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
3       r.OBJECT_SCHEMA, r.OBJECT_NAME, r.INDEX_NAME,
4       r.LOCK_TYPE AS requested_type, r.LOCK_MODE AS requested_mode,
5       b.LOCK_MODE AS blocking_mode, r.LOCK_DATA
6FROM performance_schema.data_lock_waits AS w
7JOIN performance_schema.data_locks AS r
8  ON r.ENGINE = w.ENGINE
9 AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
10JOIN performance_schema.data_locks AS b
11  ON b.ENGINE = w.ENGINE
12 AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

锁 ID 格式是内部实现，不能解析字符串猜页号或记录号。用官方给出的键把两张视图关联，再结合表、索引和锁模式判断冲突。

6. 把 Performance Schema 线程 ID 换成连接 ID

SQL

1SELECT w.REQUESTING_THREAD_ID, rt.PROCESSLIST_ID AS waiting_conn,
2       w.BLOCKING_THREAD_ID, bt.PROCESSLIST_ID AS blocking_conn
3FROM performance_schema.data_lock_waits AS w
4LEFT JOIN performance_schema.threads AS rt
5  ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
6LEFT JOIN performance_schema.threads AS bt
7  ON bt.THREAD_ID = w.BLOCKING_THREAD_ID;

拿到客户端连接 ID 后，才方便和第 1 条、应用连接池日志对号。线程可能已结束，所以使用左连接；空连接 ID 不应直接当成锁记录错误。

找持锁事务

7. 查等待与持锁事务的开始时间

SQL

1SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
2       wt.TRX_STARTED AS waiting_started,
3       wt.TRX_WAIT_STARTED AS wait_started,
4       w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
5       bt.TRX_STARTED AS blocking_started,
6       bt.TRX_STATE AS blocking_state
7FROM performance_schema.data_lock_waits AS w
8LEFT JOIN information_schema.INNODB_TRX AS wt
9  ON wt.TRX_ID = w.REQUESTING_ENGINE_TRANSACTION_ID
10LEFT JOIN information_schema.INNODB_TRX AS bt
11  ON bt.TRX_ID = w.BLOCKING_ENGINE_TRANSACTION_ID;

事务开始很早不代表等锁同样久，看 TRX_WAIT_STARTED。两张实时视图在并发更新时可能出现短暂不一致；结果缺一侧时重新取样，不要立即决定终止哪个事务。

8. 用 sys 视图快速列出 InnoDB 等待链

SQL

1SELECT wait_started, wait_age,
2       locked_table_schema, locked_table_name,
3       waiting_pid, blocking_pid, waiting_query, blocking_query
4FROM sys.innodb_lock_waits;

sys 视图把连接 ID、SQL 和等待年龄放在一起，适合值班快速看。仍要用 data_lock_waits 与 data_locks 回查锁对象和多对多关系。

9. 查看最新一次 InnoDB 死锁记录

SQL

1SHOW ENGINE INNODB STATUS;

找到 LATEST DETECTED DEADLOCK 段，比较两笔事务访问对象的先后顺序和被回滚的一方。这里通常只保留最新一次检测结果；如果死锁反复出现，要提前留存日志与现场 SQL。

10. 查看锁等待与死锁检测参数

SQL

1SHOW VARIABLES WHERE Variable_name IN
2  ('innodb_lock_wait_timeout', 'innodb_deadlock_detect',
3   'innodb_print_all_deadlocks');

死锁检测与普通等锁超时是两回事。innodb_print_all_deadlocks 影响所有死锁是否写入错误日志；若关闭，单靠第 9 条很容易丢失较早的案例。

DDL 卡住时查元数据锁

11. 列出未取得的元数据锁

SQL

1SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
2       LOCK_TYPE, LOCK_DURATION, OWNER_THREAD_ID
3FROM performance_schema.metadata_locks
4WHERE LOCK_STATUS = 'PENDING';

ALTER TABLE 一直等，不一定是 InnoDB 行锁。PENDING 表示元数据锁还没拿到；先看对象，再追请求线程。若这张表始终没有记录，检查 wait/lock/metadata/sql/mdl 是否启用了采集。

12. 查看同一张表上已经持有的元数据锁

SQL

1SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE,
2       LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID
3FROM performance_schema.metadata_locks
4WHERE OBJECT_TYPE = 'TABLE'
5  AND OBJECT_SCHEMA = 'appdb'
6  AND OBJECT_NAME = 'orders';

把库名、表名换成正在卡住的对象。GRANTED 只是已获锁，并不表示每一行都与等待请求冲突；还要结合锁类型和连接上的事务判断。

13. 把等待元数据锁的内部线程号换成连接 ID

SQL

1SELECT m.OBJECT_SCHEMA, m.OBJECT_NAME, m.LOCK_TYPE,
2       t.PROCESSLIST_ID, t.PROCESSLIST_USER,
3       t.PROCESSLIST_HOST, t.PROCESSLIST_INFO
4FROM performance_schema.metadata_locks AS m
5LEFT JOIN performance_schema.threads AS t
6  ON t.THREAD_ID = m.OWNER_THREAD_ID
7WHERE m.LOCK_STATUS = 'PENDING';

这里仍是内部 OWNER_THREAD_ID 与客户端 PROCESSLIST_ID 两套编号。拿到后者，才能去应用日志里找这条 DDL 请求。

14. 用 sys 视图看表元数据锁等待链

SQL

1SELECT object_schema, object_name, waiting_pid,
2       waiting_query, waiting_query_secs,
3       blocking_pid, blocking_lock_type,
4       blocking_lock_duration
5FROM sys.schema_table_lock_waits;

这张视图直接给出被挡住的连接和持锁连接。只针对表元数据锁；行锁仍查 sys.innodb_lock_waits。blocking_pid 可能是空闲连接，别因为当前 SQL 为空就排除它。

15. 查阻塞 DDL 的连接是否有未提交事务

SQL

1SELECT w.object_schema, w.object_name, w.blocking_pid,
2       t.TRX_ID, t.TRX_STARTED, t.TRX_STATE,
3       t.TRX_ROWS_LOCKED, t.TRX_QUERY
4FROM sys.schema_table_lock_waits AS w
5LEFT JOIN information_schema.INNODB_TRX AS t
6  ON t.TRX_MYSQL_THREAD_ID = w.blocking_pid;

长事务持有的表元数据锁，常常要等提交才释放。INNODB_TRX 没有记录时也不能认定无阻塞；元数据锁和 InnoDB 事务视图覆盖范围不同，回到第 12 条看锁状态。

16. 检查元数据锁采集是否启用

SQL

1SELECT NAME, ENABLED, TIMED
2FROM performance_schema.setup_instruments
3WHERE NAME = 'wait/lock/metadata/sql/mdl';

采集关闭时，第 11—13 条可能查不到真实等待。这条只读取配置；若要启用，先评估采集开销并按现场监控规范操作。

再查事务与连接

17. 查当前活跃事务的隔离级别和提交方式

SQL

1SELECT t.PROCESSLIST_ID, e.STATE, e.ACCESS_MODE,
2       e.ISOLATION_LEVEL, e.AUTOCOMMIT
3FROM performance_schema.events_transactions_current AS e
4JOIN performance_schema.threads AS t
5  ON t.THREAD_ID = e.THREAD_ID
6WHERE e.STATE = 'ACTIVE';

事务采集必须启用才有结果。这里的 TRX_ID 字段在 MySQL 8.4 未使用，不能拿它去和 INNODB_TRX.TRX_ID 对接。

18. 把空闲但仍有 InnoDB 事务的连接挑出来

SQL

1SELECT t.TRX_ID, t.TRX_STARTED, t.TRX_STATE,
2       t.TRX_MYSQL_THREAD_ID AS conn_id,
3       p.COMMAND, p.TIME, p.STATE, p.INFO
4FROM information_schema.INNODB_TRX AS t
5JOIN information_schema.PROCESSLIST AS p
6  ON p.ID = t.TRX_MYSQL_THREAD_ID
7WHERE p.COMMAND = 'Sleep'
8ORDER BY t.TRX_STARTED;

Sleep 只是连接当前没跑语句，事务可能仍占着锁。先找应用确认提交点和连接池行为，不能仅凭空闲时长直接 KILL。

19. 比较等待两侧的事务工作量

SQL

1SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
2       wt.TRX_WEIGHT AS waiting_weight,
3       wt.TRX_ROWS_MODIFIED AS waiting_modified,
4       w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
5       bt.TRX_WEIGHT AS blocking_weight,
6       bt.TRX_ROWS_MODIFIED AS blocking_modified
7FROM performance_schema.data_lock_waits AS w
8LEFT JOIN information_schema.INNODB_TRX AS wt
9  ON wt.TRX_ID = w.REQUESTING_ENGINE_TRANSACTION_ID
10LEFT JOIN information_schema.INNODB_TRX AS bt
11  ON bt.TRX_ID = w.BLOCKING_ENGINE_TRANSACTION_ID;

处理死锁时，InnoDB 倾向回滚权重较小的事务；TRX_WEIGHT 反映修改行和锁定行，但不是精确计数。人工处理普通等待不能照着权重盲选要杀的连接：持锁者可能已经改了大量业务数据，回滚时间和应用重试成本都要问清楚。

20. 看阻塞事务已经改了多少行

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_STARTED, TRX_STATE,
3       TRX_ROWS_LOCKED, TRX_ROWS_MODIFIED,
4       TRX_QUERY
5FROM information_schema.INNODB_TRX
6WHERE TRX_ROWS_MODIFIED > 0
7ORDER BY TRX_ROWS_MODIFIED DESC;

批量更新持锁时，TRX_ROWS_MODIFIED 可以帮助估计未提交工作量和回滚代价；它不是当前 SQL 的影响行数。TRX_QUERY 为空仍可能已有修改。先和应用确认事务提交边界，再判断继续等、让业务提交还是取消任务。

21. 把活跃事务与当前执行语句对上

SQL

1SELECT x.TRX_ID, x.TRX_MYSQL_THREAD_ID AS conn_id,
2       t.THREAD_ID, e.SQL_TEXT, e.EVENT_NAME
3FROM information_schema.INNODB_TRX AS x
4JOIN performance_schema.threads AS t
5  ON t.PROCESSLIST_ID = x.TRX_MYSQL_THREAD_ID
6LEFT JOIN performance_schema.events_statements_current AS e
7  ON e.THREAD_ID = t.THREAD_ID
8ORDER BY x.TRX_STARTED;

这条补第 2 条的 TRX_QUERY：连接可能刚执行完一条写入语句，当前事件已变成空值。要找上一条 SQL，需要事先启用语句历史采集；事后不能从当前视图还原整个事务。SQL_TEXT 也可能受采集长度限制。

22. 列出一个持锁事务挡住了哪些请求

SQL

1SELECT BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
2       COUNT(DISTINCT REQUESTING_ENGINE_TRANSACTION_ID)
3         AS waiting_transactions,
4       COUNT(*) AS conflicting_locks
5FROM performance_schema.data_lock_waits
6GROUP BY BLOCKING_ENGINE_TRANSACTION_ID
7ORDER BY waiting_transactions DESC;

一个大事务可能同时挡住许多连接。conflicting_locks 是等待关系行数，不等于业务请求数；优先看 waiting_transactions，再结合第 6 条把它们映射到连接与应用来源。等待链变化很快，取样后及时保存现场。

23. 按等待时长排序 InnoDB 事务

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_WAIT_STARTED,
3       TIMESTAMPDIFF(SECOND, TRX_WAIT_STARTED, NOW())
4         AS waiting_seconds,
5       TRX_QUERY
6FROM information_schema.INNODB_TRX
7WHERE TRX_STATE = 'LOCK WAIT'
8ORDER BY TRX_WAIT_STARTED;

这看的是从开始等锁到现在，不是事务总年龄。排在前面的请求可能快到应用超时，但不代表对应持锁者必须被终止；先看它等哪张表、哪把锁，再看业务是否允许取消或重试。

24. 对照请求锁与持有锁的模式

SQL

1SELECT r.OBJECT_SCHEMA, r.OBJECT_NAME, r.INDEX_NAME,
2       r.LOCK_TYPE, r.LOCK_MODE AS requested_mode,
3       b.LOCK_MODE AS granted_mode,
4       r.LOCK_DATA,
5       w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
6       w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx
7FROM performance_schema.data_lock_waits AS w
8JOIN performance_schema.data_locks AS r
9  ON r.ENGINE = w.ENGINE
10 AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
11JOIN performance_schema.data_locks AS b
12  ON b.ENGINE = w.ENGINE
13 AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
14WHERE r.OBJECT_SCHEMA = 'appdb'
15  AND r.OBJECT_NAME = 'orders';

限定到报障表，比全库扫锁更容易看清模式。MySQL 8.4 data_locks.LOCK_MODE 里的 X、S、IS、IX 及可能附带的 GAP，要结合索引、SQL 条件和隔离级别解释；LOCK_DATA 可能只是索引键的一部分，不能直接当成整行业务数据。

锁等待超时与死锁日志

25. 查看当前锁等待超时设置

SQL

1SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

这是 InnoDB 行锁等待的超时秒数；元数据锁另有等待机制。超时通常回滚当前语句，不一定结束整个事务。应用若收到超时后不主动回滚，连接还可能继续持锁；因此看到超时报错后要查事务是否仍在。

26. 检查死锁检测开关

SQL

1SHOW VARIABLES LIKE 'innodb_deadlock_detect';

默认开启时，InnoDB 会检测死锁并回滚一个事务。高并发热点上有人会关闭检测以降低检测开销，此时通常要靠较短的锁等待超时打破僵局。不要为了“消除死锁日志”直接关掉检测，先确认争用模式和业务重试策略。

27. 查看 InnoDB 行锁等待统计

SQL

1SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

累计等待次数、等待时间与当前等待数可用于判断锁问题是否持续。累计值从实例启动或统计重置时开始，不是最近五分钟的速率；记录两次读数及间隔，再对照应用超时、慢查询和当前等待链。

28. 在 SQL 可读的错误日志里检索死锁

SQL

1SELECT LOGGED, PRIORITY, ERROR_CODE, DATA
2FROM performance_schema.error_log
3WHERE DATA LIKE '%deadlock%'
4ORDER BY LOGGED DESC
5LIMIT 50;

第 9 条看最近一次死锁；若已开启全部死锁记录，这里可按时间检索错误日志事件。performance_schema.error_log 取决于日志组件配置，只保留有限的内存环形缓冲，部分部署也不向它写入相关消息。没有命中时继续查服务器实际错误日志，不能认定没有死锁。

29. 确认是否记录每一次死锁

SQL

1SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

开启后，每次 InnoDB 死锁都会写入 MySQL 错误日志，而不仅是第 28 条展示的最近一次。日志量可能增大；先读当前配置，不要随手永久开启。需要做短时取证时，按实例日志容量和变更规范安排开启、关闭。

30. 查询死锁相关错误码的语句汇总

SQL

1SELECT ERROR_NUMBER, ERROR_NAME, SQLSTATE,
2       SUM_ERROR_RAISED, SUM_ERROR_HANDLED,
3       FIRST_SEEN, LAST_SEEN
4FROM performance_schema.events_errors_summary_global_by_error
5WHERE ERROR_NAME IN ('ER_LOCK_DEADLOCK',
6                     'ER_LOCK_WAIT_TIMEOUT');

这张汇总给出实例上两类错误发生的次数与时间范围，适合判断问题是否反复出现；它不含每次死锁的 SQL 和事务双方。需要现场细节仍查错误日志或第 28 条。采集可能被关闭或重置，计数为零不能直接证明没有业务报错。

锁落在哪个索引和范围

31. 列出指定事务正在持有的锁

SQL

1SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
2       LOCK_TYPE, LOCK_MODE, LOCK_DATA
3FROM performance_schema.data_locks
4WHERE ENGINE_TRANSACTION_ID = 123456789
5  AND LOCK_STATUS = 'GRANTED'
6ORDER BY OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;

事务 ID 用第 4、7 条的 blocking_trx 替换。返回的是当前锁截面；锁多时先按表和索引找热点，不要把 LOCK_DATA 每行都当成主键。持锁事务结束后，这条当然可能查不到结果，取样应与等待链同时保存。

32. 统计持锁事务涉及的表和索引

SQL

1SELECT ENGINE_TRANSACTION_ID AS trx_id,
2       OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
3       LOCK_TYPE, LOCK_MODE, COUNT(*) AS lock_rows
4FROM performance_schema.data_locks
5WHERE LOCK_STATUS = 'GRANTED'
6  AND ENGINE_TRANSACTION_ID = 123456789
7GROUP BY ENGINE_TRANSACTION_ID, OBJECT_SCHEMA,
8         OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE
9ORDER BY lock_rows DESC;

这只是 Performance Schema 锁记录行数，不等于被锁业务记录的精确数量。若一个范围更新在多个索引上留下锁，先核对查询条件和索引使用情况；真正的行数仍需结合 INNODB_TRX.TRX_ROWS_LOCKED 与业务 SQL 判断。

33. 找等待锁是否落在分区上

SQL

1SELECT OBJECT_SCHEMA, OBJECT_NAME,
2       PARTITION_NAME, SUBPARTITION_NAME,
3       INDEX_NAME, LOCK_MODE, LOCK_DATA,
4       ENGINE_TRANSACTION_ID
5FROM performance_schema.data_locks
6WHERE LOCK_STATUS = 'WAITING'
7  AND OBJECT_SCHEMA = 'appdb'
8  AND OBJECT_NAME = 'orders';

分区表报锁等待时，先确认请求落在哪个分区；PARTITION_NAME 为空可能是非分区对象或该锁没有分区信息。即便命中单个分区，也不能据此认定 SQL 做了分区裁剪，后续还要看执行计划。

34. 筛出等待中的 gap 相关锁

SQL

1SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA,
2       OBJECT_NAME, INDEX_NAME, LOCK_MODE, LOCK_DATA
3FROM performance_schema.data_locks
4WHERE LOCK_STATUS = 'WAITING'
5  AND LOCK_TYPE = 'RECORD'
6  AND LOCK_MODE LIKE '%GAP%';

GAP 说明锁请求涉及索引记录之间的范围，常见于范围条件和并发插入。它不直接给出 SQL 的上下界；还要对照事务隔离级别、实际索引和语句条件。没有 GAP 字样，也不能仅凭这一条排除 next-key 或其他记录锁争用。

35. 检查 supremum 伪记录上的等待

SQL

1SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
2       LOCK_MODE, LOCK_DATA, ENGINE_TRANSACTION_ID
3FROM performance_schema.data_locks
4WHERE LOCK_STATUS = 'WAITING'
5  AND LOCK_DATA = 'supremum pseudo-record';

supremum pseudo-record 是索引页边界的内部标记，不是表里真的有一条叫这个名字的记录。看到它时，重点查范围末端的插入或锁定读、索引选择以及持锁事务；不能据此直接定位某个业务主键。

36. 查自增值分配是否在等锁

SQL

1SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA,
2       OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS
3FROM performance_schema.data_locks
4WHERE LOCK_MODE = 'AUTO_INC'
5ORDER BY OBJECT_SCHEMA, OBJECT_NAME, LOCK_STATUS;

批量插入或特殊自增锁模式下，争用不一定落在行记录上。若这里有 WAITING，再查 innodb_autoinc_lock_mode、INSERT 形态及并发负载。不能因为有 AUTO_INC 已获锁记录就认定自增一定是瓶颈；要看等待和请求时长。

37. 查看等待事务的隔离级别

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_ISOLATION_LEVEL, TRX_STATE,
3       TRX_STARTED, TRX_QUERY
4FROM information_schema.INNODB_TRX
5WHERE TRX_STATE = 'LOCK WAIT';

同一条范围 SQL 在不同隔离级别下，读和写的锁行为可能不同。这里读的是事务实际隔离级别，比只看全局默认值更贴近现场；还要区分普通一致性读、锁定读和 UPDATE/DELETE，不能把所有范围查询都叫 gap lock。

38. 查目标索引的列顺序

SQL

1SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME,
2       NON_UNIQUE, CARDINALITY
3FROM information_schema.STATISTICS
4WHERE TABLE_SCHEMA = 'appdb'
5  AND TABLE_NAME = 'orders'
6ORDER BY INDEX_NAME, SEQ_IN_INDEX;

第 24、33 条给出等待发生的索引名，这里核对索引到底按哪些列排序。锁定范围跟实际访问的索引有关，不一定跟 SQL 的 WHERE 文本直觉一致；CARDINALITY 是估计值，选了哪个索引还要看真实执行计划。

39. 查目标表的主键与唯一索引

SQL

1SELECT INDEX_NAME,
2       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
3         AS index_columns,
4       MIN(NON_UNIQUE) AS non_unique
5FROM information_schema.STATISTICS
6WHERE TABLE_SCHEMA = 'appdb'
7  AND TABLE_NAME = 'orders'
8GROUP BY INDEX_NAME
9HAVING MIN(NON_UNIQUE) = 0
10ORDER BY INDEX_NAME;

完整唯一键上的等值锁定读，与非唯一范围扫描的锁范围通常不同。这里先确认业务 SQL 的谓词是否覆盖整条唯一索引，而不是只写了联合索引第一列；真正的锁模式仍以第 24 条的现场视图为准。

40. 查看指定索引当前的等待锁数据

SQL

1SELECT ENGINE_TRANSACTION_ID, LOCK_STATUS,
2       LOCK_MODE, LOCK_DATA, THREAD_ID
3FROM performance_schema.data_locks
4WHERE OBJECT_SCHEMA = 'appdb'
5  AND OBJECT_NAME = 'orders'
6  AND INDEX_NAME = 'idx_orders_status_created'
7  AND LOCK_STATUS = 'WAITING'
8ORDER BY ENGINE_TRANSACTION_ID;

把索引名换成第 24、33 条发现的对象。LOCK_DATA 可能展示组合索引键，也可能为空；它不能替代应用参数或执行计划。若请求集中在同一范围，再找持锁 SQL 是否批量扫过这段索引，而不是贸然改变索引或隔离级别。

DDL 被挡住后怎么处置

41. 定位等待最久的表元数据锁

SQL

1SELECT object_schema, object_name,
2       waiting_pid, waiting_query,
3       waiting_query_secs,
4       blocking_pid, blocking_lock_type,
5       blocking_lock_duration
6FROM sys.schema_table_lock_waits
7WHERE object_schema = 'appdb'
8ORDER BY waiting_query_secs DESC;

第 14 条看全库等待链；这里先缩到报障库，按等待时长看 DDL 影响面。blocking_pid 指客户端连接 ID，可能正在 Sleep，但事务还没提交。锁持续时间看 blocking_lock_duration，不要只看当前 SQL 文本来判断它是否持锁。

42. 对照请求和已持有的元数据锁

SQL

1SELECT p.OBJECT_SCHEMA, p.OBJECT_NAME,
2       p.LOCK_TYPE AS pending_type,
3       g.LOCK_TYPE AS granted_type,
4       p.OWNER_THREAD_ID AS waiting_thread,
5       g.OWNER_THREAD_ID AS granted_thread
6FROM performance_schema.metadata_locks AS p
7JOIN performance_schema.metadata_locks AS g
8  ON g.OBJECT_TYPE = p.OBJECT_TYPE
9 AND g.OBJECT_SCHEMA <=> p.OBJECT_SCHEMA
10 AND g.OBJECT_NAME <=> p.OBJECT_NAME
11WHERE p.LOCK_STATUS = 'PENDING'
12  AND g.LOCK_STATUS = 'GRANTED'
13  AND p.OBJECT_TYPE = 'TABLE'
14  AND p.OBJECT_SCHEMA = 'appdb';

这能列出同一对象上的待获锁和已获锁，便于核对锁类型。它不是完整的“谁挡住谁”算法：同一对象上有些已获锁与请求兼容，队列里也可能有先到的待获锁。确认真正阻塞连接，优先用第 41 条的 sys 等待链。

43. 查看元数据锁阻塞连接的当前语句

SQL

1SELECT w.object_schema, w.object_name,
2       w.blocking_pid, t.PROCESSLIST_COMMAND,
3       t.PROCESSLIST_TIME, t.PROCESSLIST_INFO,
4       e.SQL_TEXT AS current_sql
5FROM sys.schema_table_lock_waits AS w
6LEFT JOIN performance_schema.threads AS t
7  ON t.PROCESSLIST_ID = w.blocking_pid
8LEFT JOIN performance_schema.events_statements_current AS e
9  ON e.THREAD_ID = t.THREAD_ID
10WHERE w.object_schema = 'appdb';

当前语句为空，不能推断连接无影响；它可能早已执行完写入，却仍处在事务里。要找事务，回到第 15、18 条；需要上一条 SQL 时，语句历史必须事先采集。PROCESSLIST_TIME 只是当前状态持续时间，不是持锁总时间。

44. 查询目标表已打开的表句柄

SQL

1SELECT OBJECT_SCHEMA, OBJECT_NAME,
2       OWNER_THREAD_ID, INTERNAL_LOCK,
3       EXTERNAL_LOCK
4FROM performance_schema.table_handles
5WHERE OBJECT_SCHEMA = 'appdb'
6  AND OBJECT_NAME = 'orders';

table_handles 记录已打开句柄及 SQL/存储引擎层锁，和 metadata_locks、InnoDB data_locks 不是同一层。它依赖 wait/lock/table/sql/handler 采集；查不到时先确认采集开关，而不是断言表没有句柄占用。

45. 把表句柄的内部线程号换成连接 ID

SQL

1SELECT h.OBJECT_SCHEMA, h.OBJECT_NAME,
2       h.INTERNAL_LOCK, h.EXTERNAL_LOCK,
3       t.PROCESSLIST_ID AS conn_id,
4       t.PROCESSLIST_USER, t.PROCESSLIST_INFO
5FROM performance_schema.table_handles AS h
6LEFT JOIN performance_schema.threads AS t
7  ON t.THREAD_ID = h.OWNER_THREAD_ID
8WHERE h.OBJECT_SCHEMA = 'appdb'
9  AND h.OBJECT_NAME = 'orders';

句柄 OWNER_THREAD_ID 是 Performance Schema 内部号，处理连接时必须换成 PROCESSLIST_ID。这里只帮你找句柄所有者；它本身并不证明该连接就是第 41 条 DDL 的真正阻塞者，仍需对照元数据锁等待链。

46. 检查是否有非表对象的元数据锁等待

SQL

1SELECT OBJECT_TYPE, OBJECT_SCHEMA,
2       OBJECT_NAME, LOCK_TYPE,
3       LOCK_DURATION, OWNER_THREAD_ID
4FROM performance_schema.metadata_locks
5WHERE LOCK_STATUS = 'PENDING'
6  AND OBJECT_TYPE <> 'TABLE'
7ORDER BY OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;

元数据锁也会落在 schema、存储程序、表空间及用户级命名锁等对象上。第 41 条 sys.schema_table_lock_waits 只处理表等待；它没有行，不代表 MySQL 全局无元数据锁问题。先确认对象类型，再决定用哪条链路排查。

47. 查命名锁是否被连接持有或等待

SQL

1SELECT OBJECT_NAME AS lock_name,
2       LOCK_TYPE, LOCK_STATUS,
3       OWNER_THREAD_ID
4FROM performance_schema.metadata_locks
5WHERE OBJECT_TYPE = 'USER LEVEL LOCK'
6ORDER BY OBJECT_NAME, LOCK_STATUS;

应用用 GET_LOCK() 做互斥时，业务请求可能在等用户级命名锁，而不是 InnoDB 行锁。先找到锁名和连接，再核对应用获取、释放的路径；不要把这类锁按表索引锁来分析，也不要为了让请求继续就盲杀持锁连接。

48. 查看元数据锁等待的超时秒数

SQL

1SHOW VARIABLES LIKE 'lock_wait_timeout';

这是 MySQL 元数据锁等待涉及的参数，和第 25 条 innodb_lock_wait_timeout 不是一回事。DDL 在这里等到超时，会报不同于 InnoDB 行锁超时的错误。调大参数只能让请求等更久，不能解决长事务不提交的根因。

49. 取消已经不需要的等待 DDL

SQL

1-- 1234 是第 41 条 waiting_pid；确认这条 DDL 可取消
2KILL QUERY 1234;

KILL QUERY 中断指定连接当前语句，连接本身仍在。用于撤下已不需要的等待 DDL，比直接结束持锁业务连接影响通常小；不过 DDL 取消、在线变更阶段与应用重试行为仍要确认。执行后回查第 41 条，确认等待请求已消失。

50. 必须终止持锁连接时结束整个连接

SQL

1-- 5678 是第 41 条 blocking_pid；业务确认可以回滚后再执行
2KILL CONNECTION 5678;

这会结束连接，未提交事务需要回滚，释放锁可能还要时间。先核对连接所属应用、事务修改量（第 20 条）及重试方案；不要只因 DDL 卡着就把线上业务连接杀掉。执行后继续看等待链和业务错误，不能把 KILL 返回成功当成恢复完成。

锁已经过去，SQL 还能不能找回来

51. 找持锁连接最近执行过的语句

SQL

1SELECT e.EVENT_ID, e.EVENT_NAME, e.SQL_TEXT,
2       e.MYSQL_ERRNO, e.ROWS_AFFECTED
3FROM performance_schema.events_statements_history_long AS e
4JOIN performance_schema.threads AS t
5  ON t.THREAD_ID = e.THREAD_ID
6WHERE t.PROCESSLIST_ID = 5678
7ORDER BY e.EVENT_ID DESC
8LIMIT 20;

连接当前显示 Sleep 时，历史语句能帮忙找它此前执行的 UPDATE、DELETE 或锁定读。5678 用第 6、41 条确认的连接 ID 替换。历史缓冲只保留最近事件，采集没开或记录被覆盖时查不到；SQL 文本也可能截断，不能把这 20 条当作完整事务日志。

52. 查看发生死锁或超时的连接

SQL

1SELECT t.PROCESSLIST_ID AS conn_id,
2       x.ERROR_NUMBER, x.ERROR_NAME,
3       x.SUM_ERROR_RAISED, x.LAST_SEEN
4FROM performance_schema.events_errors_summary_by_thread_by_error AS x
5LEFT JOIN performance_schema.threads AS t
6  ON t.THREAD_ID = x.THREAD_ID
7WHERE x.ERROR_NUMBER IN (1205, 1213)
8ORDER BY x.LAST_SEEN DESC;

1205 是锁等待超时，1213 是死锁。这里按内部线程汇总错误，适合先找仍在的连接；已结束连接可能无法再映射客户端 ID。计数不提供具体 SQL 和阻塞者，还需结合第 51 条历史与第 9 条死锁现场。

53. 找表锁等待耗时最高的 SQL 摘要

SQL

1SELECT SCHEMA_NAME, DIGEST_TEXT,
2       COUNT_STAR, SUM_LOCK_TIME,
3       SUM_ERRORS, FIRST_SEEN, LAST_SEEN
4FROM performance_schema.events_statements_summary_by_digest
5WHERE SUM_LOCK_TIME > 0
6ORDER BY SUM_LOCK_TIME DESC
7LIMIT 20;

按摘要聚合便于发现反复等表锁的语句。MySQL 的语句事件 LOCK_TIME 统计表锁等待，SUM_LOCK_TIME 是它的累计值，不能拿来给 InnoDB 行锁等待 SQL 排名。排查行锁仍以第 4、23 条实时链和应用错误为主；摘要也可能因采集配置缺失或文本截断而不完整。

54. 从语句历史里找 1205 和 1213

SQL

1SELECT e.THREAD_ID, e.EVENT_ID,
2       e.MYSQL_ERRNO, e.SQL_TEXT,
3       e.TIMER_WAIT, e.ROWS_AFFECTED
4FROM performance_schema.events_statements_history_long AS e
5WHERE e.MYSQL_ERRNO IN (1205, 1213)
6ORDER BY e.THREAD_ID, e.EVENT_ID DESC
7LIMIT 50;

这能找到报错的那条语句，不保证找到另一侧持锁 SQL。TIMER_WAIT 是 Performance Schema 计时器单位，不是秒；不要直接把数字贴进故障报告。若历史采集没开，错误只能去应用日志和 MySQL 错误日志找。

55. 查某个等待连接的最近语句

SQL

1SELECT t.PROCESSLIST_ID AS conn_id,
2       e.EVENT_ID, e.SQL_TEXT,
3       e.MYSQL_ERRNO, e.LOCK_TIME
4FROM performance_schema.threads AS t
5JOIN performance_schema.events_statements_history AS e
6  ON e.THREAD_ID = t.THREAD_ID
7WHERE t.PROCESSLIST_ID = 1234
8ORDER BY e.EVENT_ID DESC;

每线程历史比全局历史更适合看一个仍在线的连接；连接结束或缓冲覆盖后就没了。1234 换成等待连接 ID。LOCK_TIME 是表锁等待时间，不能据此推算 InnoDB 行锁时长。SQL 若使用预处理语句，文本和参数信息也可能不足，继续对照应用请求日志。

56. 查看运行中连接的执行计划

SQL

1-- 1234 为当前正在执行可解释语句的客户端连接 ID
2EXPLAIN FOR CONNECTION 1234;

连接仍在执行 SELECT、UPDATE、DELETE 等语句时，可以从另一连接看它的计划。若它已变成 Sleep、语句结束或无权限，命令可能失败；这是计划线索，不是死锁回放。看是否用了第 38 条找到的索引，以及扫描范围是否远大于业务预期。

57. 预估批量 UPDATE 会扫哪个索引

SQL

1-- 仅 EXPLAIN，不执行 UPDATE；条件改成报障 SQL 的实际谓词
2EXPLAIN FORMAT=JSON
3UPDATE appdb.orders
4SET status = 'closed'
5WHERE status = 'pending'
6  AND created_at < '2026-01-01';

锁冲突如果集中在一个范围，先看 UPDATE 是按条件索引定位，还是扫了大量行。EXPLAIN 不修改数据，但这份示例计划取决于现场统计、参数与索引；把真实 SQL 条件代进去后再判断，不要凭示例常量决定线上改索引。

58. 预估 DELETE 的扫描范围

SQL

1-- 仅 EXPLAIN，不执行 DELETE；检查真实业务参数
2EXPLAIN FORMAT=TREE
3DELETE FROM appdb.orders
4WHERE created_at < '2025-01-01'
5  AND status = 'archived';

一次清理任务锁住在线写入时，检查它用什么索引扫表、预计要处理多少行。EXPLAIN FORMAT=TREE 显示的是估计计划，没有实际加锁结果；要对照第 24、33 条的锁索引，以及批次大小和事务提交频率。

59. 把高锁耗时摘要限定到写入语句

SQL

1SELECT SCHEMA_NAME, DIGEST_TEXT,
2       COUNT_STAR, SUM_LOCK_TIME,
3       SUM_ERRORS, LAST_SEEN
4FROM performance_schema.events_statements_summary_by_digest
5WHERE SCHEMA_NAME = 'appdb'
6  AND DIGEST_TEXT REGEXP '^(UPDATE|DELETE|INSERT|REPLACE)'
7ORDER BY SUM_LOCK_TIME DESC
8LIMIT 30;

第 53 条覆盖所有语句；这一条缩到报障库写入类摘要，关注的是表锁等待聚合，不是 InnoDB 行锁排行。摘要把字面量规范化，同一模板的热点参数会合在一起；要找哪个订单或范围在争用，仍需应用参数和实时锁数据。

60. 确认语句历史采集是否真的开启

SQL

1SELECT NAME, ENABLED
2FROM performance_schema.setup_consumers
3WHERE NAME IN ('events_statements_current',
4               'events_statements_history',
5               'events_statements_history_long');

第 51、54、55 条依赖相应 consumer。查到 NO 时，历史没有记录是采集状态，不是 SQL 从没运行过；即使 consumer 已开启，也要确认相关语句 instrument 和缓冲容量。需要临时开启时先评估开销与留存范围，故障后再开无法补出过去的语句。

在测试库复现不同锁范围

以下示例只在测试库执行，appdb.orders 与值都要替换。需要保留锁供另一连接观察时，先在测试会话启动事务；测试结束务必提交或回滚。

61. 用完整主键条件做锁定读

SQL

1-- 测试会话已 START TRANSACTION；order_id 为主键
2SELECT order_id, status
3FROM appdb.orders
4WHERE order_id = 1001
5FOR UPDATE;

在唯一索引的完整等值条件上，InnoDB 通常锁住找到的记录，不需要把整段范围都锁起来。先在另一连接查第 3、31 条，确认实际索引和锁模式；若主键不存在、语句有 JOIN，锁结果可能不同。事务不结束，锁仍可能持有。

62. 用范围条件对照 next-key 锁

SQL

1-- 测试会话已 START TRANSACTION；用另一连接查看锁截面
2SELECT order_id, created_at
3FROM appdb.orders
4WHERE created_at >= '2026-01-01'
5  AND created_at < '2026-02-01'
6FOR UPDATE;

范围锁定读与第 61 条完整主键等值读不同。默认 REPEATABLE READ 下，InnoDB 常沿实际扫描的索引取 next-key/gap 相关锁，插入该范围也可能被挡。先用第 67 条确认计划；没有合适索引时，扫描和锁覆盖面可能远超返回行数。

63. 复现共享锁定读

SQL

1-- 测试会话已 START TRANSACTION
2SELECT order_id, status
3FROM appdb.orders
4WHERE order_id = 1001
5FOR SHARE;

FOR SHARE 为读到的记录请求共享锁，可与其他共享锁兼容，但会影响同一记录的修改。普通一致性 SELECT 不等同于这条语句；观察第 3、24 条时要对照 S 与 X 模式。应用若只是查展示数据，不应无目的地把普通读改成锁定读。

64. 不等锁，立即返回冲突

SQL

1-- 另一测试事务已锁住 order_id=1001
2SELECT order_id, status
3FROM appdb.orders
4WHERE order_id = 1001
5FOR UPDATE NOWAIT;

NOWAIT 遇到拿不到的锁会立即报错，适合明确“不等就失败”的业务路径。它不会自动重试，也不能消除持锁事务；调用方要能识别异常和决定何时重试。与第 25 条把全局等待超时改小相比，这只改变当前锁定读的行为。

65. 队列取任务时跳过已锁行

SQL

1-- 仅供受控队列场景测试；不要用于要求完整结果的查询
2SELECT task_id
3FROM appdb.task_queue
4WHERE state = 'ready'
5ORDER BY task_id
6LIMIT 10
7FOR UPDATE SKIP LOCKED;

SKIP LOCKED 会跳过其他事务已锁的行，给并行消费者取任务很实用，但结果不是一致的全量视图。要有合适的状态/排序索引，并在同一事务里更新已领取任务；否则重复领取和任务饥饿仍可能发生。不能拿它绕过报表上的锁等待。

66. 记录复现会话的隔离级别

SQL

1SELECT @@session.transaction_isolation AS isolation_level,
2       @@session.autocommit AS autocommit_mode;

比较第 61、62 条的锁模式前，先把会话实际隔离级别和提交方式记下来。autocommit=1 时单条语句执行完就可能释放锁，另一连接赶来已经看不到现场；需要受控事务才能复现等待。不要拿服务器全局默认值代替测试会话值。

67. 看范围锁定读会用哪个索引

SQL

1-- 仅查看计划，不执行锁定读
2EXPLAIN FORMAT=JSON
3SELECT order_id, created_at
4FROM appdb.orders
5WHERE created_at >= '2026-01-01'
6  AND created_at < '2026-02-01'
7FOR UPDATE;

计划中的访问路径说明它准备扫描哪条索引；第 62 条真正加锁后的截面仍要查 data_locks。EXPLAIN 的行数是估计，不能直接推算精确锁行数。若计划改走别的索引，先复核统计、条件和索引定义，再讨论锁范围。

68. 仅对下一笔测试事务设置 READ COMMITTED

SQL

1-- 必须在启动下一笔事务前执行；仅限测试会话
2SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

对照第 62 条默认 REPEATABLE READ 的范围锁行为时，可在下一笔事务测试 READ COMMITTED。该级别下多数搜索/索引扫描不再使用 gap lock，但外键检查和重复键检查等仍有例外。隔离级别关系到一致性语义，不能因为一次锁等待就直接改生产应用。

69. 查看受控测试事务结束前的锁状态

SQL

1SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME,
2       INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA
3FROM performance_schema.data_locks
4WHERE THREAD_ID = (
5    SELECT THREAD_ID
6    FROM performance_schema.threads
7    WHERE PROCESSLIST_ID = 1234);

1234 是测试会话客户端连接 ID。第 6 条说明内部 THREAD_ID 与连接 ID 不同；在第二个会话读这张表，确认第 61–65 条的锁仍在。随后结束测试事务再取一次快照，观察哪些锁已经消失。

70. 回滚测试事务并释放锁

SQL

1-- 在持锁的测试会话执行
2ROLLBACK;

受控复现结束后不要把锁一直留在测试库里。ROLLBACK 取消该事务未提交的修改并结束事务；若另有等待，随后回看第 4、23 条是否消退。生产库里的 ROLLBACK 则会丢弃真实业务工作，不能把这条测试收尾命令直接套到线上持锁连接。

INSERT 和外键检查也会等锁

71. 找出引用目标表的外键

SQL

1SELECT CONSTRAINT_SCHEMA, TABLE_NAME AS child_table,
2       COLUMN_NAME AS child_column,
3       REFERENCED_TABLE_NAME AS parent_table,
4       REFERENCED_COLUMN_NAME AS parent_column,
5       CONSTRAINT_NAME
6FROM information_schema.KEY_COLUMN_USAGE
7WHERE REFERENCED_TABLE_SCHEMA = 'appdb'
8  AND REFERENCED_TABLE_NAME = 'orders'
9ORDER BY child_table, CONSTRAINT_NAME, ORDINAL_POSITION;

对 orders 的修改可能影响哪些子表，先从约束关系找出来。插入子表、删除或修改父表时，InnoDB 会为外键检查取锁；锁等待不一定只出现在语句显式操作的表上。联合外键要按 ORDINAL_POSITION 看完整列顺序。

72. 核对父表和子表的约束定义

SQL

1-- 把子表名换成第 71 条找到的对象
2SHOW CREATE TABLE appdb.order_items;

DDL 能看到外键、关联列和索引定义，比单看约束名更容易理解等待为何落在父表。当前表结构可能已变更，故障记录也应留一份当时的 DDL；别凭历史代码里的外键名称判断现在线上仍是同一条约束。

73. 确认子表外键列有可用索引

SQL

1SELECT INDEX_NAME, SEQ_IN_INDEX,
2       COLUMN_NAME, NON_UNIQUE
3FROM information_schema.STATISTICS
4WHERE TABLE_SCHEMA = 'appdb'
5  AND TABLE_NAME = 'order_items'
6ORDER BY INDEX_NAME, SEQ_IN_INDEX;

将第 71 条子表的外键列顺序与索引前缀对照。InnoDB 外键需要可用的索引；索引可能由 MySQL 自动创建或随后被另一条合适索引替代。第 38 条看报障表索引，这一条看关联子表，不能把两个对象混在一起。

74. 查看活跃事务的外键与唯一键检查状态

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_FOREIGN_KEY_CHECKS,
3       TRX_UNIQUE_CHECKS, TRX_STATE
4FROM information_schema.INNODB_TRX
5ORDER BY TRX_STARTED;

批量装载任务有时会改变这两个检查开关。读到 0 说明该事务检查状态不同，需要问清装载流程与数据完整性风险；它不是解决锁等待的建议。检查状态为 1 时，外键和重复键检查可能参与锁冲突，仍要找现场锁对象。

75. 读出事务最近一次外键错误

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_LAST_FOREIGN_KEY_ERROR
3FROM information_schema.INNODB_TRX
4WHERE TRX_LAST_FOREIGN_KEY_ERROR IS NOT NULL;

外键约束报错与外键检查等待锁是两件事，但这条可帮忙确认应用实际碰到哪条约束。TRX_LAST_FOREIGN_KEY_ERROR 只限当前事务仍在视图中时可读；事务结束后应查应用错误和日志，不能事后指望它保留全部历史。

76. 对照父表、子表当前的数据锁

SQL

1SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME,
2       INDEX_NAME, LOCK_TYPE, LOCK_MODE,
3       LOCK_STATUS, LOCK_DATA
4FROM performance_schema.data_locks
5WHERE OBJECT_SCHEMA = 'appdb'
6  AND OBJECT_NAME IN ('orders', 'order_items')
7ORDER BY ENGINE_TRANSACTION_ID, OBJECT_NAME, LOCK_STATUS;

子表 INSERT 卡住时，父表索引上也可能有锁等待。和第 71 条约束列、第 24 条等待关系一起看，才能确认是外键检查、父行修改还是别的索引争用。这里列出两张表所有当前锁，行多时再限定事务 ID。

77. 查看唯一键的列组合

SQL

1SELECT k.CONSTRAINT_NAME, k.TABLE_NAME,
2       GROUP_CONCAT(k.COLUMN_NAME ORDER BY k.ORDINAL_POSITION)
3         AS unique_columns
4FROM information_schema.KEY_COLUMN_USAGE AS k
5JOIN information_schema.TABLE_CONSTRAINTS AS tc
6  ON tc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
7 AND tc.TABLE_NAME = k.TABLE_NAME
8 AND tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
9WHERE k.CONSTRAINT_SCHEMA = 'appdb'
10  AND k.TABLE_NAME = 'orders'
11  AND tc.CONSTRAINT_TYPE = 'UNIQUE'
12GROUP BY k.CONSTRAINT_NAME, k.TABLE_NAME
13ORDER BY k.CONSTRAINT_NAME;

这里用 TABLE_CONSTRAINTS 限定唯一约束，避免把外键混进来。第 39 条还能查唯一索引；约束列只是结构线索，不是写入冲突的精确锁现场。故障 SQL 对联合唯一键的写入值必须看全套列。

78. 区分重复键与外键相关报错次数

SQL

1SELECT ERROR_NUMBER, ERROR_NAME,
2       SUM_ERROR_RAISED, FIRST_SEEN, LAST_SEEN
3FROM performance_schema.events_errors_summary_global_by_error
4WHERE ERROR_NUMBER IN (1062, 1451, 1452)
5ORDER BY SUM_ERROR_RAISED DESC;

1062 是重复键，1451/1452 是外键约束错误。这些报错本身不等于锁等待，但若同一写入高峰同时出现，需要检查应用重试、写入顺序和约束关系。汇总没有参数值和事务双方，继续查语句历史与应用日志。

79. 从语句历史找约束错误的 SQL

SQL

1SELECT THREAD_ID, EVENT_ID,
2       MYSQL_ERRNO, SQL_TEXT
3FROM performance_schema.events_statements_history_long
4WHERE MYSQL_ERRNO IN (1062, 1451, 1452)
5ORDER BY THREAD_ID, EVENT_ID DESC
6LIMIT 50;

找到了报错 SQL，继续核对写入的父子关系、联合唯一键和值来源。历史记录可能被覆盖、文本可能截断，预处理参数也未必可见；仅凭一条归一化 SQL 不能判定是哪笔订单重复。业务日志通常要一起保存。

80. 核对外键的级联动作

SQL

1SELECT CONSTRAINT_NAME, TABLE_NAME,
2       REFERENCED_TABLE_NAME,
3       UPDATE_RULE, DELETE_RULE
4FROM information_schema.REFERENTIAL_CONSTRAINTS
5WHERE CONSTRAINT_SCHEMA = 'appdb'
6  AND REFERENCED_TABLE_NAME = 'orders'
7ORDER BY TABLE_NAME, CONSTRAINT_NAME;

父表 UPDATE/DELETE 如果触发级联，实际修改范围可能超出原语句直觉。CASCADE、RESTRICT 等规则先读清楚，再对照第 71 条子表和第 76 条锁截面。处理线上阻塞时，不能把一条父表 DELETE 简化成“只锁父表一行”。

高并发时找根阻塞者

81. 找锁结构和锁内存占用高的事务

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_STARTED, TRX_LOCK_STRUCTS,
3       TRX_LOCK_MEMORY_BYTES, TRX_ROWS_LOCKED,
4       TRX_ROWS_MODIFIED
5FROM information_schema.INNODB_TRX
6ORDER BY TRX_LOCK_MEMORY_BYTES DESC
7LIMIT 20;

大批量 UPDATE/DELETE 可能积累大量锁结构。TRX_LOCK_STRUCTS 是锁结构个数，不是精确业务行数；TRX_ROWS_LOCKED 也要结合事务运行时间和实际 SQL 看。先找工作量与提交频率，再谈缩小批次，不能只因数字大就杀事务。

82. 看长事务当前涉及多少张表

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_STARTED, TRX_TABLES_IN_USE,
3       TRX_TABLES_LOCKED, TRX_ROWS_LOCKED
4FROM information_schema.INNODB_TRX
5ORDER BY TRX_STARTED
6LIMIT 30;

这能挑出运行很久、又跨多张表操作的事务。TRX_TABLES_IN_USE 和 TRX_TABLES_LOCKED 不是详细表名；要找真正对象，仍查第 31、76 条 data_locks。行数为零也不能证明它不持有元数据锁。

83. 查看事务当前在做哪类操作

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_STATE, TRX_OPERATION_STATE,
3       TRX_QUERY, TRX_STARTED
4FROM information_schema.INNODB_TRX
5WHERE TRX_STATE IN ('RUNNING', 'LOCK WAIT')
6ORDER BY TRX_STARTED;

TRX_OPERATION_STATE 能给出 InnoDB 内部阶段线索，TRX_QUERY 可能为空。持锁事务当前没跑 SQL，仍要查第 18、51 条连接与历史；不能从一段操作状态反推它整个事务执行过哪些语句。

84. 查看等待事务的调度权重

SQL

1SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
2       TRX_WAIT_STARTED, TRX_WEIGHT,
3       TRX_SCHEDULE_WEIGHT,
4       TRX_ROWS_LOCKED, TRX_ROWS_MODIFIED
5FROM information_schema.INNODB_TRX
6WHERE TRX_STATE = 'LOCK WAIT'
7ORDER BY TRX_SCHEDULE_WEIGHT DESC;

MySQL 8.4 的 TRX_SCHEDULE_WEIGHT 属于 InnoDB CATS 等待调度的相对权重；只给等待事务，和第 19 条用于死锁牺牲者判断的 TRX_WEIGHT 不是同一个算法。高权重不等于这笔业务最重要，也不表示应人工改变调度；主要用来读懂高争用现场。

85. 同时看锁等待时间和持锁事务年龄

SQL

1SELECT wait_age_secs, locked_table,
2       locked_index, waiting_pid,
3       blocking_pid, blocking_trx_started,
4       blocking_trx_age, blocking_query
5FROM sys.innodb_lock_waits
6ORDER BY wait_age_secs DESC
7LIMIT 30;

wait_age_secs 是当前等待时间，blocking_trx_age 是持锁事务年龄，两者不能混用。blocking_query 为空，常是连接已空闲但事务没提交；这时用第 51 条查语句历史。视图是实时截面，取样后等待双方可能随时变化。

86. 统计一个连接挡住了多少等待连接

SQL

1SELECT blocking_pid, blocking_trx_id,
2       COUNT(DISTINCT waiting_pid) AS waiting_connections,
3       MAX(wait_age_secs) AS longest_wait_seconds
4FROM sys.innodb_lock_waits
5GROUP BY blocking_pid, blocking_trx_id
6ORDER BY waiting_connections DESC,
7         longest_wait_seconds DESC;

第 22 条按内部事务统计等待关系；这里直接输出业务能识别的客户端连接 ID。一个请求可能对应多把冲突锁，故用 DISTINCT waiting_pid。有很多等待者的事务值得优先排查，但是否终止仍看业务修改量与回滚成本。

87. 找自己没有继续等待别人的根持锁事务

SQL

1SELECT DISTINCT b.blocking_trx_id,
2       b.blocking_pid, b.blocking_trx_started,
3       b.blocking_query
4FROM sys.innodb_lock_waits AS b
5LEFT JOIN sys.innodb_lock_waits AS upstream
6  ON upstream.waiting_trx_id = b.blocking_trx_id
7WHERE upstream.waiting_trx_id IS NULL
8ORDER BY b.blocking_trx_started;

若 A 等 B、B 又等 C，只处理 B 的当前语句可能解决不了根因。这里挑出在当前行锁等待视图里没有再等上游的持锁事务。它不覆盖元数据锁，且死锁可能已被检测器立刻打断；结果要和第 14、28 条及业务日志对照。

88. 看根持锁事务的未提交工作量

SQL

1SELECT b.blocking_trx_id,
2       b.blocking_pid,
3       MAX(b.blocking_trx_rows_locked)
4         AS blocking_rows_locked,
5       MAX(b.blocking_trx_rows_modified)
6         AS blocking_rows_modified,
7       COUNT(DISTINCT b.waiting_pid)
8         AS waiting_connections
9FROM sys.innodb_lock_waits AS b
10LEFT JOIN sys.innodb_lock_waits AS upstream
11  ON upstream.waiting_trx_id = b.blocking_trx_id
12WHERE upstream.waiting_trx_id IS NULL
13GROUP BY b.blocking_trx_id, b.blocking_pid
14ORDER BY waiting_connections DESC;

这是第 87 条根持锁者的影响面与回滚工作量快照。修改行数高时，结束连接后的回滚可能很慢；等待连接多也不能跳过应用确认。这里的行数来自事务统计，不是每个等待请求最终能影响的行数。

89. 只生成候选终止语句供人工核对

SQL

1SELECT DISTINCT blocking_pid,
2       sql_kill_blocking_query,
3       sql_kill_blocking_connection
4FROM sys.innodb_lock_waits
5WHERE blocking_pid IS NOT NULL
6ORDER BY blocking_pid;

sys 视图会给出两个 KILL 文本，但这条只读取，不会执行。取消当前语句和结束连接的效果不同；尤其持锁连接已经 Sleep 时，杀当前语句未必释放旧事务的锁。先核对第 20、88 条工作量与业务归属，再选择是否处理。

90. 按表和索引汇总当前等待影响面

SQL

1SELECT locked_table_schema, locked_table_name,
2       locked_index, COUNT(DISTINCT waiting_pid)
3         AS waiting_connections,
4       COUNT(DISTINCT blocking_pid)
5         AS blocking_connections,
6       MAX(wait_age_secs) AS longest_wait_seconds
7FROM sys.innodb_lock_waits
8GROUP BY locked_table_schema,
9         locked_table_name, locked_index
10ORDER BY waiting_connections DESC,
11         longest_wait_seconds DESC;

报障时先找影响最大的表和索引，而不是只追最早的一条请求。locked_index 显示等待落在哪条索引上；锁定范围仍需第 24、33 条查看模式和数据。多个阻塞连接时，分别追到根事务，不能把一个表上的全部等待归因于同一连接。

处置结束还要回读现场

91. 回查报障表的行锁等待链

SQL

1SELECT wait_started, wait_age_secs,
2       locked_index, waiting_pid,
3       blocking_pid, waiting_query
4FROM sys.innodb_lock_waits
5WHERE locked_table_schema = 'appdb'
6  AND locked_table_name = 'orders'
7ORDER BY wait_started;

业务提交、取消语句或终止连接后，重新看这张表还有没有请求在等。老等待消失了，若又出现新的 waiting_pid，说明热点可能持续；这时不能只说“原连接处理完了”，要按第 90 条重新评估影响面。

92. 回查目标表的待获元数据锁

SQL

1SELECT LOCK_TYPE, LOCK_DURATION,
2       OWNER_THREAD_ID, LOCK_STATUS
3FROM performance_schema.metadata_locks
4WHERE OBJECT_TYPE = 'TABLE'
5  AND OBJECT_SCHEMA = 'appdb'
6  AND OBJECT_NAME = 'orders'
7  AND LOCK_STATUS = 'PENDING';

行锁链清空，不代表 DDL 的元数据锁等待也清了。尤其取消一个等待 DDL 后，队列里可能还有其他变更任务；结合第 41 条客户端连接 ID 与业务窗口逐个确认。采集没开时，空结果不能作为验收证据。

93. 统计报障表仍有多少行锁等待连接

SQL

1SELECT COUNT(DISTINCT waiting_pid)
2         AS waiting_connections,
3       COUNT(DISTINCT blocking_pid)
4         AS blocking_connections,
5       COALESCE(MAX(wait_age_secs), 0)
6         AS longest_wait_seconds
7FROM sys.innodb_lock_waits
8WHERE locked_table_schema = 'appdb'
9  AND locked_table_name = 'orders';

取处置前后两个快照，比较影响面和最长等待。waiting_connections=0 只说明当前没有已捕获的 InnoDB 行锁等待；应用超时可能已经发生，或下一批请求稍后又被堵住。仍需看错误和业务请求恢复情况。

94. 核对实例级当前 InnoDB 行锁等待数

SQL

1SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_current_waits';

第 93 条只针对报障表，这个状态值看整个实例。若目标表清了但实例当前等待数仍高，说明还有其他热点；若这里为零，也只是该时刻的读数。第 27 条的累计等待时间/次数要用两个时间点比较，不能与当前值混为一谈。

95. 核对原持锁事务是否仍在 InnoDB 事务视图

SQL

1-- 123456789 为处置前记录的 blocking_trx_id
2SELECT TRX_ID, TRX_MYSQL_THREAD_ID AS conn_id,
3       TRX_STATE, TRX_STARTED,
4       TRX_ROWS_MODIFIED
5FROM information_schema.INNODB_TRX
6WHERE TRX_ID = 123456789;

若原事务仍在，要看它是否正提交、回滚或继续运行；不能因为 KILL CONNECTION 返回成功就立刻认为所有锁释放。查询为空表示这笔事务不在实时视图中，还需回读第 91、93 条和应用结果，避免把“查不到原事务”当作完整恢复。

96. 检查原事务是否还有已获数据锁

SQL

1-- 用第 95 条同一个事务 ID；查询的是当前截面
2SELECT OBJECT_SCHEMA, OBJECT_NAME,
3       INDEX_NAME, LOCK_MODE, LOCK_DATA
4FROM performance_schema.data_locks
5WHERE ENGINE_TRANSACTION_ID = 123456789
6  AND LOCK_STATUS = 'GRANTED';

若事务仍在第 95 条可见，这里核对它是否还有锁；若锁记录仍多，继续等提交/回滚并观察业务负载。结果为空可能是事务结束，也可能实时截面已变化，不应脱离第 95 条单独解读。

97. 对照死锁和超时错误是否继续增长

SQL

1SELECT ERROR_NUMBER, ERROR_NAME,
2       SUM_ERROR_RAISED, LAST_SEEN
3FROM performance_schema.events_errors_summary_global_by_error
4WHERE ERROR_NUMBER IN (1205, 1213)
5ORDER BY ERROR_NUMBER;

处置前后分别保存 SUM_ERROR_RAISED，再算间隔增量。只有累计值、没有前一份快照，不能判断最近几分钟是否还在报错；LAST_SEEN 帮助确认最后一次出现时间。统计可能重置，也需以应用错误率为准。

98. 看业务用户的事务是否仍在等行锁

SQL

1SELECT t.TRX_ID, t.TRX_MYSQL_THREAD_ID AS conn_id,
2       p.USER, p.HOST,
3       t.TRX_WAIT_STARTED, t.TRX_QUERY
4FROM information_schema.INNODB_TRX AS t
5JOIN information_schema.PROCESSLIST AS p
6  ON p.ID = t.TRX_MYSQL_THREAD_ID
7WHERE t.TRX_STATE = 'LOCK WAIT'
8  AND p.USER = 'app_user'
9ORDER BY t.TRX_WAIT_STARTED;

报障表恢复后，业务用户可能还在别的表上等待。app_user 要换现场账号；账号可能由多个服务共用，还要用 HOST、连接池和第 90 条锁对象定位。只有当前等待消失，仍不足以证明应用吞吐恢复。

99. 确认目标表是否仍有表元数据锁等待链

SQL

1SELECT waiting_pid, waiting_query,
2       waiting_query_secs,
3       blocking_pid, blocking_lock_type
4FROM sys.schema_table_lock_waits
5WHERE object_schema = 'appdb'
6  AND object_name = 'orders'
7ORDER BY waiting_query_secs DESC;

第 92 条看待获锁记录，这条再核对 sys 给出的等待与阻塞连接。若曾取消 DDL，要确认已无旧请求；若随后业务又提交了 DDL，新的等待需另行处理。sys 视图没有行时，仍应确认元数据锁采集有效。

100. 同时回读行锁与元数据锁等待数量

SQL

1SELECT NOW() AS sampled_at,
2       (SELECT COUNT(*)
3        FROM performance_schema.data_lock_waits)
4         AS innodb_wait_links,
5       (SELECT COUNT(*)
6        FROM performance_schema.metadata_locks
7        WHERE LOCK_STATUS = 'PENDING')
8         AS pending_metadata_locks;

最后保存一个带时间戳的全实例快照，和处置前的同口径读数相比。两个数为零，只说明这次取样没有已采集的等待；业务错误率、请求耗时和新连接仍要观察。若数值未降，先按第 90、46 条找新的对象，不要反复处置已结束的原连接。

总结

MySQL 锁问题别只盯着正在等的 SQL。先看它请求哪把锁，再追持锁事务、索引和最近执行过的语句；DDL 卡住时另查元数据锁。处置后记得回读等待链和应用错误，确认不是旧请求刚走、新请求又排上了。

这篇清单建议收藏。其他数据库运维专题可在 ORA100 · DBA100 查看：

https://ora100.com/dba100

微信搜索小程序 「三笠的百令册」，也可以按专题找命令。

MySQL 事务、锁与死锁诊断 100 条命令 - DBA 学习之路