MySQL表元数据锁Waiting for Table Metadata Lock问题排查与解决

本文详细记录了一次在MySQL数据库操作中，因修改表结构而遭遇“Waiting for table metadata lock”问题的排查过程与解决方案。通过分析锁产生的根本原因，并借助系统表进行精准定位，最终成功解决该问题。

问题现象

在为一张表添加自增主键字段时，执行DDL语句被长时间阻塞，状态显示为 Waiting for table metadata lock。

初步使用 SHOW FULL PROCESSLIST; 命令查看线程，确认存在此等待状态。然而，在进程列表中并未发现针对同一张表进行修改或其他可能持有锁的明显操作。

排查思路与分析

通常，元数据锁（Metadata Lock， MDL）被长时间持有可能由以下原因导致：

• 存在未提交的事务。
• 存在长时间运行的查询。
• 之前失败的操作未正确释放锁。

首先，尝试检查是否有未提交的事务锁定了表，通过查询 information_schema.innodb_trx 表来寻找活跃事务：

mysql> select * from information_schema.innodb_trx\G

查询结果显示为空，这意味着问题并非由典型的未提交事务引起。进一步的排查需要深入到数据库/中间件的performance_schema中寻找线索。

根据经验，这种情况很可能源于一个“失败的事务”。具体场景是：在一个显式事务中，对目标表执行了一个失败的操作（例如，查询了一个不存在的字段）。虽然该失败语句会导致事务实际并未正常启动，但语句执行时获取到的元数据锁可能依然有效且未被释放。

定位问题会话

为了验证这个猜想，可以通过查询 performance_schema.events_statements_current 表来查找最近执行过的失败语句。

select * from performance_schema.events_statements_current\G

执行查询后，获取到了关键信息：

从结果中可以清晰地看到一条状态为错误的SELECT语句，其THREAD_ID为104。这证实了运维/DevOps的猜想：正是这个执行失败但未释放锁的会话（Session）阻塞了后续的DDL操作。

在本案例中，该查询来自一个应用程序连接，报错后连接未断开，锁也一直持有。关闭该程序连接后，DDL操作立即恢复正常。

解决方案与通用查询

找到问题根源后，解决方法很直接：终止持有元数据锁的问题会话。

1. 精准Kill：通过上述查询获取到的 THREAD_ID，可以在 performance_schema.threads 表中找到对应的 PROCESSLIST_ID，然后执行 KILL [PROCESSLIST_ID]; 命令。也可以直接终止被阻塞的DDL操作所在会话。

2. 通用查询语句：以下提供几个实用的查询，用于全面排查元数据锁等待问题。

   查询1：关联metadata_locks与threads表

   select PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DB,OBJECT_NAME,PROCESSLIST_COMMAND,PROCESSLIST_ID 
   from metadata_locks mdl 
   join threads thr on mdl.OWNER_THREAD_ID= thr.THREAD_ID;

   查询2：详细的InnoDB锁等待关系查询
   此查询可以展示谁阻塞了谁，以及等待了多久，信息非常全面。

   SELECT   
   COUNT(1) AS '数量',  
   p2.`HOST` AS '被阻塞方主机',  
   p2.`USER` AS '被阻塞方用户',  
   r.trx_mysql_thread_id AS '被阻塞方线程号',  
   r.trx_query AS '被阻塞的语句',  
   TIMESTAMPDIFF(SECOND,r.trx_wait_started,current_timestamp) AS '等待时间',  
   p.`HOST` AS '阻塞方主机',  
   p.`USER` AS '阻塞方用户',  
   b.trx_mysql_thread_id AS '阻塞方线程',  
   b.trx_query AS '阻塞方语句'  
   FROM information_schema.INNODB_LOCK_WAITS w  
   INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id   
   INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id   
   INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id   
   INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_id AND m.lock_trx_id=r.trx_id   
   INNER JOIN information_schema.`PROCESSLIST` p ON p.ID = b.trx_mysql_thread_id   
   INNER JOIN information_schema.`PROCESSLIST` p2 ON p2.ID = r.trx_mysql_thread_id   
   GROUP BY r.trx_query;

3. 检查长时间空事务：如果以上查询均无结果，建议直接查看 information_schema.innodb_trx 表，寻找那些已开启但长时间无任何操作的空事务，它们也可能导致阻塞。

   select * from information_schema.innodb_trx;

总结

“Waiting for table metadata lock”是MySQL DBA和开发者常遇到的经典问题。本次排查的核心在于，不仅要关注运行中的事务和查询，更要留意那些执行失败但会话未释放的操作。performance_schema 库提供了强大的诊断能力，是解决此类数据库/中间件疑难杂症的利器。在应用程序开发中，确保数据库连接和事务被正确管理、及时释放，是预防此类问题的根本。