上周給線上訂單表加索引一條ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);跑出去之后就一直卡著SHOW PROCESSLIST里狀態(tài)是Waiting for table metadata lock。我一開始覺得奇怪這個表才幾百萬行加個索引按說幾十秒就該完成怎么會卡到業(yè)務(wù)告警最后排查了兩個小時才明白真正要處理的不是 DDL 本身而是一筆來自應(yīng)用連接池的“早就應(yīng)該結(jié)束但沒結(jié)束”的事務(wù)。這類問題平時不容易出現(xiàn)但一出現(xiàn)往往就是連接池被打滿、接口超時一片告警。這篇文章把我這次親測排查的過程和背后的原理一次講清楚也把能直接用的命令和方法整理出來。不管你是 DBA、后端開發(fā)還是剛接觸 MySQL 的運(yùn)維照著這個思路走一遍基本都能在十幾分鐘內(nèi)定位到元兇。1. 元數(shù)據(jù)鎖到底鎖的是什么先搞清楚等待的本質(zhì)1.1 Metadata Lock 到底是什么MySQL 在 5.5 版本引入了 Metadata Lock簡稱 MDL也就是元數(shù)據(jù)鎖。它和 InnoDB 的行鎖、表鎖不太一樣InnoDB 鎖管的是“表中具體的數(shù)據(jù)行”而 MDL 管的是“表結(jié)構(gòu)本身”。你可以把 MDL 理解成圖書館門口的圖書登記員任何人對一本書做操作都得先跟登記員說一聲登記員記錄完才能進(jìn)去有人要改書的分類編號時其他人就必須等在門口等當(dāng)前正在看書的人全部出來。具體來說執(zhí)行 SELECT、INSERT、UPDATE、DELETE 這些 DML 語句時MySQL 會獲取對應(yīng)表的 MDL 共享鎖SHARED多個會話可以同時持有多把共享鎖互不阻塞。而執(zhí)行 ALTER TABLE、DROP TABLE、TRUNCATE TABLE 這類 DDL 語句時需要獲取 MDL 排他鎖EXCLUSIVE排他鎖和任何其他鎖都不能共存。一旦排他鎖申請不到DDL 就會一直處于Waiting for table metadata lock。這個設(shè)計(jì)的本意是防止 DML 在執(zhí)行過程中表結(jié)構(gòu)突然變了導(dǎo)致記錄格式錯亂。但問題也出在這里如果某個會話的一個事務(wù)始終不提交即使它現(xiàn)在已經(jīng)“不干活了”它之前訪問過的那張表的 MDL 共享鎖也會一直攥在手里。這時候任何 DDL 想拿排他鎖都只能干等。1.2 哪些場景最容易觸發(fā)這個坑根據(jù)我自己的經(jīng)歷和幫別人排查過的案例下面幾類情況占了絕大多數(shù)場景常見原因典型現(xiàn)象應(yīng)用代碼開啟事務(wù)后未提交autocommit0 或者BEGIN之后忘記COMMIT連接狀態(tài)是 Sleep但innodb_trx里有舊事務(wù)事務(wù)里執(zhí)行了長查詢或外部調(diào)用事務(wù)內(nèi)發(fā) HTTP 請求、等待第三方響應(yīng)事務(wù)長時間不結(jié)束鎖一直持有只讀事務(wù)也開了事務(wù)Transactional(readOnlytrue)等框架行為一條 SELECT 也能持有 MDL 共享鎖DDL 排隊(duì)后引發(fā)連鎖反應(yīng)DDL 獲取排他鎖排隊(duì)后續(xù) DML 全被擋住Processlist 中多個連接同時顯示 Waiting for table metadata lock這里有個很容易忽略的點(diǎn)SHOW PROCESSLIST里看到的 State 是Waiting for table metadata lock的會話未必是“持鎖的人”很多時候它是“被鎖連累的人”。真正持有鎖的會話可能正老老實(shí)實(shí)顯示成Sleep甚至Command一欄是空的。所以排查一定不能只看第一眼要順著事務(wù)列表往下挖。注意MDL 的持有范圍是整個事務(wù)而不是單條語句。也就是說哪怕某個事務(wù)里只有一條普通查詢只要這個事務(wù)還沒提交相關(guān)表的 MDL 共享鎖就一直不會釋放。這個特性是很多“查不出原因”的根子。2. 親測第一步一套穩(wěn)定的排查流程快速找出是誰在占鎖我很反感遇到問題就到處亂殺連接那樣很容易把無辜請求也一起干掉。下面這套流程是我實(shí)際用了很多次的每一步都有明確的檢查目的。2.1 先看 Processlist區(qū)分“被鎖的人”和“持鎖的人”第一件事就是執(zhí)行SHOW FULL PROCESSLIST;也可以直接從information_schema.processlist查SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist ORDER BY time DESC;重點(diǎn)看三樣?xùn)|西State、Time、Info。如果發(fā)現(xiàn)一個 ALTER 語句的 State 是Waiting for table metadata lock而且已經(jīng)跑了幾十分鐘基本可以確定它是在等鎖。同時你往往還會看到一批 SELECT 或 UPDATE 語句也掛著同樣的 State這通常是“排隊(duì)效應(yīng)”排他鎖請求在 MDL 隊(duì)列里排在最前面它后面的共享鎖請求全部被堵住結(jié)果就是一片假象。這時候不要急著殺 ALTER。先把我下面第二步做完搞清楚誰拿著鎖再動手。2.2 用 information_schema.innodb_trx 挖出隱藏事務(wù)SHOW PROCESSLIST只能看到當(dāng)前正在執(zhí)行的語句看不到那些事務(wù)還活著、但當(dāng)前沒有在執(zhí)行語句的連接。這時候要用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就是對應(yīng)的 MySQL 連接 ID用它可以和processlist.id關(guān)聯(lián)上。trx_started是事務(wù)開始時間trx_duration算出來就能快速知道哪些事務(wù)已經(jīng)跑了很久。我那次排查時就是通過這個表發(fā)現(xiàn)某個連接 ID 的事務(wù)已經(jīng)存在 2 小時 20 分trx_query字段里寫的是一個很久以前的 INSERT 語句但當(dāng)前processlist里這個連接的 Command 已經(jīng)是 Sleep狀態(tài)看著很正常。如果不是專門查事務(wù)表根本不會想到它才是占鎖的元兇。如果看到的都是短暫事務(wù)還可以進(jìn)一步加條件只看超過某個時長的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 做精準(zhǔn)定位MySQL 5.7 開始performance_schema提供了metadata_locks表可以直接看到當(dāng)前所有 MDL 鎖的情況。這是最精確的定位方式不需要靠猜。先確認(rèn)performance_schema開啟SHOW VARIABLES LIKE performance_schema;然后可以這樣查目標(biāo)表上的 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對應(yīng)線程查不到就需要再結(jié)合REQUESTING_THREAD_ID看等待鎖的線程。實(shí)際操作中我一般先看metadata_locks里OBJECT_NAME等于目標(biāo)表的記錄再用threads表把THREAD_ID映射回PROCESSLIST_ID因?yàn)閜rocesslist里直接展示的連接 ID 和線程內(nèi)部 ID 不是同一個東西。實(shí)操心得如果你不想寫這么長的 SQL可以先跑SHOW PROCESSLIST加innodb_trx九成問題已經(jīng)能定位。只有遇到特別復(fù)雜的多連接互相等待場景才需要把performance_schema.metadata_locks拿出來精確定位。我平時兩種方式結(jié)合速度快也都驗(yàn)證過。3. 親測有效的解決方法和適用場景3.1 等待并觀察什么情況下可以等最簡單粗暴的“解決方式”就是等。如果innodb_trx里查到持鎖事務(wù)已經(jīng)接近提交或者應(yīng)用那邊明確知道某個批處理任務(wù)馬上完成等一兩分鐘也未嘗不可。但我個人不建議在業(yè)務(wù)高峰期抱著僥幸心理去等。MDL 一旦堆積影響會像雪崩一樣擴(kuò)散一個 DDL 掛起后面所有訪問這張表的請求全部排隊(duì)連接數(shù)急速上漲最終連接池耗盡整個應(yīng)用不可用。我在一次模擬壓測中見過一個小表上誤加索引兩個小時內(nèi)幾乎把整庫的連接池拖垮。如果要等最好同時盯著兩個指標(biāo)一是innodb_trx里事務(wù)是否消失二是processlist里 Waiting 數(shù)量是否下降。超過 1 分鐘仍然沒有任何變化就別等了。3.2 精準(zhǔn) Kill 持鎖會話我最終用的方案這是最直接有效的手段也是我當(dāng)時結(jié)束問題的方案。定位到innodb_trx里最老的那個事務(wù)對應(yīng)的trx_mysql_thread_id后執(zhí)行KILL connection_id;比如查詢結(jié)果顯示最老事務(wù)的trx_mysql_thread_id是 123456就執(zhí)行KILL 123456;KILL 掉持鎖會話后原來排隊(duì)的 ALTER 會在很短時間內(nèi)獲取到 MDL 排他鎖繼續(xù)執(zhí)行。實(shí)際操作中我 kill 完之后幾乎立刻看到 DDL 的狀態(tài)從Waiting for table metadata lock變成了altering table再過了幾十秒就完成了。但這件事有兩個重要前提必須先確認(rèn)這個會話對應(yīng)的業(yè)務(wù)是否真的可以中斷。KILL 會導(dǎo)致這個事務(wù)回滾如果它正在寫重要數(shù)據(jù)可能帶來部分?jǐn)?shù)據(jù)不一致。不過 MDL 卡住的時候這個事務(wù)往往已經(jīng)是空閑狀態(tài)很久了回滾影響通?？煽?。不要一看到多個等待的 SELECT 就批量 KILL。這些只是排隊(duì)者殺掉它們并不能釋放排他鎖反而會造成應(yīng)用層一堆異常報(bào)錯。真正的動作應(yīng)該指向持鎖者。3.3 快速失敗的應(yīng)急參數(shù)MySQL 里有一個參數(shù)叫l(wèi)ock_wait_timeout它控制語句等待元數(shù)據(jù)鎖的最長時間。默認(rèn)值是 31536000 秒也就是整整一年。雖然生產(chǎn)上不太可能一年都沒人管但一旦發(fā)生 MDL 堆積這個默認(rèn)值等于沒有上限所有請求都會傻等著。你可以動態(tài)調(diào)整SET GLOBAL lock_wait_timeout 5; SET SESSION lock_wait_timeout 5;這樣超過 5 秒拿不到元數(shù)據(jù)鎖的語句會直接報(bào)錯ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction注意這個辦法的本質(zhì)是“快速失敗”不是“解決阻塞”。它可以讓排隊(duì)請求盡快失敗保護(hù)連接池和應(yīng)用線程但 DDL 本身也會失敗必須等持鎖事務(wù)釋放后再重試。我一般只把它當(dāng)應(yīng)急開關(guān)用調(diào)完盡快恢復(fù)默認(rèn)值并且繼續(xù)清理持鎖事務(wù)。MySQL 8.0 還支持在 DDL 語法上加WAIT n或NOWAITALTER TABLE orders ADD INDEX idx_status_created(status, created_at), WAIT 5;如果 5 秒拿不到鎖就立即失敗。這個特性適合把“長期阻塞”變成“快速失敗”避免拖垮業(yè)務(wù)。3.4 不要指望 Online DDL 能完全繞過元數(shù)據(jù)鎖MySQL 5.6 起支持 Online DDL很多人以為有了它ALTER TABLE 就不會堵業(yè)務(wù)了這是誤區(qū)。Online DDL 確實(shí)能減少部分操作的鎖持有時間但它的執(zhí)行過程分為準(zhǔn)備、執(zhí)行、提交幾個階段。準(zhǔn)備和提交階段依然需要獲取 MDL 排他鎖哪怕只是一瞬間。如果有一個未提交事務(wù)一直攥著目標(biāo)表的共享鎖DDL 連準(zhǔn)備階段都進(jìn)不去照樣卡在Waiting for table metadata lock上。也就是說Online DDL 可以解決“執(zhí)行過程中能不能繼續(xù)讀寫”的問題但解決不了“有沒有長事務(wù)占著元數(shù)據(jù)鎖”的問題。不管用ALGORITHMINPLACE還是默認(rèn)的 online 方式只要持鎖事務(wù)不結(jié)束DDL 就永遠(yuǎn)別想開工。這也是為什么我在做任何 DDL 之前都會先查一遍長事務(wù)。4. 一次真實(shí)排查實(shí)錄從現(xiàn)象到恢復(fù)的完整記錄4.1 現(xiàn)象與第一反應(yīng)晚上 20 點(diǎn)左右我接到告警通知某訂單庫活躍連接數(shù)突然從 50 漲到 300應(yīng)用側(cè)大量接口響應(yīng)超時。登錄數(shù)據(jù)庫執(zhí)行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表上狀態(tài)統(tǒng)一是Waiting for table metadata lock。第一反應(yīng)自然認(rèn)為是那條 ALTER 引起的但我也知道不能只殺 ALTER因?yàn)檎嬲龑?dǎo)致它拿不到鎖的人還在暗中。4.2 第一次失誤kill 錯了對象我當(dāng)時第一輪操作其實(shí)犯了個典型錯誤?？吹?ALTER TABLE 卡了這么久直接執(zhí)行了KILL 10001;這個連接是 DDL 持有者嗎不是它是“等待者”。殺完之后連接池里的請求倒是短暫恢復(fù)了一部分但新的問題是那條 ALTER 再跑一次依然卡住而且新的排隊(duì)又出現(xiàn)了。原因是持鎖事務(wù)還活著KILL 一個等鎖的會話既沒有釋放 MDL 共享鎖也沒有解決排他鎖的需求。后來我才把目光轉(zhuǎn)向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;結(jié)果里第一條事務(wù)讓我很意外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顯示事務(wù)從 17:40 就開始了到 20:03 已經(jīng)持續(xù)了 2 小時 23 分鐘。即便它現(xiàn)在沒有執(zhí)行任何語句這個事務(wù)在開啟后一定訪問過orders表所以一直握著orders表的 MDL 共享鎖。4.3 正確的處理步驟和恢復(fù)過程確認(rèn)就是這個連接后我先和業(yè)務(wù)負(fù)責(zé)人確認(rèn)了 ID 10005 對應(yīng)的應(yīng)用會話可以中斷隨后執(zhí)行KILL 10005;執(zhí)行完的瞬間我再看SHOW PROCESSLIST之前那批Waiting for table metadata lock的連接已經(jīng)在陸續(xù)恢復(fù)執(zhí)行原本要加索引的 ALTER 也從等鎖狀態(tài)變成了真正的執(zhí)行中。這里有一個細(xì)節(jié)MDL 等待隊(duì)列是公平的排在最前面的 ALTER 在持鎖事務(wù)釋放后會優(yōu)先拿到鎖并不需要把它 kill 掉再重新執(zhí)行。我這次沒有重新跑 ALTER等了大概幾十秒觀察它的狀態(tài)變?yōu)閍ltering table然后正常完成。親測后的經(jīng)驗(yàn)遇到這種問題正確的恢復(fù)順序是先定位持鎖者再 KILL 持鎖者最后觀察等鎖隊(duì)列自動消化。順序反了只會越搞越亂。5. 事后復(fù)盤與預(yù)防清單讓這種問題不再重復(fù)5.1 應(yīng)用層的長事務(wù)是最大根源這次持鎖 2 小時的會話追到應(yīng)用代碼后發(fā)現(xiàn)是因?yàn)橐粋€批處理任務(wù)里開啟了事務(wù)在事務(wù)里調(diào)用了外部接口外部接口超時時間設(shè)得又很長導(dǎo)致事務(wù)一直掛著。事務(wù)掛著不關(guān)MySQL 層面的鎖就不會釋放。類似的代碼配置我也在很多項(xiàng)目里見過使用Transactional注解但方法里存在遠(yuǎn)程調(diào)用、消息發(fā)送、文件上傳手動BEGIN之后忘記COMMIT連接池的autocommit被設(shè)置成了false且代碼里沒有每次請求都提交事務(wù)監(jiān)控腳本用某些客戶端工具開會話時默認(rèn)開啟了事務(wù)。這些都是隱藏長事務(wù)的溫床。實(shí)際推行落地的做法是事務(wù)內(nèi)不放任何非數(shù)據(jù)庫操作遠(yuǎn)程調(diào)用必須放在事務(wù)提交之后連接池保持autocommittrue框架層面盡量讓只讀查詢不開啟事務(wù)。5.2 變更前的三查檢查現(xiàn)在我在執(zhí)行任何涉及表結(jié)構(gòu)的 DDL 之前都會強(qiáng)制跑一遍三項(xiàng)檢查-- 1. 查當(dāng)前所有連接狀態(tài) SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE time 30; -- 2. 查超過 60 秒的未提交事務(wù) 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. 查目標(biāo)表上的 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;如果有任何一條返回結(jié)果先處理干凈再動手改表。這套檢查已經(jīng)幫我避免了好幾次線上變更事故比事后補(bǔ)救省心得多。5.3 變更工具的選擇和我后續(xù)的運(yùn)維策略大表 DDL 在低峰期執(zhí)行是底線除此之外我開始考慮引入pt-online-schema-change這類工具。它的思路是創(chuàng)建一個影子表在影子表上完成耗時的結(jié)構(gòu)變更再通過觸發(fā)器把增量數(shù)據(jù)同步過去最后用一次短時間鎖表完成切換。很多人以為用了它可以徹底免掉 MDL 問題其實(shí)不是。它在最后切換表名的RENAME TABLE階段還是要獲取 MDL 排他鎖。如果這時候正好有一個長事務(wù)還握著舊表的共享鎖這個切換同樣會卡住。只是它把最耗時的 ALTER 階段從原表剝離了最終持鎖的窗口短到秒級沖突概率大幅降低。所以就算用 pt-osc我仍然會先檢查長事務(wù)?？梢赃@樣理解任何 DDL 最終都要過“讀鎖釋放”這一關(guān)長事務(wù)不結(jié)束神仙工具也得排隊(duì)。除了按需引入工具我還把長事務(wù)監(jiān)控加到了數(shù)據(jù)庫告警里對運(yùn)行時間超過 5 分鐘的事務(wù)實(shí)時告警對超過 30 分鐘的事務(wù)直接通知到 DBA 群。線上運(yùn)行的連接池也會配合設(shè)置合理的事務(wù)超時降低單會話無限掛住的概率。最后再分享一個小習(xí)慣我現(xiàn)在查看類似 MySQL 鎖問題不會只看一條命令就下結(jié)論而是把processlist、innodb_trx、metadata_locks三個信息一起看。少了任何一個都可能被假象帶偏。這次親測的教訓(xùn)說到底就是一句話等鎖的人往往只是表象持鎖的人才是一切的根源。先把源頭找出來再動手就不會白忙。