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