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

MySQL Metadata Lock 排查：为什么表被锁了，你却找不到“凶手”？

当 DDL 操作卡在“Waiting for table metadata lock”时，使用 SHOW PROCESSLIST 常常找不到阻塞源。这是因为 MDL 锁由 MySQL 服务层管理，其状态不会显示为“Locked”，而是记录在 performance_schema.metadata_locks 表中。诸如长时间运行的 SELECT（尤其在 REPEATABLE READ 级别）、使用 --single-transaction 参数的 mysqldump 备份、以及查询 INFORMATION_SCHEMA 等操作，都会隐式持有 MDL_READ 锁，从而导致 DDL 语句被阻塞。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

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

许多数据库管理员都曾面临这一棘手场景：ALTER TABLE 命令长时间等待，但在 SHOW PROCESSLIST 的输出中却找不到任何明显持有表锁的进程。问题的根源在于 METADATA LOCK（MDL）的特殊工作机制。这种锁由 MySQL 服务层统一管理，独立于我们熟知的 InnoDB 行锁或 MyISAM 表锁体系，因此不会在传统的锁状态信息中显示为 Locked。

当你执行 DROP TABLE、TRUNCATE TABLE 或 ALTER TABLE 等 DDL 语句时，如果此时存在“隐形”的读操作，DDL 就会陷入 Waiting for table metadata lock 的等待状态。哪些操作属于“隐形”读操作呢？

• 长时间运行的 SELECT 查询：特别是在 REPEATABLE READ 事务隔离级别下，事务一旦开始，其首次执行的 SELECT 就会隐式获取相关表的 MDL_READ 锁，并且该锁会一直持续到整个事务提交或回滚。一个被遗忘提交的长事务，往往是导致锁等待的元凶。
• 使用 mysqldump --single-transaction 进行备份：这个常用参数为了确保备份数据的一致性，会开启一个事务快照，并对备份涉及的所有表持有 MDL_READ 锁，直到整个备份过程完成。
• 查询 INFORMATION_SCHEMA 系统表：访问如 TABLES、COLUMNS 等系统表时，也可能触发短暂的 MDL 锁请求。在高并发或存在慢查询的环境中，这些短暂的锁请求可能意外地成为阻塞 DDL 的瓶颈。

因此，典型的故障现象是：DDL 进程在等待，而 SHOW PROCESSLIST 中要么看不到长时间运行的查询，要么只能看到几个 Time 值很大但状态显示为正常（如 Sending data）的 SELECT 会话，它们未被标记为“killed”或“Sleep”，极具迷惑性。

如何定位真正持有 MDL 锁的会话？

既然常规诊断方法失效，就需要借助官方提供的“侦探”—— performance_schema.metadata_locks 系统表。这是在 MySQL 5.7 及以上版本中，直接查看 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';

启用性能模式（Performance Schema）的相关监控后，即可通过以下查询精准定位锁的持有者：

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 或查询 information_schema.processlist 表，查看该会话正在执行的具体 SQL 语句及其已运行时间(Time)，真相往往就此揭晓。
• 如果发现 PROCESSLIST_USER 字段为 NULL，这通常是正常的，它可能对应着 mysqldump 进程或数据库后台线程（如复制线程）。此时需要进一步查看 performance_schema.threads 表中的 PROCESSLIST_COMMAND、PROCESSLIST_INFO 等字段来确认其具体身份。

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

是的，mysqldump 堪称 MDL 锁问题的“头号伪装者”。当使用 --single-transaction 参数进行备份时，它会在开始备份第一个表时启动一个事务，然后按顺序备份所有表。关键在于，整个备份过程都复用同一个事务快照，这意味着从执行第一个 START TRANSACTION 开始，所有被备份的表就一直受到 MDL_READ 锁的保护，直到整个备份任务彻底结束。

它的典型表现是：在 SHOW PROCESSLIST 中，mysqldump 进程的 Command 显示为 Query，State 是 Sending data，Time 值持续增长但没有任何错误信息。与此同时，另一个会话尝试执行 ALTER TABLE 等 DDL 操作就会立即被阻塞。

排查时需注意：

• 不要只关注 Command = Sleep 的会话，dump 进程在备份数据时很少进入 Sleep 状态。
• 可以检查 performance_schema.threads 表中的 PROCESSLIST_INFO 字段（需确保已开启相关监控），其中可能包含“DUMP”关键字或大段的 SELECT 语句片段。
• 临时解决方案：果断使用 KILL QUERY 或 KILL 命令终止 dump 进程，但需注意这会导致生成的备份文件不完整或损坏。长期优化方案：对于全 InnoDB 表，可以尝试结合使用 --skip-lock-tables 和 --single-transaction 参数；或者，更稳妥的做法是将大表拆分进行备份，减少单次备份任务持锁的范围和持续时间。

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

MDL 等待的本质是“写操作（DDL）等待读操作释放元数据资源”，单纯调大 lock_wait_timeout 参数只是权宜之计。要根治这个问题，需要从运维习惯和监控体系两方面入手：

• DDL 操作窗口化：严格禁止在业务高峰期执行原生 DDL 语句。建议使用 pt-online-schema-change 或 gh-ost 这类在线表结构变更工具，它们通过创建影子表并同步数据的方式，能有效规避 MDL 锁冲突，实现业务无感知的 schema 变更。
• 严格管控长事务：在应用端，合理设置数据库连接的 wait_timeout 和 interactive_timeout 参数（例如设置为 300 秒）。同时，在 ORM 框架或数据库连接层代码中，确保事务及时提交(COMMIT)或回滚(ROLLBACK)，避免产生悬挂事务。
• 建立主动监控告警：定期通过脚本巡检 performance_schema.metadata_locks 表，重点关注那些 LOCK_STATUS = 'GRANTED' 且对应会话持续时间(TIME)超过设定阈值（如 60 秒）的异常情况，及时发现潜在的长事务或慢查询。
• 优化备份策略：为 mysqldump 添加 --lock-wait-timeout 参数对 MDL 锁无效。更有效的做法是，在确保所有表均为 InnoDB 引擎的前提下，使用 --skip-lock-tables --single-transaction 组合参数，并严格控制备份任务的执行时间窗口，避免与核心业务高峰时段重叠。

最后，分享一个最容易被忽略的“坑”：即使你没有执行任何长时间的 SELECT 查询，只要开启了事务（哪怕只执行了一个 BEGIN 或 START TRANSACTION），就已经对后续访问的表产生了 MDL_READ 锁。这类“空事务”往往隐藏在连接池初始化、健康检查脚本或某些框架自动开启的事务逻辑中，难以察觉，需要数据库管理员在排查时格外留意。