上周给线上订单表加索引一条ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);跑出去之后就一直卡着SHOW PROCESSLIST里状态是Waiting for table metadata lock。我一开始觉得奇怪这个表才几百万行加个索引按说几十秒就该完成怎么会卡到业务告警最后排查了两个小时才明白真正要处理的不是 DDL 本身而是一笔来自应用连接池的“早就应该结束但没结束”的事务。这类问题平时不容易出现但一出现往往就是连接池被打满、接口超时一片告警。这篇文章把我这次亲测排查的过程和背后的原理一次讲清楚也把能直接用的命令和方法整理出来。不管你是 DBA、后端开发还是刚接触 MySQL 的运维照着这个思路走一遍基本都能在十几分钟内定位到元凶。1. 元数据锁到底锁的是什么先搞清楚等待的本质1.1 Metadata Lock 到底是什么MySQL 在 5.5 版本引入了 Metadata Lock简称 MDL也就是元数据锁。它和 InnoDB 的行锁、表锁不太一样InnoDB 锁管的是“表中具体的数据行”而 MDL 管的是“表结构本身”。你可以把 MDL 理解成图书馆门口的图书登记员任何人对一本书做操作都得先跟登记员说一声登记员记录完才能进去有人要改书的分类编号时其他人就必须等在门口等当前正在看书的人全部出来。具体来说执行 SELECT、INSERT、UPDATE、DELETE 这些 DML 语句时MySQL 会获取对应表的 MDL 共享锁SHARED多个会话可以同时持有多把共享锁互不阻塞。而执行 ALTER TABLE、DROP TABLE、TRUNCATE TABLE 这类 DDL 语句时需要获取 MDL 排他锁EXCLUSIVE排他锁和任何其他锁都不能共存。一旦排他锁申请不到DDL 就会一直处于Waiting for table metadata lock。这个设计的本意是防止 DML 在执行过程中表结构突然变了导致记录格式错乱。但问题也出在这里如果某个会话的一个事务始终不提交即使它现在已经“不干活了”它之前访问过的那张表的 MDL 共享锁也会一直攥在手里。这时候任何 DDL 想拿排他锁都只能干等。1.2 哪些场景最容易触发这个坑根据我自己的经历和帮别人排查过的案例下面几类情况占了绝大多数场景常见原因典型现象应用代码开启事务后未提交autocommit0 或者BEGIN之后忘记COMMIT连接状态是 Sleep但innodb_trx里有旧事务事务里执行了长查询或外部调用事务内发 HTTP 请求、等待第三方响应事务长时间不结束锁一直持有只读事务也开了事务Transactional(readOnlytrue)等框架行为一条 SELECT 也能持有 MDL 共享锁DDL 排队后引发连锁反应DDL 获取排他锁排队后续 DML 全被挡住Processlist 中多个连接同时显示 Waiting for table metadata lock这里有个很容易忽略的点SHOW PROCESSLIST里看到的 State 是Waiting for table metadata lock的会话未必是“持锁的人”很多时候它是“被锁连累的人”。真正持有锁的会话可能正老老实实显示成Sleep甚至Command一栏是空的。所以排查一定不能只看第一眼要顺着事务列表往下挖。注意MDL 的持有范围是整个事务而不是单条语句。也就是说哪怕某个事务里只有一条普通查询只要这个事务还没提交相关表的 MDL 共享锁就一直不会释放。这个特性是很多“查不出原因”的根子。2. 亲测第一步一套稳定的排查流程快速找出是谁在占锁我很反感遇到问题就到处乱杀连接那样很容易把无辜请求也一起干掉。下面这套流程是我实际用了很多次的每一步都有明确的检查目的。2.1 先看 Processlist区分“被锁的人”和“持锁的人”第一件事就是执行SHOW FULL PROCESSLIST;也可以直接从information_schema.processlist查SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist ORDER BY time DESC;重点看三样东西State、Time、Info。如果发现一个 ALTER 语句的 State 是Waiting for table metadata lock而且已经跑了几十分钟基本可以确定它是在等锁。同时你往往还会看到一批 SELECT 或 UPDATE 语句也挂着同样的 State这通常是“排队效应”排他锁请求在 MDL 队列里排在最前面它后面的共享锁请求全部被堵住结果就是一片假象。这时候不要急着杀 ALTER。先把我下面第二步做完搞清楚谁拿着锁再动手。2.2 用 information_schema.innodb_trx 挖出隐藏事务SHOW PROCESSLIST只能看到当前正在执行的语句看不到那些事务还活着、但当前没有在执行语句的连接。这时候要用innodb_trxSELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_query, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration FROM information_schema.innodb_trx ORDER BY trx_started ASC;trx_mysql_thread_id就是对应的 MySQL 连接 ID用它可以和processlist.id关联上。trx_started是事务开始时间trx_duration算出来就能快速知道哪些事务已经跑了很久。我那次排查时就是通过这个表发现某个连接 ID 的事务已经存在 2 小时 20 分trx_query字段里写的是一个很久以前的 INSERT 语句但当前processlist里这个连接的 Command 已经是 Sleep状态看着很正常。如果不是专门查事务表根本不会想到它才是占锁的元凶。如果看到的都是短暂事务还可以进一步加条件只看超过某个时长的SELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration, trx_query FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 60 ORDER BY trx_duration DESC;2.3 用 performance_schema.metadata_locks 做精准定位MySQL 5.7 开始performance_schema提供了metadata_locks表可以直接看到当前所有 MDL 锁的情况。这是最精确的定位方式不需要靠猜。先确认performance_schema开启SHOW VARIABLES LIKE performance_schema;然后可以这样查目标表上的 MDL 锁SELECT mls.OBJECT_SCHEMA, mls.OBJECT_NAME, mls.LOCK_TYPE, mls.LOCK_STATUS, th.ID AS PROCESSLIST_ID, th.PROCESSLIST_COMMAND, th.PROCESSLIST_TIME, th.PROCESSLIST_INFO FROM performance_schema.metadata_locks mls JOIN performance_schema.threads th ON mls.OWNER_THREAD_ID th.THREAD_ID WHERE mls.OBJECT_SCHEMA your_db AND mls.OBJECT_NAME orders ORDER BY th.PROCESSLIST_TIME DESC;如果OWNER_THREAD_ID对应线程查不到就需要再结合REQUESTING_THREAD_ID看等待锁的线程。实际操作中我一般先看metadata_locks里OBJECT_NAME等于目标表的记录再用threads表把THREAD_ID映射回PROCESSLIST_ID因为processlist里直接展示的连接 ID 和线程内部 ID 不是同一个东西。实操心得如果你不想写这么长的 SQL可以先跑SHOW PROCESSLIST加innodb_trx九成问题已经能定位。只有遇到特别复杂的多连接互相等待场景才需要把performance_schema.metadata_locks拿出来精确定位。我平时两种方式结合速度快也都验证过。3. 亲测有效的解决方法和适用场景3.1 等待并观察什么情况下可以等最简单粗暴的“解决方式”就是等。如果innodb_trx里查到持锁事务已经接近提交或者应用那边明确知道某个批处理任务马上完成等一两分钟也未尝不可。但我个人不建议在业务高峰期抱着侥幸心理去等。MDL 一旦堆积影响会像雪崩一样扩散一个 DDL 挂起后面所有访问这张表的请求全部排队连接数急速上涨最终连接池耗尽整个应用不可用。我在一次模拟压测中见过一个小表上误加索引两个小时内几乎把整库的连接池拖垮。如果要等最好同时盯着两个指标一是innodb_trx里事务是否消失二是processlist里 Waiting 数量是否下降。超过 1 分钟仍然没有任何变化就别等了。3.2 精准 Kill 持锁会话我最终用的方案这是最直接有效的手段也是我当时结束问题的方案。定位到innodb_trx里最老的那个事务对应的trx_mysql_thread_id后执行KILL connection_id;比如查询结果显示最老事务的trx_mysql_thread_id是 123456就执行KILL 123456;KILL 掉持锁会话后原来排队的 ALTER 会在很短时间内获取到 MDL 排他锁继续执行。实际操作中我 kill 完之后几乎立刻看到 DDL 的状态从Waiting for table metadata lock变成了altering table再过了几十秒就完成了。但这件事有两个重要前提必须先确认这个会话对应的业务是否真的可以中断。KILL 会导致这个事务回滚如果它正在写重要数据可能带来部分数据不一致。不过 MDL 卡住的时候这个事务往往已经是空闲状态很久了回滚影响通常可控。不要一看到多个等待的 SELECT 就批量 KILL。这些只是排队者杀掉它们并不能释放排他锁反而会造成应用层一堆异常报错。真正的动作应该指向持锁者。3.3 快速失败的应急参数MySQL 里有一个参数叫lock_wait_timeout它控制语句等待元数据锁的最长时间。默认值是 31536000 秒也就是整整一年。虽然生产上不太可能一年都没人管但一旦发生 MDL 堆积这个默认值等于没有上限所有请求都会傻等着。你可以动态调整SET GLOBAL lock_wait_timeout 5; SET SESSION lock_wait_timeout 5;这样超过 5 秒拿不到元数据锁的语句会直接报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction注意这个办法的本质是“快速失败”不是“解决阻塞”。它可以让排队请求尽快失败保护连接池和应用线程但 DDL 本身也会失败必须等持锁事务释放后再重试。我一般只把它当应急开关用调完尽快恢复默认值并且继续清理持锁事务。MySQL 8.0 还支持在 DDL 语法上加WAIT n或NOWAITALTER TABLE orders ADD INDEX idx_status_created(status, created_at), WAIT 5;如果 5 秒拿不到锁就立即失败。这个特性适合把“长期阻塞”变成“快速失败”避免拖垮业务。3.4 不要指望 Online DDL 能完全绕过元数据锁MySQL 5.6 起支持 Online DDL很多人以为有了它ALTER TABLE 就不会堵业务了这是误区。Online DDL 确实能减少部分操作的锁持有时间但它的执行过程分为准备、执行、提交几个阶段。准备和提交阶段依然需要获取 MDL 排他锁哪怕只是一瞬间。如果有一个未提交事务一直攥着目标表的共享锁DDL 连准备阶段都进不去照样卡在Waiting for table metadata lock上。也就是说Online DDL 可以解决“执行过程中能不能继续读写”的问题但解决不了“有没有长事务占着元数据锁”的问题。不管用ALGORITHMINPLACE还是默认的 online 方式只要持锁事务不结束DDL 就永远别想开工。这也是为什么我在做任何 DDL 之前都会先查一遍长事务。4. 一次真实排查实录从现象到恢复的完整记录4.1 现象与第一反应晚上 20 点左右我接到告警通知某订单库活跃连接数突然从 50 涨到 300应用侧大量接口响应超时。登录数据库执行SHOW FULL PROCESSLIST看到非常典型的一幕----------------------------------------------------------------------------------------------------------------------- | Id | User | db | State | Time | Info | Command | 备注 | ----------------------------------------------------------------------------------------------------------------------- | 10001 | appuser | testdb | Query | 1234 | NULL | Waiting for table metadata lock | ALTER TABLE orders ADD INDEX ... | | 10002 | appuser | testdb | Query | 1234 | NULL | Waiting for table metadata lock | SELECT * FROM orders WHERE ... | | 10003 | appuser | testdb | Query | 1234 | NULL | Waiting for table metadata lock | UPDATE orders SET ... | | 10005 | appuser | testdb | Sleep | 8310 | NULL | NULL | 看不出异常但嫌疑很大 | -----------------------------------------------------------------------------------------------------------------------大量语句都挂在orders表上状态统一是Waiting for table metadata lock。第一反应自然认为是那条 ALTER 引起的但我也知道不能只杀 ALTER因为真正导致它拿不到锁的人还在暗中。4.2 第一次失误kill 错了对象我当时第一轮操作其实犯了个典型错误。看到 ALTER TABLE 卡了这么久直接执行了KILL 10001;这个连接是 DDL 持有者吗不是它是“等待者”。杀完之后连接池里的请求倒是短暂恢复了一部分但新的问题是那条 ALTER 再跑一次依然卡住而且新的排队又出现了。原因是持锁事务还活着KILL 一个等锁的会话既没有释放 MDL 共享锁也没有解决排他锁的需求。后来我才把目光转向information_schema.innodb_trx一下就看到了真相SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC;结果里第一条事务让我很意外trx_id: 1234567 trx_mysql_thread_id: 10005 trx_started: 2025-01-15 17:40:23 trx_state: RUNNING trx_query: NULL这个 ID 10005 的会话看SHOW PROCESSLIST只是 Sleep怎么看都不像有事。但trx_started显示事务从 17:40 就开始了到 20:03 已经持续了 2 小时 23 分钟。即便它现在没有执行任何语句这个事务在开启后一定访问过orders表所以一直握着orders表的 MDL 共享锁。4.3 正确的处理步骤和恢复过程确认就是这个连接后我先和业务负责人确认了 ID 10005 对应的应用会话可以中断随后执行KILL 10005;执行完的瞬间我再看SHOW PROCESSLIST之前那批Waiting for table metadata lock的连接已经在陆续恢复执行原本要加索引的 ALTER 也从等锁状态变成了真正的执行中。这里有一个细节MDL 等待队列是公平的排在最前面的 ALTER 在持锁事务释放后会优先拿到锁并不需要把它 kill 掉再重新执行。我这次没有重新跑 ALTER等了大概几十秒观察它的状态变为altering table然后正常完成。亲测后的经验遇到这种问题正确的恢复顺序是先定位持锁者再 KILL 持锁者最后观察等锁队列自动消化。顺序反了只会越搞越乱。5. 事后复盘与预防清单让这种问题不再重复5.1 应用层的长事务是最大根源这次持锁 2 小时的会话追到应用代码后发现是因为一个批处理任务里开启了事务在事务里调用了外部接口外部接口超时时间设得又很长导致事务一直挂着。事务挂着不关MySQL 层面的锁就不会释放。类似的代码配置我也在很多项目里见过使用Transactional注解但方法里存在远程调用、消息发送、文件上传手动BEGIN之后忘记COMMIT连接池的autocommit被设置成了false且代码里没有每次请求都提交事务监控脚本用某些客户端工具开会话时默认开启了事务。这些都是隐藏长事务的温床。实际推行落地的做法是事务内不放任何非数据库操作远程调用必须放在事务提交之后连接池保持autocommittrue框架层面尽量让只读查询不开启事务。5.2 变更前的三查检查现在我在执行任何涉及表结构的 DDL 之前都会强制跑一遍三项检查-- 1. 查当前所有连接状态 SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE time 30; -- 2. 查超过 60 秒的未提交事务 SELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration, trx_query FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 60; -- 3. 查目标表上的 MDL 锁情况 SELECT mls.OBJECT_NAME, mls.LOCK_TYPE, mls.LOCK_STATUS, th.ID AS PROCESSLIST_ID, th.PROCESSLIST_COMMAND FROM performance_schema.metadata_locks mls LEFT JOIN performance_schema.threads th ON mls.OWNER_THREAD_ID th.THREAD_ID WHERE mls.OBJECT_SCHEMA your_db AND mls.OBJECT_NAME orders;如果有任何一条返回结果先处理干净再动手改表。这套检查已经帮我避免了好几次线上变更事故比事后补救省心得多。5.3 变更工具的选择和我后续的运维策略大表 DDL 在低峰期执行是底线除此之外我开始考虑引入pt-online-schema-change这类工具。它的思路是创建一个影子表在影子表上完成耗时的结构变更再通过触发器把增量数据同步过去最后用一次短时间锁表完成切换。很多人以为用了它可以彻底免掉 MDL 问题其实不是。它在最后切换表名的RENAME TABLE阶段还是要获取 MDL 排他锁。如果这时候正好有一个长事务还握着旧表的共享锁这个切换同样会卡住。只是它把最耗时的 ALTER 阶段从原表剥离了最终持锁的窗口短到秒级冲突概率大幅降低。所以就算用 pt-osc我仍然会先检查长事务。可以这样理解任何 DDL 最终都要过“读锁释放”这一关长事务不结束神仙工具也得排队。除了按需引入工具我还把长事务监控加到了数据库告警里对运行时间超过 5 分钟的事务实时告警对超过 30 分钟的事务直接通知到 DBA 群。线上运行的连接池也会配合设置合理的事务超时降低单会话无限挂住的概率。最后再分享一个小习惯我现在查看类似 MySQL 锁问题不会只看一条命令就下结论而是把processlist、innodb_trx、metadata_locks三个信息一起看。少了任何一个都可能被假象带偏。这次亲测的教训说到底就是一句话等锁的人往往只是表象持锁的人才是一切的根源。先把源头找出来再动手就不会白忙。