MySQL出现Waiting for table metadata lock的原因以及解决方法(已亲测)如果你管过线上MySQL大概率见过这个让人血压升高的状态Waiting for table metadata lock。业务方在群里问接口怎么突然超时了你上服务器执行show processlist一排会话卡在这个状态上后面的查询越堆越多连接数直接被打满。我第一次碰到这个情况时第一反应是表被锁了于是赶紧去看show open tables where in_use 0结果什么都没发现。折腾了快两个小时才摸到门道。这篇文章把我踩过的坑、排查思路和实际解决步骤完整写出来希望你再遇到它时能在十分钟内定位根因并解决问题。1. 先弄明白metadata lock是什么为什么一个查询也能堵住整条业务链路很多人把Waiting for table metadata lock和InnoDB的ROW LOCK行锁混在一起这是第一个误区。行锁锁的是数据行而metadata lock简称MDL锁的是表结构本身是MySQL在5.5版本引入的元数据锁机制5.6、5.7版本里它的行为已经非常严格。MySQL为什么要设计MDL核心目的有三个保证事务隔离性、保证数据一致性、保证DDL操作的安全性。举个例子你正在对一张订单表执行ALTER TABLE加字段如果同时有一个事务正在读写这张表两边对表结构的理解就可能不一致——那边还在用旧结构读数据这边已经换成了新结构数据错乱的风险极大。所以MySQL规定任何对表的访问包括SELECT、INSERT、UPDATE、DELETE、ALTER等都必须先获取对应的MDL拿不到就排队等待。MDL有三种主要类型MDL_SHARED_READ共享读SELECT语句在访问表时获取多个会话可以同时持有互相不阻塞。MDL_SHARED_WRITE共享写INSERT、UPDATE、DELETE语句获取允许多个会话同时持有但和MDL_SHARED_READ冲突——也就是说一个会话在执行写操作时另一个会话的SELECT也需要排队。MDL_EXCLUSIVE独占锁ALTER TABLE、DROP TABLE、RENAME TABLE等DDL语句获取它与任何其他MDL都互斥必须等待所有共享锁释放后才能拿到。理解了这点你就会明白为什么Waiting for table metadata lock这么难缠它不是InnoDB的行锁超时问题innodb_lock_wait_timeout参数对它是无效的。它本质上是表结构锁的排队问题而排队的原因往往是某个你根本看不见的会话一直没释放MDL。2. 最常见的元凶长事务与未提交事务一个隐藏的SELECT就能卡死DDL我在实际排查中遇到的Waiting for table metadata lock案例里80%以上的根因都是同一个有事务长时间未提交甚至根本没有提交。而且最坑的是这个罪魁祸首往往不是你正在执行的操作而是一个看起来人畜无害的SELECT。2.1 一个普通查询为什么能锁住DDL很多人不理解ALTER TABLE要拿独占锁等一等也正常但SELECT明明早就执行完了为什么DDL还在等问题出在事务边界。MySQL默认是autocommit1单条SQL自动提交此时SELECT获取的MDL_SHARED_READ在语句结束后会立即释放。但如果你在某个客户端里执行了BEGIN或START TRANSACTION之后又执行了一条SELECT这条SELECT获取的MDL_SHARED_READ会一直持有到事务提交或回滚为止。场景还原一下-- 连接A BEGIN; SELECT * FROM order_info WHERE id 100; -- 然后什么都不做既不COMMIT也不ROLLBACK这个时候连接A的事务一直开着order_info表上的MDL_SHARED_READ锁一直没释放。另一边运维同学执行ALTER TABLE order_info ADD COLUMN remark VARCHAR(255);ALTER TABLE需要MDL_EXCLUSIVE但表上还挂着共享读锁只能一直等待。于是这个DDL的状态就是Waiting for table metadata lock。2.2 隐蔽性极强事务打开但没有任何活动语句有一种更隐蔽的情况事务里执行过SQL但SQL早就执行完了事务还开着。从processlist里看这个连接的Command是SleepInfo是NULL看起来完全空闲但它的MDL就是没释放。我自己就遇到过这么一次报表系统每天凌晨跑批其中一段逻辑是先查一批数据然后循环逐条更新框架用的是连接池整个方法标了Transactional。结果某次代码改动后某条数据一直查不到方法抛了异常但事务没有正确回滚连接池里的这条连接就一直挂着。白天上班后我执行DDL加索引死活加不上去排查了半天才发现是凌晨时候遗留的僵尸事务。这种情况下SHOW FULL PROCESSLIST根本看不到它占用了MDL因为Info为空。必须借助performance_schema才能锁定真凶。2.3 快速定位从processlist到内部进程表定位长事务最快的方法是按以下顺序执行-- 第一步查看所有执行中的SQL SHOW FULL PROCESSLIST; -- 第二步查正在运行但没有提交的事务 SELECT * FROM information_schema.innodb_trx\G;innodb_trx表里会列出所有当前未结束的事务看trx_started字段就可以知道事务开了多久trx_query字段可以知道这个事务最后执行的SQL是什么。如果这个表里有一条记录trx_started已经是一两个小时之前了那基本就是它卡住了你的DDL。2.4 为什么一定要先查binlog这里有个细节如果你的实例开了GTID模式并开启了binloginnodb_trx还会配合SHOW MASTER STATUS\G来确认事务对应的Executed_Gtid_Set但这通常只对排查主从复制类问题有用。平时排查MDLinnodb_trx加SHOW PROCESSLIST已经足够定位了。3. 冷门但高频的诱因SLEEP连接、大查询与被阻塞的阻塞长事务是最大的元凶但它不是唯一的。我梳理了这些年碰到的高频场景下面三个坑同样经常出现甚至更难排查。3.1 手滑执行了SELECT SLEEP()或者前端请求没断开有开发者为了测试接口性能直接在数据库连接里执行SELECT SLEEP(600);在MySQL 5.7版本里SLEEP函数执行期间会话持有目标表上的MDL_SHARED_READ如果它正在SLEEP的那张表恰好是你要做DDL的表你的DDL就只能干等600秒。更常见的情况是应用服务器上的连接池因为网络问题没有断开连接其实已经死了但MySQL服务端不知道仍然认为会话存活着它之前查询过某张表就会一直占据该表的MDL。面对这类情况SHOW FULL PROCESSLIST会直接显示SELECT SLEEP(600)在Info字段里一目了然。对于连接池死连接同样要借助performance_schema的events_statements_current来定位它最后执行了哪条SQL。3.2 无索引大查询一条慢SQL让MDL寸步难移这个坑比上面的更隐蔽。一条全表扫描的SELECT执行时间要10分钟。在这个10分钟里它一直持有表上的MDL_SHARED_READ。运维想给这张表加个索引结果只能看到Waiting for table metadata lock。这类场景里processlist里能看到一个大查询还在Sending data阶段你觉得等它查完就好了结果它在Sending data里卡了15分钟都没结束。更麻烦的是如果同时还有其他业务在写这张表写操作会和这个长SELECT互相等待最终形成锁等待链。3.3 阻塞的传递效应新的SELECT也被堵在门外MDL最恐怖的一点是阻塞会传染。假设连接A持有MDL导致连接B的ALTER等待中此时连接C执行一条普通的SELECT它同样需要获取MDL。因为连接B的ALTER排在等待队列里并且它要的是独占锁MySQL会把后续的所有共享读/写请求也一起阻塞掉以保证后面来的请求不会插队拿到锁然后让ALTER继续饿死。这意味着一个长事务引发的MDL问题如果不及时处理会在十几秒内把整个业务的所有读写请求全部堵住连接数飙升数据库连接池被打爆应用大面积报错。4. 亲测有效的完整排查链路从一团乱麻到揪出真凶有一次在某公司的订单系统上排查故障完整的排查链路我记录了下来可以说是最典型的一次MDL演练。4.1 当时的现场故障反馈是下单接口全部超时数据库连接数从平时的100左右涨到了500连接池已经被打满。执行SHOW FULL PROCESSLIST后看到的结果是几十个会话都是Waiting for table metadata lock只有一两条是正在执行INSERT语句但也卡住了。这个画面很有迷惑性因为几乎整个processlist都被Waiting刷屏你根本不知道哪个才是源头。4.2 按顺序执行的排查步骤第一步查information_schema.innodb_trxSELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx\G;结果显示有一笔事务ID为要小很多的事务trx_started大约是40分钟前trx_query为NULLtrx_mysql_thread_id指向了一个Sleep状态的连接。这个会话40分钟前执行过SQL但事务一直没提交。第二步拿到这个连接ID后直接查看它最后执行过的SQL-- 开启performance_schema的语句事件采集如果没开的话 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%; -- 查看指定会话的最近语句 SELECT THREAD_ID, EVENT_NAME, SQL_TEXT, MYSQL_ERRNO FROM performance_schema.events_statements_current WHERE THREAD_ID (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID 123);这条SQL会返回那个连接当前正在执行的语句。如果是Sleep状态events_statements_current通常为空你可以查events_statements_history里面会保留该连接最近执行过的几条语句记录。第三步确认根因。最终查到那个连接最后执行的是一条UPDATE语句从语句内容看是框架在更新订单状态但事务未提交。再配合代码排查发现是开发在事务方法里捕获了异常却没有主动回滚导致事务一直挂起。第四步确认这个会话是否可以杀掉。因为是应用连接池的连接杀掉后应用会自动重新建立新连接不会影响数据一致性。我直接执行KILL 123;kill之后SHOW FULL PROCESSLIST里的所有Waiting for table metadata lock瞬间消失ALTER语句马上执行完成连接数在几分钟内回到了正常水位。4.3 为什么我没有直接kill那个ALTER这里有个容易踩的坑考谁阻塞了谁的时候千万别把ALTER那个连接杀掉因为这个阻塞源头不是它它是受害者。杀掉ALTER虽然能让当前会话不再Waiting但你后续还是要做DDL只要根因事务还在问题会原样复现而且你断开ALTER后所有排队的新请求都会被放进来恢复瞬间可能出现一波压力毛刺。顺带说一句实操时如果你的MySQL版本支持5.7以上的performance_schema.metadata_locks表可以直接一锤定音SELECT * FROM performance_schema.metadata_locks;这张表会直接列出所有当前持有的MDL和正在等待的MDL包含OBJECT_NAME表名、LOCK_STATUSGRANTED表示已持有、PENDING表示在等待、OWNER_THREAD_ID持有者线程ID。5. 解决办法与实战参数三种处理策略的适用场景定位到根因后怎么处理是一门学问。不同场景适合不同的策略我用过下面三种效果都验证过。5.1 临时止血精准KILL持有MDL事务的连接适用于能准确找到事务连接且确认杀掉不会产生数据一致性问题。-- 找出未提交事务对应的连接ID SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_state RUNNING; -- 确认连接信息避免误杀正常业务线程 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE ID 上一步查到的trx_mysql_thread_id; -- 杀掉 KILL thread_id;注意KILL之前最好确认这个连接上没有活跃的update还没提交。如果事务正在执行大UPDATE或大INSERTKILL之后MySQL会回滚该事务但这可能需要一些时间如果这个事务已经执行了很长时间回滚也需要不少代价。提示不要贸然执行KILL一个正在跑大事务的会话尤其是写入量大的业务。回滚操作会继续进行期间依然持有MDL不会因为KILL就立刻释放。5.2 设置合理的锁等待超时加一道护栏MySQL默认的lock_wait_timeout是31536000秒也就是一年。这意味着一条ALTER语句一旦进入Waiting for table metadata lock状态会一直傻等到天荒地老。合理设置超时时间能避免因一次事故拖垮整个应用。-- 会话级超时10秒 SET SESSION lock_wait_timeout 10; -- 全局级建议在配置文件里持久化 SET GLOBAL lock_wait_timeout 10;lock_wait_timeout表示获取元数据锁的等待时间超时后MySQL会返回错误ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。这样一来执行DDL的会话不会无限期堆积在Waiting状态DBA也能及时收到报错快速介入排查而不是等业务彻底不可用了才发现。5.3 规避策略避开高峰窗口用在线DDL工具如果业务不允许中断而你又要执行大表的DDL可以尝试采用更温和的工具来做。这类工具的本质是先创建一张新表然后通过触发器或解析binlog的方式将增量数据同步到新表最后用RENAME TABLE完成切换。由于整个过程中原表上始终维持着正常的DML操作MDL等待的时间被压缩到了极短的窗口期几乎不会对业务产生影响。不过说实话如果你的表只有几万行直接用ALTER就行不用考虑这些工具引入它们反而会让运维复杂度变高。它们的优势主要体现在几百GB甚至上TB的大表上。5.4 预防性方案监控与告警处理完一次之后一定要把预防手段补上。我现在在监控系统里加了两条告警规则效果很好规则1information_schema.innodb_trx中的事务存活时间超过30秒就告警规则2processlist里出现Waiting for table metadata lock状态就立即告警。第一条解决长事务隐患第二条在MDL发生时第一时间通知值班人员。两条规则都只需要一条SQL配合定时任务就能实现成本极低但能让你在下一次事故前就察觉到风险。-- 告警规则1的查检SQL每30秒执行一次 SELECT count(*) FROM information_schema.innodb_trx WHERE trx_started NOW() - INTERVAL 30 SECOND; -- 告警规则2的查检SQL每1分钟执行一次 SELECT count(*) FROM information_schema.processlist WHERE STATE Waiting for table metadata lock;6. 复盘总结这类问题为什么难排查以及我的经验建议从我的个人经验来看Waiting for table metadata lock之所以坑人有几个关键原因第一它和行锁不一样innodb相关的锁监控统统无效你惯用的那些排行锁的经验都用不上。第二持有者往往处于什么都不干的状态processlist里看不到任何SQL天然就隐身了。第三阻塞具有扩散性你看到的几十个Waiting往往都是受害者真凶只有一个而且藏在最不起眼的地方。排查建议就一条永远先检查长事务别在那一堆Waiting里挨个猜。直接查innodb_trx看有没有老寿星级别的未提交事务如果没有再看metadata_locks表的PENDING状态再不行排查有没有大查询或者SLEEP时间长到离谱的连接。我曾见过有同事遇到MDL问题后把数据库实例重启了一遍。那确实能解决因为重启后所有事务都断了MDL自然全部释放。但代价是所有连接瞬间中断、未提交事务全部回滚、主从可能重新同步对业务的影响比MDL本身还大。重启是最不推荐的手段除非你已经被高可用方案保护得很好并确认可以接受闪断。最后分享一个小细节如果你在代码里用连接池并且显式开了事务务必用try-with-resources或者try加finally来保证commit或rollback一定会执行。很多时候MDL问题的根因不在数据库而在一个忘了提交事务的代码分支上。管好事务边界MDL问题至少能少一半。