MySQL / 系统课时

排查 MDL 阻塞链

排查 MDL 阻塞链

DDL 长时间停在“Waiting for table metadata lock”时,真正的根因往往不是 DDL 本身,而是更早启动、仍持有元数据锁的会话。本节用 sys.schema_table_lock_waits、performance_schema.metadata_locks、进程列表与事务视图,把等待者、阻塞者和长事务串成一条可执行的定位链路。

学习目标

  • 从等待中的 DDL 快速定位直接阻塞它的会话
  • 联合 MDL、进程与事务信息识别阻塞链的源头
  • 在终止会话前判断影响,并把 MDL 风险纳入上线门禁

从等待的 DDL 找到直接阻塞者

MySQL 用 metadata lock(MDL)保护表、库等对象的元数据一致性。DDL 需要获取与现有访问兼容的 MDL;只要某个事务仍持有不兼容锁,DDL 就会等待。MySQL 8.4 的 sys.schema_table_lock_waits 已把等待会话与阻塞会话直接关联起来,适合先做第一跳定位。:contentReference[oaicite:0]{index=0}

先针对目标表查询等待关系:

sql
SELECT
    object_schema,
    object_name,
    waiting_pid,
    waiting_lock_type,
    waiting_query_secs,
    blocking_pid,
    blocking_lock_type,
    sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = 'app'
  AND object_name = 'orders'
ORDER BY waiting_query_secs DESC;

预期结果

text
object_schema | object_name | waiting_pid | waiting_lock_type | waiting_query_secs | blocking_pid | blocking_lock_type | sql_kill_blocking_connection
app           | orders      | <ID>        | EXCLUSIVE         | <VALUE>            | <ID>         | SHARED_WRITE       | KILL <ID>

waiting_pid 是正在等待的会话,blocking_pid 是当前直接阻塞者。sql_kill_blocking_connection 只是 sys 视图生成的处置建议,不应看到后立即执行;先确认该会话属于哪个业务、是否仍有未提交事务,以及回滚成本。

MDL 等待者、阻塞者与长事务的关系

用 metadata_locks 还原同一对象上的锁队列

如果需要确认谁已经拿到锁、谁仍在等待,可直接查看 performance_schema.metadata_locks。其中 LOCK_STATUS='GRANTED' 表示已持有,PENDING 表示正在等待;再关联 performance_schema.threads,可以把内部线程号映射到连接 ID 和当前语句。:contentReference[oaicite:1]{index=1}

sql
SELECT
    t.PROCESSLIST_ID AS pid,
    ml.LOCK_STATUS,
    ml.LOCK_TYPE,
    ml.LOCK_DURATION,
    t.PROCESSLIST_USER AS user_name,
    t.PROCESSLIST_TIME AS seconds_in_state,
    LEFT(t.PROCESSLIST_INFO, 120) AS sql_text
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
  ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
  AND ml.OBJECT_SCHEMA = 'app'
  AND ml.OBJECT_NAME = 'orders'
ORDER BY
    FIELD(ml.LOCK_STATUS, 'PENDING', 'GRANTED'),
    t.PROCESSLIST_TIME DESC;

预期结果

text
pid  | LOCK_STATUS | LOCK_TYPE    | LOCK_DURATION | user_name | seconds_in_state | sql_text
<ID> | PENDING     | EXCLUSIVE    | TRANSACTION   | ddl_user  | <VALUE>          | ALTER TABLE app.orders ...
<ID> | GRANTED     | SHARED_WRITE | TRANSACTION   | app_user  | <VALUE>          | <VALUE>

阻塞者此刻可能处于空闲状态,因此 sql_text 可能为空。不要因为进程列表里看不到正在执行的 SQL,就认定它没有持锁;MDL 是否仍存在应以 metadata_locks 为准。

用事务视图找到真正的根因会话

DDL 的直接阻塞者常常只是一个“事务未结束”的连接。INFORMATION_SCHEMA.INNODB_TRX 能看到 InnoDB 中当前事务的状态、启动时间和对应 MySQL 线程 ID,可用 trx_mysql_thread_id 与前面得到的 blocking_pid 对上。:contentReference[oaicite:2]{index=2}

sql
SELECT
    trx_mysql_thread_id AS pid,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds,
    trx_rows_locked,
    trx_rows_modified,
    LEFT(trx_query, 120) AS trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

预期结果

text
pid  | trx_state | trx_age_seconds | trx_rows_locked | trx_rows_modified | trx_query
<ID> | RUNNING   | <VALUE>         | <VALUE>         | <VALUE>           | <VALUE>

如果这里的 pid 与 blocking_pid 一致,而且事务年龄明显超过该业务的正常请求周期,通常就已经找到根因。锁和事务视图都是瞬时快照,排查期间数据可能变化,因此应连续采样并围绕同一个连接 ID 交叉确认,而不是只依赖一次查询。:contentReference[oaicite:3]{index=3}

处置阻塞链而不是只杀掉等待者

处理顺序应从“阻止影响扩大”开始:先确认等待 DDL 是否已经让大量请求排队;必要时优先撤销或终止尚未生效的 DDL,再处理最早的持锁事务。若业务能够安全提交或回滚,应由应用侧结束事务;只有确认会话可中断且能够承担回滚代价时,才考虑执行 KILL <ID>。

上线前还应把 MDL 检查变成门禁:目标表存在长事务、已经出现 MDL 等待,或无法确认阻塞会话归属时,不启动 DDL。真正可控的发布不是“DDL 最终能执行完”,而是等待出现时能够在业务堆积前找到链路源头并安全退出。

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

目录与阅读设置

记录一个想法

排查 MDL 阻塞链

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

登录哪都通