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
准备一个测试表:
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';预期结果
Query OK, <VALUE> rows affected
Database changed
Query OK, <VALUE> rows affected
Query OK, <VALUE> rows affected<VALUE> 会因对象是否已存在、数据是否发生更新而变化。随后在会话 A 中开启事务并访问该表,但暂时不要提交:
USE mdl_demo;
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;预期结果
+----+--------+
| id | status |
+----+--------+
| 1 | NEW |
+----+--------+
1 row in set在会话 B 中执行 DDL:
ALTER TABLE mdl_demo.orders
ADD COLUMN note VARCHAR(100) NULL;预期结果
会话保持等待,直到阻塞它的事务提交、回滚或连接被终止。这个实验的重点不是 DDL 本身耗时,而是它在真正执行前就可能卡在“Waiting for table metadata lock”。
从等待者反查持锁者
先确认 MDL instrumentation 已启用。MySQL 8.x 通常可通过 performance_schema.metadata_locks 直接观察已授予和等待中的元数据锁。
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;预期结果
+---------------+-------------+----------------------+---------------+-------------+----------------+------------------+------------------+---------------------------------+--------------------------------------+
| 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,也可以直接查看表级锁等待关系,字段更接近日常排障需要:
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';预期结果
+---------------+-------------+-------------+-----------------------------------+--------------+------------------+------------------------------+
| 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。对于已经失控、无人可处理的连接,再考虑由有权限的管理员终止阻塞连接。
KILL CONNECTION <ID>;预期结果
Query OK, 0 rows affected连接断开后,MySQL 会清理会话并回滚未提交事务,相关 MDL 随事务结束而释放。此时会话 B 的 DDL 应从等待状态继续执行。
验证发布已经真正恢复
DDL 返回成功后,不要只看客户端提示,还应验证结构已变化,并确认目标表不再存在 PENDING 的 MDL。
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';预期结果
+--------+--------------+------+-----+---------+-------+
| 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 问题从发布现场的临时救火,变成可提前发现和可控处置的风险。