MySQL / 系统课时

排查元数据锁阻塞

排查元数据锁阻塞

Metadata Lock(MDL)保证查询看到的表定义在语句或事务期间保持一致,但它也会让一个看似无害的未提交事务卡住 DDL。本课复现“发布 ALTER TABLE 一直等待”的典型现场,并沿等待链找到真正的阻塞会话。

学习目标

  • 理解 DML、事务与 DDL 之间的 MDL 兼容关系
  • 用 Performance Schema 找到等待 MDL 的会话和持锁者
  • 按安全顺序解除阻塞并验证 DDL 恢复推进

先看懂为什么 DDL 会停住

普通 SELECT、INSERT、UPDATE 等语句会获取与表对象相关的 MDL。事务未结束时,其中一部分锁会一直保留到 COMMIT 或 ROLLBACK。多数 DDL 需要更强的排他元数据锁,因此前面只要有事务仍持有不兼容的 MDL,DDL 就只能排队等待。

更麻烦的是队列效应:一个 DDL 已在等待排他 MDL 后,后续访问同一对象的新请求也可能受到影响,最终把“一个未提交事务”放大成发布停滞和业务堆积。

DDL 等待 Metadata Lock 的会话链

复现一个被长事务卡住的 DDL

准备一个测试表:

sql
CREATE DATABASE IF NOT EXISTS mdl_demo;
USE mdl_demo;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY,
    status VARCHAR(20) NOT NULL
) ENGINE = InnoDB;

INSERT INTO orders (id, status)
VALUES (1, 'NEW')
ON DUPLICATE KEY UPDATE status = 'NEW';

预期结果

text
Query OK, <VALUE> rows affected
Database changed
Query OK, <VALUE> rows affected
Query OK, <VALUE> rows affected

<VALUE> 会因对象是否已存在、数据是否发生更新而变化。随后在会话 A 中开启事务并访问该表,但暂时不要提交:

sql
USE mdl_demo;
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;

预期结果

text
+----+--------+
| id | status |
+----+--------+
|  1 | NEW    |
+----+--------+
1 row in set

在会话 B 中执行 DDL:

sql
ALTER TABLE mdl_demo.orders
    ADD COLUMN note VARCHAR(100) NULL;

预期结果

text
会话保持等待,直到阻塞它的事务提交、回滚或连接被终止。

这个实验的重点不是 DDL 本身耗时,而是它在真正执行前就可能卡在“Waiting for table metadata lock”。

从等待者反查持锁者

先确认 MDL instrumentation 已启用。MySQL 8.x 通常可通过 performance_schema.metadata_locks 直接观察已授予和等待中的元数据锁。

sql
SELECT
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_DURATION,
    ml.LOCK_STATUS,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_TIME,
    th.PROCESSLIST_STATE,
    th.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS th
    ON th.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
  AND ml.OBJECT_SCHEMA = 'mdl_demo'
  AND ml.OBJECT_NAME = 'orders'
ORDER BY
    CASE ml.LOCK_STATUS WHEN 'PENDING' THEN 0 ELSE 1 END,
    th.PROCESSLIST_TIME DESC;

预期结果

text
+---------------+-------------+----------------------+---------------+-------------+----------------+------------------+------------------+---------------------------------+--------------------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_TIME | PROCESSLIST_STATE               | PROCESSLIST_INFO                     |
+---------------+-------------+----------------------+---------------+-------------+----------------+------------------+------------------+---------------------------------+--------------------------------------+
| mdl_demo      | orders      | EXCLUSIVE            | TRANSACTION   | PENDING     | <ID>           | <VALUE>          | <VALUE>          | Waiting for table metadata lock | ALTER TABLE mdl_demo.orders ...      |
| mdl_demo      | orders      | SHARED_READ          | TRANSACTION   | GRANTED     | <ID>           | <VALUE>          | <VALUE>          | <VALUE>                         | <VALUE>                              |
+---------------+-------------+----------------------+---------------+-------------+----------------+------------------+------------------+---------------------------------+--------------------------------------+

定位时先找 LOCK_STATUS='PENDING' 的 DDL,再看同一对象上 GRANTED 的锁属于哪些线程。真正需要处理的通常不是等待 DDL 的线程,而是更早拿到不兼容锁且迟迟没有结束事务的会话。

如果安装了 sys schema,也可以直接查看表级锁等待关系,字段更接近日常排障需要:

sql
SELECT
    object_schema,
    object_name,
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_account,
    sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = 'mdl_demo'
  AND object_name = 'orders';

预期结果

text
+---------------+-------------+-------------+-----------------------------------+--------------+------------------+------------------------------+
| object_schema | object_name | waiting_pid | waiting_query                     | blocking_pid | blocking_account | sql_kill_blocking_connection |
+---------------+-------------+-------------+-----------------------------------+--------------+------------------+------------------------------+
| mdl_demo      | orders      | <ID>        | ALTER TABLE mdl_demo.orders ...   | <ID>         | <VALUE>          | KILL <ID>;                   |
+---------------+-------------+-------------+-----------------------------------+--------------+------------------+------------------------------+

解除阻塞时先结束事务而不是乱杀 DDL

如果会话 A 是测试或确认可安全结束的业务事务,优先让应用正常 COMMIT 或 ROLLBACK。对于已经失控、无人可处理的连接,再考虑由有权限的管理员终止阻塞连接。

sql
KILL CONNECTION <ID>;

预期结果

text
Query OK, 0 rows affected

连接断开后,MySQL 会清理会话并回滚未提交事务,相关 MDL 随事务结束而释放。此时会话 B 的 DDL 应从等待状态继续执行。

验证发布已经真正恢复

DDL 返回成功后,不要只看客户端提示,还应验证结构已变化,并确认目标表不再存在 PENDING 的 MDL。

sql
SHOW COLUMNS FROM mdl_demo.orders;

SELECT COUNT(*) AS pending_mdl_count
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'
  AND OBJECT_SCHEMA = 'mdl_demo'
  AND OBJECT_NAME = 'orders'
  AND LOCK_STATUS = 'PENDING';

预期结果

text
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | bigint       | NO   | PRI | NULL    |       |
| status | varchar(20)  | NO   |     | NULL    |       |
| note   | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

+-------------------+
| pending_mdl_count |
+-------------------+
|                 0 |
+-------------------+

线上治理应把“DDL 等待时间”与“未提交事务年龄”一起监控。发布前尽量缩短业务长事务,DDL 工具设置合理的锁等待策略和失败重试,异常时沿 PENDING 等待者反查 GRANTED 持锁者。这样才能把 MDL 问题从发布现场的临时救火,变成可提前发现和可控处置的风险。

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

目录与阅读设置

阅读设置

17

记录一个想法

排查元数据锁阻塞

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

登录哪都通