MySQL 的Waiting for table metadata lock绝对是 DBA 和开发同学在运维过程中最常撞见的“幽灵”之一。明明 SQL 语句很简单数据量也不大可它就像死机了一样卡在那里show processlist一看State 列赫然写着Waiting for table metadata lock让人瞬间头皮发麻。我前阵子就在一个凌晨的变更窗口里被这个锁折腾得不轻所以打算把这次的排查思路、背后原理和实际解决动作完整梳理一遍。这篇文章没有绕弯子全部是我自己实测过的过程包括怎么定位阻塞源头、怎么安全地“杀掉”持有锁的会话、以及从根上减少这类问题的表结构变更习惯。1. 什么是 metadata lock它锁的到底是什么很多人一听“元数据锁”第一反应是“锁的是表结构”这个方向对但不够完整。MySQL 从 5.5 版本开始引入 MDLMetadata Lock它的核心作用是在访问表对象时对表的定义、结构变更DDL、以及显式事务中涉及的表进行并发控制。简单说MDL 锁保证你在执行ALTER TABLE改表结构的时候不会有其它会话正在读写这张表否则改了结构之后老会话的 SQL 还按照旧结构执行很容易出乱子。你可以把 MDL 锁理解成一个“门禁系统”每个会话在首次访问一张表时要先领一个“出入证”共享 MDL 锁多个会话之间可以同时持有共享锁各自读写互不干扰。可一旦有人要改表结构执行 DDL就得申请“独占 MDL 锁”这时门禁系统会要求所有持证的旧会话先退场退场不干净独占锁就永远等下去表现就是Waiting for table metadata lock。1.1 MDL 的加锁流程与临界时机MDL 加锁的流程是这样的会话 A 执行SELECT、INSERT、UPDATE、DELETE等语句时需要获取表的 MDL 共享锁语句执行完成后立即释放这个没有悬念。会话 B 执行ALTER TABLE、DROP TABLE、TRUNCATE TABLE等 DDL 语句时需要获取 MDL 独占锁而且必须等所有共享锁释放之后才能拿到。一旦 B 在等待独占锁它会形成一个“锁等待队列”。注意后续任何新会话访问该表时申请共享锁也会被堵住这就是“排队效应”。这里有个特别容易被忽略的坑MDL 锁并不仅仅覆盖语句执行期间。如果一个会话开启了一个事务哪怕它只是执行了一条SELECT之后事务一直不提交这个事务所占用的 MDL 共享锁也会一直持有到事务结束。正是因为共享锁不释放DDL 的独占锁永远排不上于是出现卡死。1.2 共享锁、独占锁与兼容矩阵为了照顾刚接触 MySQL 锁机制的同学我把兼容关系整理成一张小表锁类型共享锁S独占锁X共享锁S兼容不兼容独占锁X不兼容不兼容多个SELECT/DML会话之间可以共存因为它们都是 MDL 共享锁。DDL 需要的独占锁和任何形式的共享锁都不兼容必须等所有共享锁释放。这个逻辑其实很像我们平时用的文件编辑多人同时打开文件只读没问题可一旦有人要以独占方式重写文件就得等所有只读的人都关闭文件。谁摊上一个迟迟不关文件的家伙谁就得一直等。2. 复现这个锁的完整排查链路从 processlist 到阻塞源头遇到Waiting for table metadata lock第一步肯定不是急着kill而是先搞清楚到底是谁卡住了谁。我当时的处理路径如下一条命令一条命令走。2.1 先用 show processlist 看现场SHOW FULL PROCESSLIST;重点看State列如果大量连接停在Waiting for table metadata lock先记下它们的Id。这些是最直观的“受害者”但它们不一定是你需要处理的“元凶”。比如我那次看到的结果大致是IdUserHostdbCommandTimeStateInfo1001app_user10.0.0.5:52310order_dbQuery342Waiting for table metadata lockALTER TABLE orders ADD COLUMN remark VARCHAR(255)1002app_user10.0.0.6:52318order_dbQuery342Waiting for table metadata lockSELECT * FROM orders WHERE id 123451003admin10.0.0.9:52320order_dbSleep1200(空)(空)ALTER TABLE等锁是可以理解的可为什么连SELECT也被卡住因为前面说的排队效应——独占锁在等后续共享锁全部排队。这里的核心突破口在于那个Sleep状态的连接1003它时间最长很可能就是持有旧共享锁不撒手的会话。2.2 用 sys.schema_table_lock_waits 精确定位阻塞链直接看 processlist 只能知道谁在等想知道“谁阻塞了谁”建议直接查 MySQL 5.7 以上自带的sys库视图SELECT * FROM sys.schema_table_lock_waits\G这个视图会非常清晰地列出等待锁的会话、持有锁的会话、涉及的表名、以及等待时间。需要注意sys.schema_table_lock_waits是基于performance_schema的如果performance_schema没开启查询会报错或返回空。检查方法SHOW VARIABLES LIKE performance_schema;一般默认是 ON如果是 OFF可以通过my.cnf开启后重启 MySQL生产环境建议保持开启毕竟排查锁问题离不开它。2.3 用 performance_schema 直接追 session 持有锁情况如果sys视图信息不够还可以直接查performance_schema.metadata_locks表这是 MDL 锁的第一现场。MySQL 5.7 中对应的表是performance_schema.metadata_locksMySQL 8.0 里字段稍有调整。我自己常用的查询是SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, SOURCE, OWNER_THREAD_ID, OWNER_EVENT_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA order_db AND OBJECT_NAME orders;LOCK_STATUS是GRANTED表示已持有PENDING表示正在等待。看到GRANTED状态的共享锁对应LOCK_TYPE SHARED_READ或SHARED_WRITE再通过线程 ID 关联到具体连接进程。关联方式SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_COMMAND, PROCESSLIST_STATE, PROCESSLIST_TIME FROM performance_schema.threads WHERE THREAD_ID 上一步查到的OWNER_THREAD_ID;到了这一步基本就锁定了“真凶”——通常是某个长时间Sleep但事务没提交的会话或者一个正在跑超大事务的会话。注意Sleep状态不代表事务结束只要没执行COMMIT锁就还在。2.4 借助 information_schema 检查事务状态有时 processlist 里显示的是Sleep你没法直接看出它是否还在事务中。这时可以联合查两个库SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx;如果这个Sleep连接对应的trx_state是RUNNING且trx_started是很早的时间那么它就是“持有 MDL 共享锁却迟迟不提交”的元凶基本实锤。3. 最典型的场景之一长事务未提交导致的 DDL 卡死在日常运维中我遇到最多的情况就是开发同学在凌晨跑一个ALTER TABLE加字段结果一直等等到天亮还没结束。查processlist发现一堆连接堵在Waiting for table metadata lock。为什么会这样十有八九是前一晚有业务会话开启了事务执行了几条查询之后既没提交也没回滚直接挂在那里把订单表orders的 MDL 共享锁一直攥着。3.1 一个完整的“凌晨变更”案例分析我当时接手的场景如下变更需求给orders表加一个remark字段长度VARCHAR(255)默认值空字符串。SQL 语句ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT ;现象执行后 5 分钟没反应10 分钟没反应查processlist看到Waiting for table metadata lock。排查步骤一步步走下来SHOW FULL PROCESSLIST发现ALTER已在Waiting。查sys.schema_table_lock_waits发现持有者是 ID 为1003的会话。查information_schema.innodb_trx发现1003对应一个trx_started为两小时前的事务。和业务方确认后这个连接是某个定时任务的残留事务一直没有COMMIT。到了这一步解决方案无非两条路等事务自然结束或者主动断开该连接。对于凌晨变更等肯定是等不了的只能联系业务方确认无活动交易后选择KILL掉对应的连接。3.2 KILL 连接之后的连锁反应与注意点KILL 操作要谨慎不是随便杀。要确认这个连接对应的事务是否存在未保存的写操作如果只是一条SELECT开启的只读事务断开连接不会造成数据丢失如果有未提交的写事务断开后 MySQL 会回滚该事务可能引发业务方面的数据不一致必须提前和业务方对齐。我当时的做法KILL 1003;然后立刻再看SHOW FULL PROCESSLISTALTER TABLE瞬间从Waiting for table metadata lock变成正常执行状态。整个过程不到 3 秒新的remark字段就加成功了。这里有个实操细节KILL连接只是断开客户端连接并不会主动干掉已开启的事务。MySQL 在检测到连接断开时会自动回滚该连接上开启但未提交的事务回滚完成后 MDL 共享锁才会释放。所以如果你的阻塞源是一个超大的写事务KILL 之后可能要等一段时间让回滚跑完不要以为 KILL 完就万事大吉要持续观察。4. 另一大典型场景慢查询与备份任务占着 MDL 不放很多人以为只有“显式事务”才会持有 MDL这是个认识误区。一个没有任何显式事务包裹的长查询只要它执行时间足够长依然会把 MDL 共享锁攥到底。等到某个 DDL 想要获取独占锁时同样会被卡住。4.1 慢查询占用与 DDL 的互相踩踏举个例子业务方凌晨跑了一个复杂的报表查询对orders表做全表JOIN和聚合这个查询本身可能要跑 20 分钟。它不需要事务包裹因为单条SELECT结束时锁才释放。问题是20 分钟内如果恰好有 DBA 执行ALTER TABLE orders ...那么这个 DDL 就要疯狂等这个慢查询跑完。这种情况下尤其要注意一个反直觉的点慢查询可能已经跑完了但它的事务还没结束。MySQL 默认开启autocommit单条语句结束立即释放锁但如果这个慢查询是在一个显式START TRANSACTION里那么语句执行完事务仍然存活。所以看到Sleep状态并不可怕真正可怕的是“看起来睡了很久但事务还活着”。4.2 备份工具mysqldump / Xtrabackup的神奇锁行为还有一个常被忽视的阻塞源是备份任务。mysqldump --single-transaction在备份 InnoDB 表时会开启一个一致性的REPEATABLE READ事务通过START TRANSACTION WITH CONSISTENT SNAPSHOT拿到一个快照。这个事务会保持到备份结束可能持续几十分钟甚至几小时。在此期间如果对备份涉及的表执行 DDL就会撞上 MDL 锁排队。我碰到过一次很经典的案例同事用mysqldump做每日备份备份脚本跑了一个半小时还没结束。与此同时另一个同事在变更窗口执行表结构变更结果整个变更卡了整整一个晚上。排查到最后才发现阻塞源是一个Sleep状态的mysqldump会话。遇到这类情况处理方法首先要判断备份是否已经到了尾声。如果只是备份刚开始建议直接放弃这个变更窗口等备份结束再执行 DDL如果备份已经基本完成可以 KILL 备份连接。KILL 备份连接不会损坏数据但会中断这次备份需要重新执行备份这个决策要和团队确认。4.3 如何避免备份和 DDL 撞车生产环境里我更推荐的做法是按时间窗口隔离而不是靠运气备份窗口和 DDL 窗口严格错开建议至少间隔一小时以上。DDL 使用pt-online-schema-change或gh-ost这类工具它们会把 DDL 拆分成小颗粒的增量复制对 MDL 锁的持有时间极短。如果一定要在备份期间做 DDL先查processlist确认没有mysqldump会话或者通过SELECT * FROM information_schema.processlist WHERE COMMAND Binlog Dump等方式做二次确认。5. 表面 Sleep 实际未提交隐藏最深的一种阻塞方式接下来说一个我印象特别深的坑。有一回我排查Waiting for table metadata lockprocesslist里看到持有锁的会话是一个Sleep状态的空闲连接Time已经 8000 多秒。乍一看这不就是个空闲连接吗怎么会持有 MDL问题就出在事务没有提交。这个连接之前执行了一个START TRANSACTION然后做了一次UPDATE orders SET ... WHERE id 999之后代码里没有COMMIT也没有ROLLBACK连接就继续空在那里。虽然它不再执行任何 SQL但在 MySQL 服务端看来这个事务仍然存活它拥有的 MDL 共享锁依然有效。5.1 事务隔离级别对锁释放的影响这里还涉及一个REPEATABLE READ隔离级别下的重要机制在 InnoDB 里一个事务中执行过的SELECT会建立一个read view这个read view会一直维持到事务结束。即便后续没有新的语句read view也不会主动消失。也就是说不只是写事务连只读事务都可能让 MDL 锁的寿命远超你的直觉。为了解决“看起来空闲、实际占锁”的会话最直接的手段还是确认事务后再 KILL。不过KILL 这个连接之前我一般会再确认三件事这个连接对应的应用服务是否使用连接池如果连接池会把断开的连接自动重建那 KILL 之后新连接会重新建立不会影响业务。这个连接上是否有未提交的写入变更有的话要评估回滚代价。是否有其它方式能绕过比如先COMMIT这个事务如果当时处于事务里但现实是 Sleep 状态的连接你没法替它COMMIT所以常规做法还是 KILL。5.2 应用层连接池引发的“死锁”假象连接池本身也可能造成一种很有意思的阻塞现象应用层维护了一批空闲连接这些连接在数据库侧显示为Sleep但某个连接的事务没提交其它连接又不断请求新的数据库会话。于是旧的连接占锁不放新的连接排队等待show processlist看起来就像是数据库“死锁”了。实际上数据库并没有死锁只是 MDL 排队排得太长。解决思路是在应用层做好事务管理——确保try-finally或with语法块中一定提交或回滚事务避免异常路径下事务悬挂。同时在 DBA 层面通过监控工具定期扫描information_schema.innodb_trx发现超长事务及时告警。一个比较实用的监控 SQLSELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS trx_age_minutes, trx_query FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(MINUTE, trx_started, NOW()) 10;超过 10 分钟的事务可以直接告警出来让值班同学人工确认。6. 各种 DDL 工具在 MDL 上的不同处理方式如果你已经在生产环境吃过几次Waiting for table metadata lock的亏接下来就该认真考虑换个工具来执行表结构变更了。不同工具对 MDL 锁的持有策略差异巨大直接影响变更过程中的业务稳定性。6.1 原生 ALTER TABLE 的锁持有逻辑原生ALTER TABLE在大多数情况下需要获取 MDL 独占锁而且在表重建或复制数据过程中会一直占用。MySQL 5.6 以后支持了 Online DDL部分操作如添加字段不需要复制整表数据但 MDL 独占锁依然需要短暂持有。问题在于它需要等待旧锁释放这个等待时间完全不可控所以原生 DDL 在高峰期做变更就是拿稳定性冒险。6.2 pt-online-schema-change 为何能“软化”锁冲突pt-online-schema-change简称 pt-osc采取了一种完全不同的策略它创建一个影子表在影子表上执行结构变更然后用触发器把原表上的增量变更同步到影子表最后通过原子性的RENAME TABLE完成切换。在这个过程中真正的 DDL 是在影子表上执行的原表只需要在最后切换瞬间获取极短时间的 MDL 独占锁。这种方式的优点是 MDL 独占锁时间极短正常几秒内就能完成切换。缺点是对触发器有一定性能开销且要求表必须有主键或唯一键。如果原表存在长期未提交事务pt-osc 最后切换时依然可能撞上 MDL 锁但它比原生 DDL 已经好太多至少绝大多数时间不会卡住业务。我选择的 pt-osc 命令模板供参考pt-online-schema-change \ --alter ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT \ --host127.0.0.1 \ --useradmin \ --passwordxxx \ --port3306 \ --execute \ Dorder_db,torders执行前它会自动检查连接数、表大小、主键信息然后完成整个变更流程。有一次我在凌晨用这个工具加索引全程业务零感知比直接用ALTER TABLE卡一小时舒服多了。6.3 gh-ost 与 pt-osc 的选择差异gh-ost是另一个常用的在线 DDL 工具它的特点是不需要触发器通过解析 Binlog 来同步增量数据对主库负载的控制更精细还支持暂停和限流。如果团队对触发器比较敏感或者担心触发器对性能有影响gh-ost是更好的选择。对比一下两者对比项pt-oscgh-ost同步机制触发器Binlog 解析对主库额外负载触发器开销解析 Binlog 较小是否要求主键必须必须是否支持限流有限支持更好安装依赖Perl 环境Go 二进制个人建议表结构变更频繁、变更量大、且对锁敏感的业务直接在标准化流程里引入这两个工具之一不要再用原生 DDL 硬扛。7. 从根子上减少 MDL 锁等待的几条实用建议前面讲了很多排障方法但如果每次都要等人来 KILL 才能执行 DDL说明你的变更管理流程其实是有问题的。下面这些建议都是我自己踩过坑之后总结出来的每一条都对应过一次真实事故。7.1 在业务代码层做强制事务超时应用层最容易埋雷的是事务不提交。很多时候不是开发人员不想提交而是异常处理写得太随意或者连接池复用导致事务被“继承”了。建议在连接池配置里增加连接最大生命周期比如 30 分钟。事务超时时间比如 Spring 里设置Transactional(timeout 30)单位秒。连接池的空闲回收检测。如果连接的Wait_timeout是 8 小时那么一个空闲事务可能占用 MDL 锁最长 8 小时这对 DDL 是灾难。我会把空闲连接回收时间调整为 60 秒级别同时确保应用代码的Connection在 finally 块中归还。7.2 DDL 前先做一个“锁健康检查”在真实执行任何 DDL 前写一个锁健康检查脚本先确认当前没有任何长事务或活跃SELECT正在访问目标表。我自己常用的检查逻辑是-- 查看目标表是否已有任何锁等待 SELECT * FROM sys.schema_table_lock_waits WHERE OBJECT_SCHEMA 目标库 AND OBJECT_NAME 目标表; -- 查看是否存在长事务 SELECT trx_mysql_thread_id, trx_started, trx_query FROM information_schema.innodb_trx WHERE trx_started NOW() - INTERVAL 30 SECOND;只要发现有事务在跑就推迟 DDL。宁可晚半小时执行也不要冒险卡死生产。7.3 开启 lock_wait_timeout 作为保险丝MySQL 有一个专门针对 MDL 锁等待的超时参数lock_wait_timeout默认值是 31536000 秒一年。没错真的是一年所以你不设置的话DDL 理论上可以等一年。执行 DDL 前可以先设置一个较短超时SET SESSION lock_wait_timeout 30; ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT ;如果 30 秒内拿不到 MDL 独占锁这条 DDL 会直接报错退出而不是无限等待。这样至少不会把变更窗口耗干也方便自动化脚本在超时后及时重试或通知。建议所有手动 DDL 都用这个方式保护一下。7.4 变更管理流程与监控告警双轨并进最后还是要落到流程上。我现在所在的团队规定任何涉及核心表的 DDL 变更必须提前报备运维侧在变更前检查innodb_trx和metadata_locks变更完成后记录耗时。一旦监控发现Waiting for table metadata lock持续超过 10 秒自动告警并通知 DBA 介入。这套流程并不复杂但它能把“凌晨被 Call”变成“变更前就发现问题”。8. 最后的实战心得一次“已亲测”的复盘回到文章开头说的那次经历。最终解决方案其实非常简单找到Sleep的1003连接确认它对应一个早已不用的应用实例KILL掉之后ALTER TABLE立刻完成。事后我把整个过程拆成了下面几件事变更前先看innodb_trx确认无长事务再执行 DDL。执行时使用lock_wait_timeout 30不让自己傻等一年。把所有核心表 DDL 全部迁到 pt-osc遇到长事务不再卡死。应用层修复事务挂起 bug定期扫描空闲事务。如果你现在正被Waiting for table metadata lock折磨我建议你先别急着 KILL因为胡乱 KILL 可能误伤业务。先花两分钟查清阻塞源头确认是只读事务还是写事务再做决定。你会发现大多数情况下问题不是 MySQL 本身而是某个倒霉的连接把一个事务谈了很久很久。最后再分享一个小技巧如果在生产环境看到大量连接卡在同一条 SQL 的Waiting for table metadata lock上可以顺手把这些连接的ID记下来后续即使 KILL 了元凶也要观察这些连接是否恢复。因为某些连接池会因等待时间过长导致客户端超时表现为连接被重置这时候刷新应用日志即可。记住MDL 锁的解决思路是“定位阻塞源头、谨慎清理、流程预防”三步走缺一不可。