MySQL元数据锁MDL：锁等待复现、排查与治理

1. MDL 到底是什么从一次真实的卡顿说起线上最让人头疼的故障是业务报错日志一片安静、CPU 和 IO 都趴在地板上但所有请求就是不动。这种时候大部分人的第一反应是去翻行锁和死锁日志结果翻了半天什么也没有。我头一回被教育是给一张千万级的订单表补索引执行之后运维群里开始有人喊“下单页面转圈了”。那会儿我盯着SHOW PROCESSLIST看了十分钟几十个线程齐刷刷停在Waiting for table metadata lock这一行上而真正让它们等人的那个线程状态是Sleep。MDL 就是 Metadata Lock元数据锁MySQL 服务层自己维护的一套锁和存储引擎没关系。它保的是表结构以及库、函数、存储过程、触发器这些对象定义在语句执行期间不被别人改掉。你执行一条SELECTInnoDB 那边会给行加锁或者读快照但 MySQL 服务层在这之前会先给这张表加一把 MDL确保这条语句跑着跑着表不会被ALTER掉、列不会被删掉。这套机制在 5.5 之后就全面铺开了5.7 起还能在performance_schema里直接看到每一把锁的持有与等待关系。它解决的是一致性问题如果没有 MDL一条执行了一半的查询完全可能在解析阶段看到的是旧表结构、取数时表已经被DROP或加了列结果轻则报错重则数据错乱。所以 MDL 不是可选项它是 MySQL 强制的。这也意味着任何想在线上做结构变更的人都必须先跟它打交道。这篇内容适合两类人看一类是被Waiting for table metadata lock卡过、想搞清楚背后到底发生了什么的一线开发另一类是负责变更窗口、需要判断“这次 DDL 能不能上”的运维和 DBA。下面我按“机制—复现—排查—治理”的顺序把它拆开讲中间穿插我自己踩过的坑。2. MDL 的加锁规则拆解为什么一条 SELECT 也能把路挡住2.1 锁类型与作用范围的对照MDL 有两个维度类型共享还是排他和范围全局、库、表、函数、存储过程、触发器、事件。日常打交道最多的还是表级。表级的锁类型比想象中多performance_schema.metadata_locks里能看到的枚举值大致如下LOCK_TYPE语义典型持有者SHARED共享读锁可被多个请求同时持有普通SELECT、INSERT、UPDATE、DELETESHARED_HIGH_PRIO高优先级共享锁只查元数据不碰数据SHOW、DESC、部分FLUSH前置检查SHARED_NO_READ_WRITE允许别人读元数据不许读写数据部分在线 DDL 的中间阶段SHARED_UPGRADABLE可升级的共享锁为后续升级成排他做准备在线 DDL 的准备阶段EXCLUSIVE排他锁独占ALTER TABLE、DROP TABLE、RENAME、TRUNCATE真正要命的规则只有一条共享锁之间互相兼容排他锁和谁都互斥。二十个SELECT同时挂在一张表上完全没问题但只要有一个ALTER在排队后面所有的SELECT都进不来。2.2 加锁与释放的时机语句结束不等于锁释放这是最容易搞错的一点。MDL 是在语句的解析和预处理阶段就加上的但释放时机取决于上下文自动提交autocommit1的普通查询语句执行完、事务提交锁就放了所以线上大多数读请求碰不到这个问题。显式事务里BEGIN之后MDL 会一直持有到COMMIT或ROLLBACK中间无论执行了多少条语句、有没有写数据锁都不会提前释放。LOCK TABLES ... READ这类显式表锁锁持续到UNLOCK TABLES。预处理语句比较特殊PREPARE阶段拿到的 MDL 会一直保留到EXECUTE完成或者语句被DEALLOCATE中间如果有什么东西忘了释放锁就赖在那儿了。所以“我只查了一下什么都没改”这句话在 MDL 的世界里是不成立的。BEGIN之后一条SELECT只要不提交那张表的共享锁就一直被攥着谁也别想改结构。注意MDL 不受隔离级别影响。READ COMMITTED和REPEATABLE READ在这一点上表现完全一样别指望改隔离级别能绕过它。2.3 排队规则后来者如何把整条路堵死MDL 的分配不是简单的“兼容就发锁”而是带队列公平性的。当 A 持有共享锁、B 请求排他锁被挡住时B 会进入等待队列此时 C 又来请求共享锁虽然它和 A 完全兼容但因为队列里排在最前面的 B 是排他请求C 不能插队必须排在 B 后面一起等。这个设计是为了防止 DDL 被源源不断的读请求饿死。代价就是一个等待中的排他请求会把它之后的所有请求全部拦住。这就是为什么一条ALTER能把整张表的读写都冻住——它自己没拿到锁但它站的位置足够关键。不同版本在细节上有差异新版内核对饥饿问题做了一些优化但“排他请求排队后会阻塞后续请求”这条核心行为我在 5.7 和 8.0 上实测都成立。理解这个队列模型你就明白排查时的方向永远是不要盯着那些Waiting的线程要去找那个让排他请求等下来的源头。3. 手工复现三条会话把 MDL 等待现场造出来光看理论容易飘我在测试库上把现场完整造了一遍你照着敲也能复现成本极低。3.1 环境准备与前置确认准备一张小表就行shop库下的t_order建表语句里带个普通索引字段即可。开工之前先确认两件事-- 确认 performance_schema 是开的 SHOW VARIABLES LIKE performance_schema; -- 确认 MDL 的 instrument 已启用否则 sys 视图查出来是空的 UPDATE performance_schema.setup_instruments SET ENABLED YES WHERE NAME wait/lock/metadata/sql/mdl;performance_schema在 5.7 和 8.0 上默认是开的但 MDL instrument 在某些精简配置里会被关掉导致sys.schema_table_lock_waits永远返回空结果白白浪费你半天排查时间。这一步花十秒确认值。3.2 三条会话依次执行开三个连接按顺序来-- 会话 A手动开事务只读一条然后不提交 SET SESSION autocommit 0; SELECT * FROM t_order WHERE id 1; -- 到此为止不要 commit不要 rollback-- 会话 B请求排他锁 ALTER TABLE t_order ADD INDEX idx_create_time (create_time);-- 会话 C一条再普通不过的读 SELECT * FROM t_order WHERE id 2;预期结果B 卡住C 也卡住。此时在第四个连接里看SHOW PROCESSLIST;你能看到 B 和 C 的State都是Waiting for table metadata lock而 A 的状态很可能是Sleep——它已经不在执行任何语句了但事务没结束锁还在。这个现象经常让第一次遇到的人困惑A 明明什么都没干为什么是它的问题答案就在锁的持有粒度上MDL 绑的是事务不是语句。3.3 用视图把等待链路抓出来SHOW PROCESSLIST只能告诉你“谁在等”不能告诉你“谁让它等的”。这时候上performance_schemaSELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_STATE, LEFT(t.PROCESSLIST_INFO, 60) AS current_sql, m.OBJECT_SCHEMA, m.OBJECT_NAME, m.LOCK_TYPE, m.LOCK_STATUS, m.LOCK_DURATION FROM performance_schema.metadata_locks m JOIN performance_schema.threads t ON t.THREAD_ID m.OWNER_THREAD_ID WHERE m.OBJECT_SCHEMA shop ORDER BY m.OBJECT_NAME, m.LOCK_STATUS;输出大概是这个形状PROCESSLIST_IDLOCK_TYPELOCK_STATUSLOCK_DURATIONA 的 idSHAREDGRANTEDTRANSACTIONB 的 idEXCLUSIVEPENDINGTRANSACTIONC 的 idSHAREDPENDINGTRANSACTIONLOCK_STATUS GRANTED的是持有者PENDING的是等待者。这里的因果链条一眼就清楚了A 持共享锁不放B 想升级成排他锁但等 AC 又排在 B 后面。再进一步如果手头有 sys 库一条语句更省事SELECT blocking_pid, waiting_pid, wait_age, locked_table, LEFT(waiting_query, 50) AS waiting_sql FROM sys.schema_table_lock_waits;这个视图直接把“谁堵了谁”翻译成人话缺点是依赖的 instrument 配置比较严格前面那步没做好它就是空的。3.4 几个关键字段怎么读metadata_locks表里OWNER_THREAD_ID是内部的线程 ID和SHOW PROCESSLIST里的Id不是一个东西必须 joinperformance_schema.threads才能对上号。LOCK_DURATION有三个值STATEMENT语句结束就放、TRANSACTION事务结束放、EXPLICIT显式控制。日常出事的几乎全是TRANSACTION。还有OWNER_EVENT_ID它指向的是持有者当前正在处理的事件。如果这个线程已经空闲但锁还挂着说明它在等某个外部动作客户端提交、网络回包等基本可以判定是应用侧的事务没收尾。提示排查时先看LOCK_STATUS PENDING的LOCK_TYPE是EXCLUSIVE还是SHARED。如果是EXCLUSIVE说明有 DDL 在排队被挡住的可能是整张表的读写如果连PENDING的SHARED都一堆那说明队伍已经排起来了影响面在扩大得抓紧处理。4. 常见故障场景与排查路径4.1 典型场景速查表现象大概率原因处理方向只有读请求却集体卡住某个连接开了事务没提交或长事务在跑找innodb_trx里长时间 RUNNING 的事务ALTER一直Waiting超时不退出有活跃读写事务持共享锁先定位持有者必要时 kill 掉应用侧报“表不存在/列不存在”后大面积超时DDL 抢到锁了后续请求排队缩短 DDL 持锁时间或改在线变更复制链路延迟飙升备库的 DDL 被本地长查询挡住检查只读实例上是否有长事务备份窗口内 DDL 全部失败备份工具在事务里持有共享锁备份与变更错峰预处理语句相关的偶发卡顿PREPARE后长期未EXECUTE检查连接池的语句缓存策略4.2 三步定位法我自己的习惯是固定三步不跳步第一步看等待的队伍有多长。一条命令够用SELECT COUNT(*) AS waiting_cnt, STATE FROM information_schema.PROCESSLIST WHERE STATE LIKE %metadata lock% GROUP BY STATE;只有一个等待者问题不大慢一点也能过去几十个等待者说明已经影响到业务得准备动手了。第二步找持锁的事务。没有performance_schema权限的时候innodb_trx是穷人版方案SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS elapsed_sec, trx_mysql_thread_id, LEFT(trx_query, 80) AS cur_sql FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;按trx_started排序排最前面、elapsed_sec几百上千的那个基本就是元凶。注意trx_state显示RUNNING但trx_query为空的说明事务开着但没在跑语句——这就是典型的“连接池里忘了提交”。第三步处理。优先级永远是能沟通就让应用侧提交/回滚不能沟通就KILL。KILL的对象是持锁的那个连接 ID不是被堵的等待者。杀掉等待者是无效操作队列前进一格后面那个立刻顶上来继续等问题一点没解决。-- 先确认是不是它 SELECT * FROM information_schema.innodb_trx WHERE trx_mysql_thread_id 12345; -- 再动手 KILL 12345;4.3 几个特别隐蔽的持有者除了“忘提交事务”这个大头还有几类持有者很不显眼空闲连接的残留事务。连接池配了autocommit0业务代码里某条路径漏了commit连接还回池子里被复用。SHOW PROCESSLIST里看到的是Sleep完全不像有问题但innodb_trx里它的trx_started可能是两小时前。这类问题最难查因为它每隔一段时间就换一个连接 ID 出现。引用了目标表的视图、存储过程、触发器。一个视图底层引用了t_order查询这个视图同样会给t_order加共享 MDL。同理触发器里用到的表也会被牵连。改结构之前先把这些依赖关系捋一遍。外键关联表。给带外键的表做 DDL往往需要连带锁定关联表影响面比你预估的大一圈。很多线上事故就是“我只改了一张表为什么另一张也卡了”。复制线程。备库上回放 DDL 时一样要申请排他锁如果备库正好有长查询在读那张表回放线程就等着延迟直接往上冲。这种情况在主库上完全看不出来只会在监控的延迟曲线上体现。注意MDL 没有死锁检测。InnoDB 那套死锁检测只针对行锁MDL 的等待只有超时机制超时时间由lock_wait_timeout控制。这意味着互相等待的 MDL 请求可以僵持到天荒地老不会自动解开。5. 治理与规避把 MDL 风险压到最低5.1 参数兜底lock_wait_timeout这个参数默认值是 31536000 秒也就是一年。意思就是DDL 被挡住之后它会非常耐心地等下去期间把后面所有的请求都堵在门外。线上环境基本必须调小。-- 全局设置建议 5 到 10 秒 SET GLOBAL lock_wait_timeout 8; -- 单次会话覆盖用于需要长时间等待的特殊变更 SET SESSION lock_wait_timeout 60;设成个位数秒的含义是DDL 抢不到锁就快速失败把位置让出来而不是挂在那儿当路障。失败了你再重试总比让整张表停摆强。这里有个高频混淆点必须澄清lock_wait_timeout管的是元数据锁的等待行锁等待看的是innodb_lock_wait_timeout两个参数互不相干。很多人调错了参数调完发现一点用没有。5.2 变更流程里的三道前置检查工具再好也替代不了流程。我现在做任何表结构变更之前固定跑三件事扫长事务。跑一遍innodb_trx把持续时间超过 30 秒的事务列出来先跟业务确认能不能清掉。扫活跃查询。information_schema.PROCESSLIST里TIME超过 10 秒的、且涉及目标表的语句全部过一遍。确认依赖。视图、触发器、存储过程、外键用information_schema.VIEWS、TRIGGERS、ROUTINES和KEY_COLUMN_USAGE查一遍别等到变更时才发现牵连了一大片。变更窗口尽量选在业务低谷这不是形式主义。同样的 DDL在流量低谷时排队短、持锁窗口小出问题的概率差一个量级。5.3 在线变更工具与锁的博弈市面上主流的在线表结构变更工具大致分两派一派用触发器把增量写到影子表一派消费 binlog 做增量同步。它们的共同思路都是“先建新表、慢慢灌数据、最后切换”把排他锁的持有时间从几十分钟压缩到毫秒级。但要说清楚这些工具并没有消除 MDL只是缩短了持锁时间。最后 cut-over 那一刻它还是得拿一把短暂的排他锁。很多事故恰恰出在这里——工具在切换阶段反复重试拿锁每次重试都发一个排他请求这些请求排在队列前面把正常的读写全挡住了。你看着工具日志里“正在重试”线上已经红了。我的做法是给工具的拿锁重试设一个上限比如 3 次失败就自动放弃并告警人工介入。宁可这次变更失败重来也不要让它无限重试把库拖死。另外切换前一定要人为地把源表的读写降下来或者干脆用短暂的应用侧只读窗口成功率会高很多。5.4 值得盯住的几个监控项比起事后排查事前预警便宜得多。我固定监控这几项监控项采集方式阈值建议MDL 等待线程数统计PROCESSLIST里Waiting for table metadata lock的数量大于 5 持续 30 秒告警MDL PENDING 锁数量performance_schema.metadata_locks按LOCK_STATUS分组出现PENDING且持续超过 10 秒长事务数量innodb_trx里elapsed_sec 60大于 0 就提示空闲事务时长Sleep状态且有活跃事务超过 5 分钟告警DDL 执行时长对ALTER类语句单独埋点超过 30 秒告警其中“空闲事务时长”这一项最容易被忽略但它抓的恰恰是最难查的那类问题。实现方式很简单把information_schema.PROCESSLIST的COMMANDSleep和innodb_trx的trx_mysql_thread_id做一次 join命中的就是“连接闲着但事务开着”的家伙。6. 实操心得几个反直觉的细节和我的操作清单6.1 那些踩过才知道的坑慢日志会骗你。MDL 等待的时间是算进语句执行时间里的所以那条被卡住的SELECT会以几十秒甚至几分钟的执行时长出现在慢日志里。第二天你去看慢日志第一反应肯定是“这条 SQL 怎么这么慢”然后去优化索引、改执行计划白忙一场。看到慢日志里某条 SQL 突然集体变慢、而且都是同一张表先怀疑 MDL。杀错对象比不处理更糟。我见过有人图省事把所有Waiting的线程全 kill 了。结果呢持锁的 A 还在锁没释放业务请求重连进来继续排队唯一变化是连接数暴涨了一波。记住KILL的目标永远是 GRANTED 的那个。提交和回滚都要走完。有些客户端在关闭连接时会自动回滚有些不会。走KILL的时候MySQL 会帮你回滚事务但如果事务很大回滚本身又要花时间锁在这期间还挂着。所以大事务的KILL要提前评估别以为按下就立刻见效。8.0 的 INSTANT 算法不是万能的。加列、改默认值这类操作在 8.0 走 INSTANT 确实快到没感觉但它仍然需要短暂持有排他锁。而且不是所有变更都能 INSTANT改字段类型、加索引这些照样得走拷表或者在线重建。别因为版本新就放松警惕。只读实例不是避风港。有些人觉得“我查询都打到只读库主库改结构跟我没关系”。实际上复制线程在备库回放 DDL 时一样会被备库上的长查询挡住然后延迟飙升最后整个只读集群的数据都变旧了。6.2 我自己的操作清单现在遇到Waiting for table metadata lock我基本是条件反射式地走这套流程从看到告警到处理完通常不超过五分钟数等待者数量判断影响面决定是“观察”还是“动手”。查performance_schema.metadata_locks找LOCK_STATUS GRANTED且LOCK_TYPE为SHARED的那个线程。反查它的PROCESSLIST_STATE如果是Sleep基本锁定为应用侧事务未提交。查innodb_trx确认事务开始时间评估是否可杀。联系业务方确认无响应就按预案KILL杀完盯十秒看 PENDING 是否清零。事后追溯把这条连接的来源 IP、应用名、执行的最后一条 SQL 整理出来推动应用侧修复。最后再分享一个小习惯每次做完 DDL我都会顺手查一下metadata_locks里有没有残留的PENDING记录。有的话说明还有东西没释放干净趁早处理别等它在下一次变更时爆发。这个动作花不到十秒帮我躲过了好几次潜在事故。