MySQL / 系统课时
定位锁等待阻塞链
定位锁等待阻塞链
看到某条 SQL 卡住时,真正要找的不是“谁最慢”,而是谁正在持有等待者需要的锁。本课把 performance_schema.data_lock_waits、data_locks、线程、事务和当前语句信息关联起来,建立 waiter → blocker → 锁对象 → SQL 的完整证据链。
学习目标
- 从
data_lock_waits读取等待者与阻塞者之间的直接依赖 - 将锁关系关联到连接 ID、事务状态、SQL 文本和具体索引对象
- 沿等待边继续向上追踪,识别真正的根阻塞会话
先把阻塞关系看成有向边
performance_schema.data_lock_waits 的一行可以理解为一条边:请求锁的一方是 waiter,持有冲突锁的一方是 blocker。data_locks 再补充双方锁定的库、表、索引、锁类型与锁模式;threads 将内部线程 ID 映射成连接 ID;事务与语句表则补上现场上下文。

定位时优先从等待边出发,因为“占用 CPU 最多”或“执行时间最长”的会话不一定持有冲突锁。一个连接甚至可能自己也在等待上游事务,因此第一层 blocker 也不一定是真正源头。
一条查询拼出 waiter 与 blocker
下面查询适用于 MySQL 8.x 的 InnoDB 锁等待现场。它把等待事务、阻塞事务、双方连接 ID、当前 SQL,以及等待者请求的锁对象和双方锁模式放在同一行中。
SELECT
w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
wt.PROCESSLIST_ID AS waiting_conn_id,
wtrx.STATE AS waiting_trx_state,
ws.SQL_TEXT AS waiting_sql,
rl.OBJECT_SCHEMA,
rl.OBJECT_NAME,
rl.INDEX_NAME,
rl.LOCK_TYPE,
rl.LOCK_MODE AS waiting_lock_mode,
w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
bt.PROCESSLIST_ID AS blocking_conn_id,
btrx.STATE AS blocking_trx_state,
bs.SQL_TEXT AS blocking_sql,
bl.LOCK_MODE AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS rl
ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS bl
ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS wt
ON wt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
LEFT JOIN performance_schema.events_transactions_current AS wtrx
ON wtrx.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.events_transactions_current AS btrx
ON btrx.THREAD_ID = w.BLOCKING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current AS ws
ON ws.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current AS bs
ON bs.THREAD_ID = w.BLOCKING_THREAD_ID
ORDER BY blocking_conn_id, waiting_conn_id;预期结果
至少返回一行等待关系:
waiting_conn_id=<ID>
blocking_conn_id=<ID>
OBJECT_SCHEMA=<VALUE>
OBJECT_NAME=<VALUE>
INDEX_NAME=<VALUE>
waiting_lock_mode=<VALUE>
blocking_lock_mode=<VALUE>
实际事务 ID、连接 ID、SQL 文本和锁对象以现场为准。如果 INDEX_NAME 是 PRIMARY 或某个二级索引,就能继续判断冲突发生在什么访问路径上;如果 LOCK_TYPE 显示记录级对象,再结合 LOCK_MODE 判断双方请求是否兼容。SQL 文本为空时,不要据此断定阻塞者“什么也没做”:它可能已经执行完更新后处于空闲状态,却仍因未提交事务持有锁。
沿阻塞边找到根源
存在 T1 等 T2、T2 又等 T3 的链条时,T2 同时是 blocker 和 waiter。根阻塞者的特征是:它出现在 BLOCKING_THREAD_ID 中,但不再出现在任何 REQUESTING_THREAD_ID 中。下面查询直接列出这类线程及其连接 ID。
WITH edges AS (
SELECT
REQUESTING_THREAD_ID AS waiter_thread_id,
BLOCKING_THREAD_ID AS blocker_thread_id
FROM performance_schema.data_lock_waits
WHERE BLOCKING_THREAD_ID IS NOT NULL
)
SELECT DISTINCT
e.blocker_thread_id,
t.PROCESSLIST_ID AS root_blocking_conn_id
FROM edges AS e
LEFT JOIN edges AS upstream
ON upstream.waiter_thread_id = e.blocker_thread_id
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = e.blocker_thread_id
WHERE upstream.waiter_thread_id IS NULL;预期结果
blocker_thread_id root_blocking_conn_id
<ID> <ID>如果返回多行,说明现场可能存在多条独立阻塞链。此时分别回到上一条关联查询查看每个根阻塞连接持有什么锁、执行过什么 SQL,以及事务是否长期未提交。
决定是否终止连接之前
找到根 blocker 只是诊断结论,不等于可以直接 KILL。先确认事务年龄、业务身份、是否正在执行批处理,以及回滚成本;大型更新被终止后也可能需要较长时间回滚。更安全的处置通常是先停止制造新等待,再让可控事务提交或回滚,最后验证 data_lock_waits 是否清空。
SELECT COUNT(*) AS lock_wait_edges
FROM performance_schema.data_lock_waits;预期结果
lock_wait_edges
0当结果为 0,只能说明当前没有被 Performance Schema 观察到的 InnoDB 锁等待边;它不代表所有事务都已结束。诊断完成后还应回看事务边界、访问顺序和索引条件,避免同一阻塞链再次出现。