开发者日记

外观

元数据锁踩坑实录：DDL 卡住到恢复的排查链路 ​

问题背景：一条加索引语句为什么拖垮业务 ​

凌晨变更窗口，我们给订单明细表执行在线加索引：

sql
ALTER TABLE orders_order_item
  ADD INDEX idx_shop_sku_created (shop_id, sku_id, created_at);

正常情况下，MySQL 8.0 会使用 Instant 或 Inplace 算法，业务影响较小。但这条 DDL 在第一台从库上执行了十几分钟仍未返回，应用侧随后出现大量请求超时：新查询停在 Waiting for table metadata lock，写入线程被拖慢，连接池迅速打满。

根因不是 DDL 本身扫描数据慢，而是它没有拿到表的 元数据锁（Metadata Lock，简称 MDL）。MySQL 访问表时会持有 MDL，用于保护表结构在语句执行期间不被修改。只要有一个未提交事务还占着该表的 MDL，DDL 就必须等待；DDL 排队后，后续访问同一张表的新事务也会被堵住，形成雪崩式 DDL 阻塞。

下面用可复现的 SQL 示例，完整演示持锁会话定位、等待链分析、安全 kill 与 变更窗口 选择。

方案对比：排查 MDL 应该看哪套视图 ​

常见排查方式有三类：

方案能看到什么局限适用场景
SHOW PROCESSLIST当前会话状态、SQL、等待秒数看不到锁对象和完整等待关系快速判断是否出现 MDL 堆积
information_schema.innodb_trxInnoDB 事务开始时间、锁等待对只读事务、自动提交外的空闲事务支持有限找长事务
performance_schema.metadata_locksMDL 类型、对象、持有/等待状态需要开启对应 instrumentation精确定位元数据锁
sys.schema_table_lock_waits表锁等待者与阻塞者依赖 P_S 配置快速生成 kill 建议

生产环境优先使用 performance_schema 与 sys 联动：先确认 DDL 在等哪张表，再找持有 MDL 的会话，而不是看到 Waiting for table metadata lock 就盲目批量 kill。

长事务持有 MDL表结构不可变更DDL 排队等待ALTER TABLE新查询被阻塞连接池耗尽查询 PROCESSLIST确认堆积范围查询 metadata_locks定位持锁会话评估后 KILL执行 DDL 变更

实操步骤：从复现到定位持锁会话 ​

1. 准备测试表并开启 MDL 监控 ​

以下示例可在 MySQL 8.0 测试库直接运行：

sql
-- 建表：模拟订单明细表
CREATE TABLE orders_order_item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  shop_id BIGINT NOT NULL,
  sku_id BIGINT NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 写入少量测试数据
INSERT INTO orders_order_item
  (shop_id, sku_id, order_no, quantity, amount)
VALUES
  (1001, 2001, 'NO202401', 1, 99.00),
  (1001, 2002, 'NO202402', 2, 58.50),
  (1002, 2001, 'NO202403', 1, 99.00);

-- 开启等待事件采集，便于观察 MDL 等待
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_waits_current', 'events_statements_current');

2. 会话 A：制造一个未提交事务 ​

在第一个连接中显式开启事务，执行查询但不提交：

sql
-- 会话 A
BEGIN;
SELECT id, order_no, amount
FROM orders_order_item
WHERE shop_id = 1001;

-- 故意不执行 COMMIT，也不执行 ROLLBACK

很多人以为只读 SELECT 不会造成锁问题。确实，它不会产生常见的 InnoDB 行锁，但事务期间会持有该表的共享 MDL。此时如果连接被应用池放回，服务端仍认为事务活跃，就会成为 DDL 的阻塞源。

3. 会话 B：执行 DDL，观察阻塞 ​

第二个连接执行加索引：

sql
-- 会话 B：会被会话 A 的 MDL 阻塞
ALTER TABLE orders_order_item
  ADD INDEX idx_shop_sku_created (shop_id, sku_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

第三个连接再执行简单查询，也会被堵住：

sql
-- 会话 C：DDL 排队后，新访问也可能等待
SELECT COUNT(*) FROM orders_order_item;

使用下面语句快速确认现场：

sql
SELECT
  id,
  user,
  host,
  db,
  command,
  time,
  state,
  LEFT(info, 200) AS sql_text
FROM information_schema.PROCESSLIST
WHERE info IS NOT NULL
   OR state LIKE '%metadata lock%'
ORDER BY time DESC;

典型状态如下：

text
会话 B：Waiting for table metadata lock，SQL 为 ALTER TABLE ...
会话 C：Waiting for table metadata lock，SQL 为 SELECT COUNT(*) ...
会话 A：Sleep，time 很长，info 为 NULL

注意，真正的阻塞者常常显示为 Sleep，不能只根据当前 info 判断。

4. 查询 MDL 持有与等待关系 ​

执行以下 SQL 找出谁在等、谁在持有：

sql
SELECT
  ml.OBJECT_SCHEMA AS db_name,
  ml.OBJECT_NAME AS table_name,
  ml.LOCK_TYPE,
  ml.LOCK_DURATION,
  ml.LOCK_STATUS,
  ml.OWNER_THREAD_ID,
  pt.PROCESSLIST_ID AS conn_id,
  pt.PROCESSLIST_USER AS user_name,
  pt.PROCESSLIST_HOST AS host_name,
  pt.PROCESSLIST_TIME AS active_seconds,
  LEFT(pt.PROCESSLIST_INFO, 120) AS current_sql
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads pt
  ON ml.OWNER_THREAD_ID = pt.THREAD_ID
WHERE ml.OBJECT_SCHEMA = DATABASE()
  AND ml.OBJECT_NAME = 'orders_order_item'
ORDER BY
  FIELD(ml.LOCK_STATUS, 'PENDING', 'GRANTED'),
  pt.PROCESSLIST_TIME DESC;

结果中通常会看到：

  • 会话 A：LOCK_STATUS = GRANTED，LOCK_TYPE = SHARED_READ，但当前 SQL 可能为 NULL；
  • 会话 B：LOCK_STATUS = PENDING，需要排他 MDL；
  • 会话 C：同样处于 PENDING，被前面的 DDL 队列挡住。

如果实例安装了 sys 库，可以直接查表锁等待：

sql
SELECT
  object_schema,
  object_name,
  waiting_pid,
  waiting_lock_type,
  waiting_query,
  blocking_pid,
  blocking_lock_type,
  blocking_query,
  sql_kill_blocking_connection AS kill_sql
FROM sys.schema_table_lock_waits;

5. 结合事务开始时间判断是否可以终止 ​

不要只看连接空闲时间，还要看事务持续时间：

sql
SELECT
  trx.thread_id,
  th.PROCESSLIST_ID AS conn_id,
  th.PROCESSLIST_USER AS user_name,
  th.PROCESSLIST_HOST AS host_name,
  trx.trx_started,
  TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_seconds,
  trx.trx_state,
  LEFT(th.PROCESSLIST_INFO, 200) AS current_sql
FROM information_schema.innodb_trx trx
JOIN performance_schema.threads th
  ON trx.thread_id = th.THREAD_ID
ORDER BY trx.trx_started ASC;

确认会话 A 是测试连接或无主连接后，再终止：

sql
-- 将 52 替换为实际 blocking_pid
KILL 52;

kill 后，会话 A 回滚，MDL 释放，会话 B 的 DDL 继续执行，会话 C 恢复。测试完成后清理索引：

sql
ALTER TABLE orders_order_item
  DROP INDEX idx_shop_sku_created;

变更窗口：不要只按业务低峰设置时间 ​

DDL 变更窗口应同时满足四个条件：业务写入低峰、长事务少、主从复制有余量、出现阻塞时可快速回退。建议变更前执行以下检查：

sql
-- 1. 查看运行超过 60 秒的事务
SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_old,
  trx_state,
  trx_mysql_thread_id AS pid,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started ASC;

-- 2. 查看目标表是否已有 MDL 等待
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_STATUS,
  OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = '你的库名'
  AND OBJECT_NAME = 'orders_order_item';

-- 3. 查看复制状态，重点关注 Seconds_Behind_Source 或 Seconds_Behind_Master
SHOW REPLICA STATUS\G

对于核心表，建议把 DDL 放在可灰度的从库或使用变更平台执行，并设置 lock_wait_timeout，避免 DDL 无限等待：

sql
-- 只控制 MDL 等待时间，不控制整个 DDL 执行时间
SET SESSION lock_wait_timeout = 10;

ALTER TABLE orders_order_item
  ADD INDEX idx_shop_sku_created (shop_id, sku_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

如果 10 秒内拿不到 MDL，DDL 会报错退出，而不是长时间堵住后续访问。变更平台可以重试，但重试间隔要留出清理长事务的时间。

避坑总结：至少记住这五条 ​

  1. 不要忽略 Sleep 状态的长连接。 常见现象是 PROCESSLIST 中 DDL 显示 Waiting for table metadata lock，但看不到明显正在执行的 SQL。原因是应用开启事务后查询结束却没有提交或回滚，连接进入 Sleep，仍持有 MDL。修正方法是通过 metadata_locks 关联 threads 找 GRANTED 会话，而不是 kill 正在等待的 DDL。

  2. 不要把 lock_wait_timeout 当成 DDL 总超时。 有同学设置后以为整个加索引最多执行 10 秒，实际上它只控制元数据锁等待。DDL 拿到 MDL 后，数据重建、日志应用仍可能持续很久。大表变更仍需评估磁盘、临时空间、复制延迟和业务峰谷。

  3. 不要在高峰期直接对主库执行不可逆的大表 DDL。 即使 ALGORITHM=INPLACE, LOCK=NONE，准备阶段和提交阶段仍需要 MDL；一旦被长事务卡住，后续查询会排队。应先清理长事务，再在变更窗口执行，并准备 KILL 阻塞会话、暂停任务、延迟切流等预案。

  4. kill 会话前要确认回滚代价。 kill 一个大事务不是瞬间消失，InnoDB 可能需要回滚大量修改。若误杀批处理或支付任务，还可能造成业务数据处理中断。应先看 trx_started、来源主机、账号和最近执行的 SQL，必要时联系应用负责人确认。

  5. 主库完成不等于变更结束，必须检查主从延迟。 DDL 在从库通常串行应用，大索引可能造成复制日志积压，表现为 Seconds_Behind_Source 持续增大、只读查询读到旧执行位点。修正方法是在低峰执行、控制同批次 DDL 数量，变更后持续观察复制延迟，延迟未恢复前不要继续下一张核心表。

一次元数据锁事故的本质，往往不是“MySQL 加索引太慢”，而是表结构变更与事务生命周期发生了冲突。把 MDL 排查 SQL 提前准备好，把变更窗口从“日历上的低峰”升级为“锁、事务、复制都满足条件的窗口”，才能让 DDL 从风险操作变成可控操作。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。