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;事务与语句表则补上现场上下文。

waiter、blocker、SQL 与锁对象关联图

定位时优先从等待边出发,因为“占用 CPU 最多”或“执行时间最长”的会话不一定持有冲突锁。一个连接甚至可能自己也在等待上游事务,因此第一层 blocker 也不一定是真正源头。

一条查询拼出 waiter 与 blocker

下面查询适用于 MySQL 8.x 的 InnoDB 锁等待现场。它把等待事务、阻塞事务、双方连接 ID、当前 SQL,以及等待者请求的锁对象和双方锁模式放在同一行中。

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;

预期结果

text
至少返回一行等待关系:
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。

sql
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;

预期结果

text
blocker_thread_id  root_blocking_conn_id
<ID>               <ID>

如果返回多行,说明现场可能存在多条独立阻塞链。此时分别回到上一条关联查询查看每个根阻塞连接持有什么锁、执行过什么 SQL,以及事务是否长期未提交。

决定是否终止连接之前

找到根 blocker 只是诊断结论,不等于可以直接 KILL。先确认事务年龄、业务身份、是否正在执行批处理,以及回滚成本;大型更新被终止后也可能需要较长时间回滚。更安全的处置通常是先停止制造新等待,再让可控事务提交或回滚,最后验证 data_lock_waits 是否清空。

sql
SELECT COUNT(*) AS lock_wait_edges
FROM performance_schema.data_lock_waits;

预期结果

text
lock_wait_edges
0

当结果为 0,只能说明当前没有被 Performance Schema 观察到的 InnoDB 锁等待边;它不代表所有事务都已结束。诊断完成后还应回看事务边界、访问顺序和索引条件,避免同一阻塞链再次出现。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

定位锁等待阻塞链

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通