mysql如何解决Metadata lock等待导致的锁表_排查未结束的select或dump进程-mysql教程-PHP中文网

show processlist 看不到阻塞源是因为 mdl 锁由服务层管理，不显示为“locked”，而是在 performance_schema.metadata_locks 中体现；select（尤其 rr 级别）、mysqldump --single-transaction、information_schema 查询等均会隐式持有 mdl_read 锁，导致 ddl 卡在 waiting for table metadata lock。

mysql如何解决metadata lock等待导致的锁表_排查未结束的select或dump进程

为什么 `SHOW PROCESSLIST` 看不到阻塞源，但表就是锁着？

MySQL 的 METADATA LOCK（MDL）等待不会出现在 State 列为 Locked 的传统锁表现中，它独立于事务锁和表锁，由服务层统一管理。当你执行 ALTER TABLE、DROP TABLE 或 TRUNCATE TABLE 时，如果此时有长事务、未提交的 SELECT（尤其在 REPEATABLE READ 隔离级别下）、或正在运行的 mysqldump 进程持有 MDL 读锁，DDL 就会卡在 Waiting for table metadata lock 状态。

常见错误现象：SHOW PROCESSLIST 显示 DDL 进程状态是 Waiting for table metadata lock，但找不到明显长时间运行的查询；或者看到几个 Query 是 SELECT，Time 值很大，却没被标记为 “killed” 或 “Sleep”。

SELECT 本身不显式加锁，但在 RR 隔离级别下，事务开启后第一次 SELECT 会隐式获取该表的 MDL_READ 锁，并持续到事务结束
mysqldump --single-transaction 默认启用，它靠 START TRANSACTION WITH CONSISTENT SNAPSHOT 维持一致性，也会对所有 dump 中涉及的表加 MDL_READ 锁，直到 dump 结束
INFORMATION_SCHEMA 查询（如查 TABLES、COLUMNS）也可能触发短暂但关键的 MDL 请求，若并发高且慢，会成为隐形瓶颈

怎么定位真正持有 MDL 的会话？

MySQL 5.7+ 提供了 performance_schema 中的 metadata_locks 表，它是唯一能直接看到谁在持有什么 MDL 的途径。默认可能未启用，需确认并打开：

SELECT * FROM performance_schema.setup_actors WHERE HOST = '%';
UPDATE performance_schema.setup_actors SET ENABLED = 'YES' WHERE HOST = '%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

然后查锁持有者：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SELECT 
  OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS,
  THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB
FROM performance_schema.metadata_locks m
JOIN performance_schema.threads t ON m.OWNER_THREAD_ID = t.THREAD_ID
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'your_table';

重点关注 LOCK_STATUS = 'GRANTED' 的行，对应正在持有锁的会话
结合 PROCESSLIST_ID 去 SHOW PROCESSLIST 查具体 SQL 和 Time
若 PROCESSLIST_USER 是 NULL，很可能是 mysqldump 或后台线程（如复制 IO/SQL 线程），需进一步比对 PROCESSLIST_COMMAND 字段（需查 threads 表扩展字段）

`mysqldump` 没报错却卡住，是不是它在锁表？

是的，而且非常隐蔽。mysqldump --single-transaction 在开始 dump 前会对第一个表执行 START TRANSACTION，然后按顺序 dump 所有表 —— 但它对后续表的访问仍复用同一个事务快照，因此从第一个 START TRANSACTION 开始，整个 dump 过程中所有被 dump 的表都持续持有 MDL_READ 锁。

典型表现：mysqldump 进程在 SHOW PROCESSLIST 中显示 Command = Query、State = Sending data、Time 持续增长，但没报错；与此同时，另一个会话执行 ALTER TABLE 就卡住。

不要只看 Command = Sleep 的会话 —— dump 很少进入 Sleep
检查 PROCESSLIST_INFO（需开启 performance_schema.threads.PROCESSLIST_INFO）是否包含 DUMP 或大段 SELECT 语句
临时解决：杀掉 dump 进程（KILL QUERY xxx 或 KILL xxx），但会导致 dump 文件损坏；长期方案是改用 --skip-lock-tables + --single-transaction（仅适用于 InnoDB），或分表 dump

如何避免下次又被 MDL 卡死？

MDL 等待本质是“写操作等读操作结束”，不能靠调大超时参数治本，得从使用习惯和监控入手：

禁止在业务高峰期执行 DDL；用 pt-online-schema-change 或 gh-ost 替代原生 ALTER
应用端避免长事务：设置 wait_timeout 和 interactive_timeout（如 300 秒），并在 ORM 层强制事务及时 COMMIT 或 ROLLBACK
定期巡检：用脚本查 performance_schema.metadata_locks 中 LOCK_STATUS = 'GRANTED' 且 THREAD_ID 对应会话 TIME > 60 的异常持有
备份策略调整：mysqldump 加 --lock-wait-timeout=10（对 MDL 无效），不如改用 --skip-lock-tables + --single-transaction 并确保 dump 时间可控