MySQL出現(xiàn)Waiting for table metadata lock的原因以及解決方法(已親測(cè))如果你管過線上MySQL大概率見過這個(gè)讓人血壓升高的狀態(tài)Waiting for table metadata lock。業(yè)務(wù)方在群里問接口怎么突然超時(shí)了你上服務(wù)器執(zhí)行show processlist一排會(huì)話卡在這個(gè)狀態(tài)上后面的查詢?cè)蕉言蕉噙B接數(shù)直接被打滿。我第一次碰到這個(gè)情況時(shí)第一反應(yīng)是表被鎖了于是趕緊去看show open tables where in_use 0結(jié)果什么都沒發(fā)現(xiàn)。折騰了快兩個(gè)小時(shí)才摸到門道。這篇文章把我踩過的坑、排查思路和實(shí)際解決步驟完整寫出來希望你再遇到它時(shí)能在十分鐘內(nèi)定位根因并解決問題。1. 先弄明白metadata lock是什么為什么一個(gè)查詢也能堵住整條業(yè)務(wù)鏈路很多人把Waiting for table metadata lock和InnoDB的ROW LOCK行鎖混在一起這是第一個(gè)誤區(qū)。行鎖鎖的是數(shù)據(jù)行而metadata lock簡稱MDL鎖的是表結(jié)構(gòu)本身是MySQL在5.5版本引入的元數(shù)據(jù)鎖機(jī)制5.6、5.7版本里它的行為已經(jīng)非常嚴(yán)格。MySQL為什么要設(shè)計(jì)MDL核心目的有三個(gè)保證事務(wù)隔離性、保證數(shù)據(jù)一致性、保證DDL操作的安全性。舉個(gè)例子你正在對(duì)一張訂單表執(zhí)行ALTER TABLE加字段如果同時(shí)有一個(gè)事務(wù)正在讀寫這張表兩邊對(duì)表結(jié)構(gòu)的理解就可能不一致——那邊還在用舊結(jié)構(gòu)讀數(shù)據(jù)這邊已經(jīng)換成了新結(jié)構(gòu)數(shù)據(jù)錯(cuò)亂的風(fēng)險(xiǎn)極大。所以MySQL規(guī)定任何對(duì)表的訪問包括SELECT、INSERT、UPDATE、DELETE、ALTER等都必須先獲取對(duì)應(yīng)的MDL拿不到就排隊(duì)等待。MDL有三種主要類型MDL_SHARED_READ共享讀SELECT語句在訪問表時(shí)獲取多個(gè)會(huì)話可以同時(shí)持有互相不阻塞。MDL_SHARED_WRITE共享寫INSERT、UPDATE、DELETE語句獲取允許多個(gè)會(huì)話同時(shí)持有但和MDL_SHARED_READ沖突——也就是說一個(gè)會(huì)話在執(zhí)行寫操作時(shí)另一個(gè)會(huì)話的SELECT也需要排隊(duì)。MDL_EXCLUSIVE獨(dú)占鎖ALTER TABLE、DROP TABLE、RENAME TABLE等DDL語句獲取它與任何其他MDL都互斥必須等待所有共享鎖釋放后才能拿到。理解了這點(diǎn)你就會(huì)明白為什么Waiting for table metadata lock這么難纏它不是InnoDB的行鎖超時(shí)問題innodb_lock_wait_timeout參數(shù)對(duì)它是無效的。它本質(zhì)上是表結(jié)構(gòu)鎖的排隊(duì)問題而排隊(duì)的原因往往是某個(gè)你根本看不見的會(huì)話一直沒釋放MDL。2. 最常見的元兇長事務(wù)與未提交事務(wù)一個(gè)隱藏的SELECT就能卡死DDL我在實(shí)際排查中遇到的Waiting for table metadata lock案例里80%以上的根因都是同一個(gè)有事務(wù)長時(shí)間未提交甚至根本沒有提交。而且最坑的是這個(gè)罪魁禍?zhǔn)淄皇悄阏趫?zhí)行的操作而是一個(gè)看起來人畜無害的SELECT。2.1 一個(gè)普通查詢?yōu)槭裁茨苕i住DDL很多人不理解ALTER TABLE要拿獨(dú)占鎖等一等也正常但SELECT明明早就執(zhí)行完了為什么DDL還在等問題出在事務(wù)邊界。MySQL默認(rèn)是autocommit1單條SQL自動(dòng)提交此時(shí)SELECT獲取的MDL_SHARED_READ在語句結(jié)束后會(huì)立即釋放。但如果你在某個(gè)客戶端里執(zhí)行了BEGIN或START TRANSACTION之后又執(zhí)行了一條SELECT這條SELECT獲取的MDL_SHARED_READ會(huì)一直持有到事務(wù)提交或回滾為止。場(chǎng)景還原一下-- 連接A BEGIN; SELECT * FROM order_info WHERE id 100; -- 然后什么都不做既不COMMIT也不ROLLBACK這個(gè)時(shí)候連接A的事務(wù)一直開著order_info表上的MDL_SHARED_READ鎖一直沒釋放。另一邊運(yùn)維同學(xué)執(zhí)行ALTER TABLE order_info ADD COLUMN remark VARCHAR(255);ALTER TABLE需要MDL_EXCLUSIVE但表上還掛著共享讀鎖只能一直等待。于是這個(gè)DDL的狀態(tài)就是Waiting for table metadata lock。2.2 隱蔽性極強(qiáng)事務(wù)打開但沒有任何活動(dòng)語句有一種更隱蔽的情況事務(wù)里執(zhí)行過SQL但SQL早就執(zhí)行完了事務(wù)還開著。從processlist里看這個(gè)連接的Command是SleepInfo是NULL看起來完全空閑但它的MDL就是沒釋放。我自己就遇到過這么一次報(bào)表系統(tǒng)每天凌晨跑批其中一段邏輯是先查一批數(shù)據(jù)然后循環(huán)逐條更新框架用的是連接池整個(gè)方法標(biāo)了Transactional。結(jié)果某次代碼改動(dòng)后某條數(shù)據(jù)一直查不到方法拋了異常但事務(wù)沒有正確回滾連接池里的這條連接就一直掛著。白天上班后我執(zhí)行DDL加索引死活加不上去排查了半天才發(fā)現(xiàn)是凌晨時(shí)候遺留的僵尸事務(wù)。這種情況下SHOW FULL PROCESSLIST根本看不到它占用了MDL因?yàn)镮nfo為空。必須借助performance_schema才能鎖定真兇。2.3 快速定位從processlist到內(nèi)部進(jìn)程表定位長事務(wù)最快的方法是按以下順序執(zhí)行-- 第一步查看所有執(zhí)行中的SQL SHOW FULL PROCESSLIST; -- 第二步查正在運(yùn)行但沒有提交的事務(wù) SELECT * FROM information_schema.innodb_trx\G;innodb_trx表里會(huì)列出所有當(dāng)前未結(jié)束的事務(wù)看trx_started字段就可以知道事務(wù)開了多久trx_query字段可以知道這個(gè)事務(wù)最后執(zhí)行的SQL是什么。如果這個(gè)表里有一條記錄trx_started已經(jīng)是一兩個(gè)小時(shí)之前了那基本就是它卡住了你的DDL。2.4 為什么一定要先查binlog這里有個(gè)細(xì)節(jié)如果你的實(shí)例開了GTID模式并開啟了binloginnodb_trx還會(huì)配合SHOW MASTER STATUS\G來確認(rèn)事務(wù)對(duì)應(yīng)的Executed_Gtid_Set但這通常只對(duì)排查主從復(fù)制類問題有用。平時(shí)排查MDLinnodb_trx加SHOW PROCESSLIST已經(jīng)足夠定位了。3. 冷門但高頻的誘因SLEEP連接、大查詢與被阻塞的阻塞長事務(wù)是最大的元兇但它不是唯一的。我梳理了這些年碰到的高頻場(chǎng)景下面三個(gè)坑同樣經(jīng)常出現(xiàn)甚至更難排查。3.1 手滑執(zhí)行了SELECT SLEEP()或者前端請(qǐng)求沒斷開有開發(fā)者為了測(cè)試接口性能直接在數(shù)據(jù)庫連接里執(zhí)行SELECT SLEEP(600);在MySQL 5.7版本里SLEEP函數(shù)執(zhí)行期間會(huì)話持有目標(biāo)表上的MDL_SHARED_READ如果它正在SLEEP的那張表恰好是你要做DDL的表你的DDL就只能干等600秒。更常見的情況是應(yīng)用服務(wù)器上的連接池因?yàn)榫W(wǎng)絡(luò)問題沒有斷開連接其實(shí)已經(jīng)死了但MySQL服務(wù)端不知道仍然認(rèn)為會(huì)話存活著它之前查詢過某張表就會(huì)一直占據(jù)該表的MDL。面對(duì)這類情況SHOW FULL PROCESSLIST會(huì)直接顯示SELECT SLEEP(600)在Info字段里一目了然。對(duì)于連接池死連接同樣要借助performance_schema的events_statements_current來定位它最后執(zhí)行了哪條SQL。3.2 無索引大查詢一條慢SQL讓MDL寸步難移這個(gè)坑比上面的更隱蔽。一條全表掃描的SELECT執(zhí)行時(shí)間要10分鐘。在這個(gè)10分鐘里它一直持有表上的MDL_SHARED_READ。運(yùn)維想給這張表加個(gè)索引結(jié)果只能看到Waiting for table metadata lock。這類場(chǎng)景里processlist里能看到一個(gè)大查詢還在Sending data階段你覺得等它查完就好了結(jié)果它在Sending data里卡了15分鐘都沒結(jié)束。更麻煩的是如果同時(shí)還有其他業(yè)務(wù)在寫這張表寫操作會(huì)和這個(gè)長SELECT互相等待最終形成鎖等待鏈。3.3 阻塞的傳遞效應(yīng)新的SELECT也被堵在門外MDL最恐怖的一點(diǎn)是阻塞會(huì)傳染。假設(shè)連接A持有MDL導(dǎo)致連接B的ALTER等待中此時(shí)連接C執(zhí)行一條普通的SELECT它同樣需要獲取MDL。因?yàn)檫B接B的ALTER排在等待隊(duì)列里并且它要的是獨(dú)占鎖MySQL會(huì)把后續(xù)的所有共享讀/寫請(qǐng)求也一起阻塞掉以保證后面來的請(qǐng)求不會(huì)插隊(duì)拿到鎖然后讓ALTER繼續(xù)餓死。這意味著一個(gè)長事務(wù)引發(fā)的MDL問題如果不及時(shí)處理會(huì)在十幾秒內(nèi)把整個(gè)業(yè)務(wù)的所有讀寫請(qǐng)求全部堵住連接數(shù)飆升數(shù)據(jù)庫連接池被打爆應(yīng)用大面積報(bào)錯(cuò)。4. 親測(cè)有效的完整排查鏈路從一團(tuán)亂麻到揪出真兇有一次在某公司的訂單系統(tǒng)上排查故障完整的排查鏈路我記錄了下來可以說是最典型的一次MDL演練。4.1 當(dāng)時(shí)的現(xiàn)場(chǎng)故障反饋是下單接口全部超時(shí)數(shù)據(jù)庫連接數(shù)從平時(shí)的100左右漲到了500連接池已經(jīng)被打滿。執(zhí)行SHOW FULL PROCESSLIST后看到的結(jié)果是幾十個(gè)會(huì)話都是Waiting for table metadata lock只有一兩條是正在執(zhí)行INSERT語句但也卡住了。這個(gè)畫面很有迷惑性因?yàn)閹缀跽麄€(gè)processlist都被Waiting刷屏你根本不知道哪個(gè)才是源頭。4.2 按順序執(zhí)行的排查步驟第一步查information_schema.innodb_trxSELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx\G;結(jié)果顯示有一筆事務(wù)ID為要小很多的事務(wù)trx_started大約是40分鐘前trx_query為NULLtrx_mysql_thread_id指向了一個(gè)Sleep狀態(tài)的連接。這個(gè)會(huì)話40分鐘前執(zhí)行過SQL但事務(wù)一直沒提交。第二步拿到這個(gè)連接ID后直接查看它最后執(zhí)行過的SQL-- 開啟performance_schema的語句事件采集如果沒開的話 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%; -- 查看指定會(huì)話的最近語句 SELECT THREAD_ID, EVENT_NAME, SQL_TEXT, MYSQL_ERRNO FROM performance_schema.events_statements_current WHERE THREAD_ID (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID 123);這條SQL會(huì)返回那個(gè)連接當(dāng)前正在執(zhí)行的語句。如果是Sleep狀態(tài)events_statements_current通常為空你可以查events_statements_history里面會(huì)保留該連接最近執(zhí)行過的幾條語句記錄。第三步確認(rèn)根因。最終查到那個(gè)連接最后執(zhí)行的是一條UPDATE語句從語句內(nèi)容看是框架在更新訂單狀態(tài)但事務(wù)未提交。再配合代碼排查發(fā)現(xiàn)是開發(fā)在事務(wù)方法里捕獲了異常卻沒有主動(dòng)回滾導(dǎo)致事務(wù)一直掛起。第四步確認(rèn)這個(gè)會(huì)話是否可以殺掉。因?yàn)槭菓?yīng)用連接池的連接殺掉后應(yīng)用會(huì)自動(dòng)重新建立新連接不會(huì)影響數(shù)據(jù)一致性。我直接執(zhí)行KILL 123;kill之后SHOW FULL PROCESSLIST里的所有Waiting for table metadata lock瞬間消失ALTER語句馬上執(zhí)行完成連接數(shù)在幾分鐘內(nèi)回到了正常水位。4.3 為什么我沒有直接kill那個(gè)ALTER這里有個(gè)容易踩的坑考誰阻塞了誰的時(shí)候千萬別把ALTER那個(gè)連接殺掉因?yàn)檫@個(gè)阻塞源頭不是它它是受害者。殺掉ALTER雖然能讓當(dāng)前會(huì)話不再Waiting但你后續(xù)還是要做DDL只要根因事務(wù)還在問題會(huì)原樣復(fù)現(xiàn)而且你斷開ALTER后所有排隊(duì)的新請(qǐng)求都會(huì)被放進(jìn)來恢復(fù)瞬間可能出現(xiàn)一波壓力毛刺。順帶說一句實(shí)操時(shí)如果你的MySQL版本支持5.7以上的performance_schema.metadata_locks表可以直接一錘定音SELECT * FROM performance_schema.metadata_locks;這張表會(huì)直接列出所有當(dāng)前持有的MDL和正在等待的MDL包含OBJECT_NAME表名、LOCK_STATUSGRANTED表示已持有、PENDING表示在等待、OWNER_THREAD_ID持有者線程ID。5. 解決辦法與實(shí)戰(zhàn)參數(shù)三種處理策略的適用場(chǎng)景定位到根因后怎么處理是一門學(xué)問。不同場(chǎng)景適合不同的策略我用過下面三種效果都驗(yàn)證過。5.1 臨時(shí)止血精準(zhǔn)KILL持有MDL事務(wù)的連接適用于能準(zhǔn)確找到事務(wù)連接且確認(rèn)殺掉不會(huì)產(chǎn)生數(shù)據(jù)一致性問題。-- 找出未提交事務(wù)對(duì)應(yīng)的連接ID SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_state RUNNING; -- 確認(rèn)連接信息避免誤殺正常業(yè)務(wù)線程 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE ID 上一步查到的trx_mysql_thread_id; -- 殺掉 KILL thread_id;注意KILL之前最好確認(rèn)這個(gè)連接上沒有活躍的update還沒提交。如果事務(wù)正在執(zhí)行大UPDATE或大INSERTKILL之后MySQL會(huì)回滾該事務(wù)但這可能需要一些時(shí)間如果這個(gè)事務(wù)已經(jīng)執(zhí)行了很長時(shí)間回滾也需要不少代價(jià)。提示不要貿(mào)然執(zhí)行KILL一個(gè)正在跑大事務(wù)的會(huì)話尤其是寫入量大的業(yè)務(wù)?；貪L操作會(huì)繼續(xù)進(jìn)行期間依然持有MDL不會(huì)因?yàn)镵ILL就立刻釋放。5.2 設(shè)置合理的鎖等待超時(shí)加一道護(hù)欄MySQL默認(rèn)的lock_wait_timeout是31536000秒也就是一年。這意味著一條ALTER語句一旦進(jìn)入Waiting for table metadata lock狀態(tài)會(huì)一直傻等到天荒地老。合理設(shè)置超時(shí)時(shí)間能避免因一次事故拖垮整個(gè)應(yīng)用。-- 會(huì)話級(jí)超時(shí)10秒 SET SESSION lock_wait_timeout 10; -- 全局級(jí)建議在配置文件里持久化 SET GLOBAL lock_wait_timeout 10;lock_wait_timeout表示獲取元數(shù)據(jù)鎖的等待時(shí)間超時(shí)后MySQL會(huì)返回錯(cuò)誤ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。這樣一來執(zhí)行DDL的會(huì)話不會(huì)無限期堆積在Waiting狀態(tài)DBA也能及時(shí)收到報(bào)錯(cuò)快速介入排查而不是等業(yè)務(wù)徹底不可用了才發(fā)現(xiàn)。5.3 規(guī)避策略避開高峰窗口用在線DDL工具如果業(yè)務(wù)不允許中斷而你又要執(zhí)行大表的DDL可以嘗試采用更溫和的工具來做。這類工具的本質(zhì)是先創(chuàng)建一張新表然后通過觸發(fā)器或解析binlog的方式將增量數(shù)據(jù)同步到新表最后用RENAME TABLE完成切換。由于整個(gè)過程中原表上始終維持著正常的DML操作MDL等待的時(shí)間被壓縮到了極短的窗口期幾乎不會(huì)對(duì)業(yè)務(wù)產(chǎn)生影響。不過說實(shí)話如果你的表只有幾萬行直接用ALTER就行不用考慮這些工具引入它們反而會(huì)讓運(yùn)維復(fù)雜度變高。它們的優(yōu)勢(shì)主要體現(xiàn)在幾百GB甚至上TB的大表上。5.4 預(yù)防性方案監(jiān)控與告警處理完一次之后一定要把預(yù)防手段補(bǔ)上。我現(xiàn)在在監(jiān)控系統(tǒng)里加了兩條告警規(guī)則效果很好規(guī)則1information_schema.innodb_trx中的事務(wù)存活時(shí)間超過30秒就告警規(guī)則2processlist里出現(xiàn)Waiting for table metadata lock狀態(tài)就立即告警。第一條解決長事務(wù)隱患第二條在MDL發(fā)生時(shí)第一時(shí)間通知值班人員。兩條規(guī)則都只需要一條SQL配合定時(shí)任務(wù)就能實(shí)現(xiàn)成本極低但能讓你在下一次事故前就察覺到風(fēng)險(xiǎn)。-- 告警規(guī)則1的查檢SQL每30秒執(zhí)行一次 SELECT count(*) FROM information_schema.innodb_trx WHERE trx_started NOW() - INTERVAL 30 SECOND; -- 告警規(guī)則2的查檢SQL每1分鐘執(zhí)行一次 SELECT count(*) FROM information_schema.processlist WHERE STATE Waiting for table metadata lock;6. 復(fù)盤總結(jié)這類問題為什么難排查以及我的經(jīng)驗(yàn)建議從我的個(gè)人經(jīng)驗(yàn)來看Waiting for table metadata lock之所以坑人有幾個(gè)關(guān)鍵原因第一它和行鎖不一樣innodb相關(guān)的鎖監(jiān)控統(tǒng)統(tǒng)無效你慣用的那些排行鎖的經(jīng)驗(yàn)都用不上。第二持有者往往處于什么都不干的狀態(tài)processlist里看不到任何SQL天然就隱身了。第三阻塞具有擴(kuò)散性你看到的幾十個(gè)Waiting往往都是受害者真兇只有一個(gè)而且藏在最不起眼的地方。排查建議就一條永遠(yuǎn)先檢查長事務(wù)別在那一堆Waiting里挨個(gè)猜。直接查innodb_trx看有沒有老壽星級(jí)別的未提交事務(wù)如果沒有再看metadata_locks表的PENDING狀態(tài)再不行排查有沒有大查詢或者SLEEP時(shí)間長到離譜的連接。我曾見過有同事遇到MDL問題后把數(shù)據(jù)庫實(shí)例重啟了一遍。那確實(shí)能解決因?yàn)橹貑⒑笏惺聞?wù)都斷了MDL自然全部釋放。但代價(jià)是所有連接瞬間中斷、未提交事務(wù)全部回滾、主從可能重新同步對(duì)業(yè)務(wù)的影響比MDL本身還大。重啟是最不推薦的手段除非你已經(jīng)被高可用方案保護(hù)得很好并確認(rèn)可以接受閃斷。最后分享一個(gè)小細(xì)節(jié)如果你在代碼里用連接池并且顯式開了事務(wù)務(wù)必用try-with-resources或者try加finally來保證commit或rollback一定會(huì)執(zhí)行。很多時(shí)候MDL問題的根因不在數(shù)據(jù)庫而在一個(gè)忘了提交事務(wù)的代碼分支上。管好事務(wù)邊界MDL問題至少能少一半。 Waiting for table metadata lock原因與解決
MySQL出現(xiàn)Waiting for table metadata lock的原因以及解決方法(已親測(cè))如果你管過線上MySQL大概率見過這個(gè)讓人血壓升高的狀態(tài)Waiting for table metadata lock。業(yè)務(wù)方在群里問接口怎么突然超時(shí)了你上服務(wù)器執(zhí)行show processlist一排會(huì)話卡在這個(gè)狀態(tài)上后面的查詢?cè)蕉言蕉噙B接數(shù)直接被打滿。我第一次碰到這個(gè)情況時(shí)第一反應(yīng)是表被鎖了于是趕緊去看show open tables where in_use 0結(jié)果什么都沒發(fā)現(xiàn)。折騰了快兩個(gè)小時(shí)才摸到門道。這篇文章把我踩過的坑、排查思路和實(shí)際解決步驟完整寫出來希望你再遇到它時(shí)能在十分鐘內(nèi)定位根因并解決問題。1. 先弄明白metadata lock是什么為什么一個(gè)查詢也能堵住整條業(yè)務(wù)鏈路很多人把Waiting for table metadata lock和InnoDB的ROW LOCK行鎖混在一起這是第一個(gè)誤區(qū)。行鎖鎖的是數(shù)據(jù)行而metadata lock簡稱MDL鎖的是表結(jié)構(gòu)本身是MySQL在5.5版本引入的元數(shù)據(jù)鎖機(jī)制5.6、5.7版本里它的行為已經(jīng)非常嚴(yán)格。MySQL為什么要設(shè)計(jì)MDL核心目的有三個(gè)保證事務(wù)隔離性、保證數(shù)據(jù)一致性、保證DDL操作的安全性。舉個(gè)例子你正在對(duì)一張訂單表執(zhí)行ALTER TABLE加字段如果同時(shí)有一個(gè)事務(wù)正在讀寫這張表兩邊對(duì)表結(jié)構(gòu)的理解就可能不一致——那邊還在用舊結(jié)構(gòu)讀數(shù)據(jù)這邊已經(jīng)換成了新結(jié)構(gòu)數(shù)據(jù)錯(cuò)亂的風(fēng)險(xiǎn)極大。所以MySQL規(guī)定任何對(duì)表的訪問包括SELECT、INSERT、UPDATE、DELETE、ALTER等都必須先獲取對(duì)應(yīng)的MDL拿不到就排隊(duì)等待。MDL有三種主要類型MDL_SHARED_READ共享讀SELECT語句在訪問表時(shí)獲取多個(gè)會(huì)話可以同時(shí)持有互相不阻塞。MDL_SHARED_WRITE共享寫INSERT、UPDATE、DELETE語句獲取允許多個(gè)會(huì)話同時(shí)持有但和MDL_SHARED_READ沖突——也就是說一個(gè)會(huì)話在執(zhí)行寫操作時(shí)另一個(gè)會(huì)話的SELECT也需要排隊(duì)。MDL_EXCLUSIVE獨(dú)占鎖ALTER TABLE、DROP TABLE、RENAME TABLE等DDL語句獲取它與任何其他MDL都互斥必須等待所有共享鎖釋放后才能拿到。理解了這點(diǎn)你就會(huì)明白為什么Waiting for table metadata lock這么難纏它不是InnoDB的行鎖超時(shí)問題innodb_lock_wait_timeout參數(shù)對(duì)它是無效的。它本質(zhì)上是表結(jié)構(gòu)鎖的排隊(duì)問題而排隊(duì)的原因往往是某個(gè)你根本看不見的會(huì)話一直沒釋放MDL。2. 最常見的元兇長事務(wù)與未提交事務(wù)一個(gè)隱藏的SELECT就能卡死DDL我在實(shí)際排查中遇到的Waiting for table metadata lock案例里80%以上的根因都是同一個(gè)有事務(wù)長時(shí)間未提交甚至根本沒有提交。而且最坑的是這個(gè)罪魁禍?zhǔn)淄皇悄阏趫?zhí)行的操作而是一個(gè)看起來人畜無害的SELECT。2.1 一個(gè)普通查詢?yōu)槭裁茨苕i住DDL很多人不理解ALTER TABLE要拿獨(dú)占鎖等一等也正常但SELECT明明早就執(zhí)行完了為什么DDL還在等問題出在事務(wù)邊界。MySQL默認(rèn)是autocommit1單條SQL自動(dòng)提交此時(shí)SELECT獲取的MDL_SHARED_READ在語句結(jié)束后會(huì)立即釋放。但如果你在某個(gè)客戶端里執(zhí)行了BEGIN或START TRANSACTION之后又執(zhí)行了一條SELECT這條SELECT獲取的MDL_SHARED_READ會(huì)一直持有到事務(wù)提交或回滾為止。場(chǎng)景還原一下-- 連接A BEGIN; SELECT * FROM order_info WHERE id 100; -- 然后什么都不做既不COMMIT也不ROLLBACK這個(gè)時(shí)候連接A的事務(wù)一直開著order_info表上的MDL_SHARED_READ鎖一直沒釋放。另一邊運(yùn)維同學(xué)執(zhí)行ALTER TABLE order_info ADD COLUMN remark VARCHAR(255);ALTER TABLE需要MDL_EXCLUSIVE但表上還掛著共享讀鎖只能一直等待。于是這個(gè)DDL的狀態(tài)就是Waiting for table metadata lock。2.2 隱蔽性極強(qiáng)事務(wù)打開但沒有任何活動(dòng)語句有一種更隱蔽的情況事務(wù)里執(zhí)行過SQL但SQL早就執(zhí)行完了事務(wù)還開著。從processlist里看這個(gè)連接的Command是SleepInfo是NULL看起來完全空閑但它的MDL就是沒釋放。我自己就遇到過這么一次報(bào)表系統(tǒng)每天凌晨跑批其中一段邏輯是先查一批數(shù)據(jù)然后循環(huán)逐條更新框架用的是連接池整個(gè)方法標(biāo)了Transactional。結(jié)果某次代碼改動(dòng)后某條數(shù)據(jù)一直查不到方法拋了異常但事務(wù)沒有正確回滾連接池里的這條連接就一直掛著。白天上班后我執(zhí)行DDL加索引死活加不上去排查了半天才發(fā)現(xiàn)是凌晨時(shí)候遺留的僵尸事務(wù)。這種情況下SHOW FULL PROCESSLIST根本看不到它占用了MDL因?yàn)镮nfo為空。必須借助performance_schema才能鎖定真兇。2.3 快速定位從processlist到內(nèi)部進(jìn)程表定位長事務(wù)最快的方法是按以下順序執(zhí)行-- 第一步查看所有執(zhí)行中的SQL SHOW FULL PROCESSLIST; -- 第二步查正在運(yùn)行但沒有提交的事務(wù) SELECT * FROM information_schema.innodb_trx\G;innodb_trx表里會(huì)列出所有當(dāng)前未結(jié)束的事務(wù)看trx_started字段就可以知道事務(wù)開了多久trx_query字段可以知道這個(gè)事務(wù)最后執(zhí)行的SQL是什么。如果這個(gè)表里有一條記錄trx_started已經(jīng)是一兩個(gè)小時(shí)之前了那基本就是它卡住了你的DDL。2.4 為什么一定要先查binlog這里有個(gè)細(xì)節(jié)如果你的實(shí)例開了GTID模式并開啟了binloginnodb_trx還會(huì)配合SHOW MASTER STATUS\G來確認(rèn)事務(wù)對(duì)應(yīng)的Executed_Gtid_Set但這通常只對(duì)排查主從復(fù)制類問題有用。平時(shí)排查MDLinnodb_trx加SHOW PROCESSLIST已經(jīng)足夠定位了。3. 冷門但高頻的誘因SLEEP連接、大查詢與被阻塞的阻塞長事務(wù)是最大的元兇但它不是唯一的。我梳理了這些年碰到的高頻場(chǎng)景下面三個(gè)坑同樣經(jīng)常出現(xiàn)甚至更難排查。3.1 手滑執(zhí)行了SELECT SLEEP()或者前端請(qǐng)求沒斷開有開發(fā)者為了測(cè)試接口性能直接在數(shù)據(jù)庫連接里執(zhí)行SELECT SLEEP(600);在MySQL 5.7版本里SLEEP函數(shù)執(zhí)行期間會(huì)話持有目標(biāo)表上的MDL_SHARED_READ如果它正在SLEEP的那張表恰好是你要做DDL的表你的DDL就只能干等600秒。更常見的情況是應(yīng)用服務(wù)器上的連接池因?yàn)榫W(wǎng)絡(luò)問題沒有斷開連接其實(shí)已經(jīng)死了但MySQL服務(wù)端不知道仍然認(rèn)為會(huì)話存活著它之前查詢過某張表就會(huì)一直占據(jù)該表的MDL。面對(duì)這類情況SHOW FULL PROCESSLIST會(huì)直接顯示SELECT SLEEP(600)在Info字段里一目了然。對(duì)于連接池死連接同樣要借助performance_schema的events_statements_current來定位它最后執(zhí)行了哪條SQL。3.2 無索引大查詢一條慢SQL讓MDL寸步難移這個(gè)坑比上面的更隱蔽。一條全表掃描的SELECT執(zhí)行時(shí)間要10分鐘。在這個(gè)10分鐘里它一直持有表上的MDL_SHARED_READ。運(yùn)維想給這張表加個(gè)索引結(jié)果只能看到Waiting for table metadata lock。這類場(chǎng)景里processlist里能看到一個(gè)大查詢還在Sending data階段你覺得等它查完就好了結(jié)果它在Sending data里卡了15分鐘都沒結(jié)束。更麻煩的是如果同時(shí)還有其他業(yè)務(wù)在寫這張表寫操作會(huì)和這個(gè)長SELECT互相等待最終形成鎖等待鏈。3.3 阻塞的傳遞效應(yīng)新的SELECT也被堵在門外MDL最恐怖的一點(diǎn)是阻塞會(huì)傳染。假設(shè)連接A持有MDL導(dǎo)致連接B的ALTER等待中此時(shí)連接C執(zhí)行一條普通的SELECT它同樣需要獲取MDL。因?yàn)檫B接B的ALTER排在等待隊(duì)列里并且它要的是獨(dú)占鎖MySQL會(huì)把后續(xù)的所有共享讀/寫請(qǐng)求也一起阻塞掉以保證后面來的請(qǐng)求不會(huì)插隊(duì)拿到鎖然后讓ALTER繼續(xù)餓死。這意味著一個(gè)長事務(wù)引發(fā)的MDL問題如果不及時(shí)處理會(huì)在十幾秒內(nèi)把整個(gè)業(yè)務(wù)的所有讀寫請(qǐng)求全部堵住連接數(shù)飆升數(shù)據(jù)庫連接池被打爆應(yīng)用大面積報(bào)錯(cuò)。4. 親測(cè)有效的完整排查鏈路從一團(tuán)亂麻到揪出真兇有一次在某公司的訂單系統(tǒng)上排查故障完整的排查鏈路我記錄了下來可以說是最典型的一次MDL演練。4.1 當(dāng)時(shí)的現(xiàn)場(chǎng)故障反饋是下單接口全部超時(shí)數(shù)據(jù)庫連接數(shù)從平時(shí)的100左右漲到了500連接池已經(jīng)被打滿。執(zhí)行SHOW FULL PROCESSLIST后看到的結(jié)果是幾十個(gè)會(huì)話都是Waiting for table metadata lock只有一兩條是正在執(zhí)行INSERT語句但也卡住了。這個(gè)畫面很有迷惑性因?yàn)閹缀跽麄€(gè)processlist都被Waiting刷屏你根本不知道哪個(gè)才是源頭。4.2 按順序執(zhí)行的排查步驟第一步查information_schema.innodb_trxSELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx\G;結(jié)果顯示有一筆事務(wù)ID為要小很多的事務(wù)trx_started大約是40分鐘前trx_query為NULLtrx_mysql_thread_id指向了一個(gè)Sleep狀態(tài)的連接。這個(gè)會(huì)話40分鐘前執(zhí)行過SQL但事務(wù)一直沒提交。第二步拿到這個(gè)連接ID后直接查看它最后執(zhí)行過的SQL-- 開啟performance_schema的語句事件采集如果沒開的話 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%; -- 查看指定會(huì)話的最近語句 SELECT THREAD_ID, EVENT_NAME, SQL_TEXT, MYSQL_ERRNO FROM performance_schema.events_statements_current WHERE THREAD_ID (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID 123);這條SQL會(huì)返回那個(gè)連接當(dāng)前正在執(zhí)行的語句。如果是Sleep狀態(tài)events_statements_current通常為空你可以查events_statements_history里面會(huì)保留該連接最近執(zhí)行過的幾條語句記錄。第三步確認(rèn)根因。最終查到那個(gè)連接最后執(zhí)行的是一條UPDATE語句從語句內(nèi)容看是框架在更新訂單狀態(tài)但事務(wù)未提交。再配合代碼排查發(fā)現(xiàn)是開發(fā)在事務(wù)方法里捕獲了異常卻沒有主動(dòng)回滾導(dǎo)致事務(wù)一直掛起。第四步確認(rèn)這個(gè)會(huì)話是否可以殺掉。因?yàn)槭菓?yīng)用連接池的連接殺掉后應(yīng)用會(huì)自動(dòng)重新建立新連接不會(huì)影響數(shù)據(jù)一致性。我直接執(zhí)行KILL 123;kill之后SHOW FULL PROCESSLIST里的所有Waiting for table metadata lock瞬間消失ALTER語句馬上執(zhí)行完成連接數(shù)在幾分鐘內(nèi)回到了正常水位。4.3 為什么我沒有直接kill那個(gè)ALTER這里有個(gè)容易踩的坑考誰阻塞了誰的時(shí)候千萬別把ALTER那個(gè)連接殺掉因?yàn)檫@個(gè)阻塞源頭不是它它是受害者。殺掉ALTER雖然能讓當(dāng)前會(huì)話不再Waiting但你后續(xù)還是要做DDL只要根因事務(wù)還在問題會(huì)原樣復(fù)現(xiàn)而且你斷開ALTER后所有排隊(duì)的新請(qǐng)求都會(huì)被放進(jìn)來恢復(fù)瞬間可能出現(xiàn)一波壓力毛刺。順帶說一句實(shí)操時(shí)如果你的MySQL版本支持5.7以上的performance_schema.metadata_locks表可以直接一錘定音SELECT * FROM performance_schema.metadata_locks;這張表會(huì)直接列出所有當(dāng)前持有的MDL和正在等待的MDL包含OBJECT_NAME表名、LOCK_STATUSGRANTED表示已持有、PENDING表示在等待、OWNER_THREAD_ID持有者線程ID。5. 解決辦法與實(shí)戰(zhàn)參數(shù)三種處理策略的適用場(chǎng)景定位到根因后怎么處理是一門學(xué)問。不同場(chǎng)景適合不同的策略我用過下面三種效果都驗(yàn)證過。5.1 臨時(shí)止血精準(zhǔn)KILL持有MDL事務(wù)的連接適用于能準(zhǔn)確找到事務(wù)連接且確認(rèn)殺掉不會(huì)產(chǎn)生數(shù)據(jù)一致性問題。-- 找出未提交事務(wù)對(duì)應(yīng)的連接ID SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_state RUNNING; -- 確認(rèn)連接信息避免誤殺正常業(yè)務(wù)線程 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE ID 上一步查到的trx_mysql_thread_id; -- 殺掉 KILL thread_id;注意KILL之前最好確認(rèn)這個(gè)連接上沒有活躍的update還沒提交。如果事務(wù)正在執(zhí)行大UPDATE或大INSERTKILL之后MySQL會(huì)回滾該事務(wù)但這可能需要一些時(shí)間如果這個(gè)事務(wù)已經(jīng)執(zhí)行了很長時(shí)間回滾也需要不少代價(jià)。提示不要貿(mào)然執(zhí)行KILL一個(gè)正在跑大事務(wù)的會(huì)話尤其是寫入量大的業(yè)務(wù)?；貪L操作會(huì)繼續(xù)進(jìn)行期間依然持有MDL不會(huì)因?yàn)镵ILL就立刻釋放。5.2 設(shè)置合理的鎖等待超時(shí)加一道護(hù)欄MySQL默認(rèn)的lock_wait_timeout是31536000秒也就是一年。這意味著一條ALTER語句一旦進(jìn)入Waiting for table metadata lock狀態(tài)會(huì)一直傻等到天荒地老。合理設(shè)置超時(shí)時(shí)間能避免因一次事故拖垮整個(gè)應(yīng)用。-- 會(huì)話級(jí)超時(shí)10秒 SET SESSION lock_wait_timeout 10; -- 全局級(jí)建議在配置文件里持久化 SET GLOBAL lock_wait_timeout 10;lock_wait_timeout表示獲取元數(shù)據(jù)鎖的等待時(shí)間超時(shí)后MySQL會(huì)返回錯(cuò)誤ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。這樣一來執(zhí)行DDL的會(huì)話不會(huì)無限期堆積在Waiting狀態(tài)DBA也能及時(shí)收到報(bào)錯(cuò)快速介入排查而不是等業(yè)務(wù)徹底不可用了才發(fā)現(xiàn)。5.3 規(guī)避策略避開高峰窗口用在線DDL工具如果業(yè)務(wù)不允許中斷而你又要執(zhí)行大表的DDL可以嘗試采用更溫和的工具來做。這類工具的本質(zhì)是先創(chuàng)建一張新表然后通過觸發(fā)器或解析binlog的方式將增量數(shù)據(jù)同步到新表最后用RENAME TABLE完成切換。由于整個(gè)過程中原表上始終維持著正常的DML操作MDL等待的時(shí)間被壓縮到了極短的窗口期幾乎不會(huì)對(duì)業(yè)務(wù)產(chǎn)生影響。不過說實(shí)話如果你的表只有幾萬行直接用ALTER就行不用考慮這些工具引入它們反而會(huì)讓運(yùn)維復(fù)雜度變高。它們的優(yōu)勢(shì)主要體現(xiàn)在幾百GB甚至上TB的大表上。5.4 預(yù)防性方案監(jiān)控與告警處理完一次之后一定要把預(yù)防手段補(bǔ)上。我現(xiàn)在在監(jiān)控系統(tǒng)里加了兩條告警規(guī)則效果很好規(guī)則1information_schema.innodb_trx中的事務(wù)存活時(shí)間超過30秒就告警規(guī)則2processlist里出現(xiàn)Waiting for table metadata lock狀態(tài)就立即告警。第一條解決長事務(wù)隱患第二條在MDL發(fā)生時(shí)第一時(shí)間通知值班人員。兩條規(guī)則都只需要一條SQL配合定時(shí)任務(wù)就能實(shí)現(xiàn)成本極低但能讓你在下一次事故前就察覺到風(fēng)險(xiǎn)。-- 告警規(guī)則1的查檢SQL每30秒執(zhí)行一次 SELECT count(*) FROM information_schema.innodb_trx WHERE trx_started NOW() - INTERVAL 30 SECOND; -- 告警規(guī)則2的查檢SQL每1分鐘執(zhí)行一次 SELECT count(*) FROM information_schema.processlist WHERE STATE Waiting for table metadata lock;6. 復(fù)盤總結(jié)這類問題為什么難排查以及我的經(jīng)驗(yàn)建議從我的個(gè)人經(jīng)驗(yàn)來看Waiting for table metadata lock之所以坑人有幾個(gè)關(guān)鍵原因第一它和行鎖不一樣innodb相關(guān)的鎖監(jiān)控統(tǒng)統(tǒng)無效你慣用的那些排行鎖的經(jīng)驗(yàn)都用不上。第二持有者往往處于什么都不干的狀態(tài)processlist里看不到任何SQL天然就隱身了。第三阻塞具有擴(kuò)散性你看到的幾十個(gè)Waiting往往都是受害者真兇只有一個(gè)而且藏在最不起眼的地方。排查建議就一條永遠(yuǎn)先檢查長事務(wù)別在那一堆Waiting里挨個(gè)猜。直接查innodb_trx看有沒有老壽星級(jí)別的未提交事務(wù)如果沒有再看metadata_locks表的PENDING狀態(tài)再不行排查有沒有大查詢或者SLEEP時(shí)間長到離譜的連接。我曾見過有同事遇到MDL問題后把數(shù)據(jù)庫實(shí)例重啟了一遍。那確實(shí)能解決因?yàn)橹貑⒑笏惺聞?wù)都斷了MDL自然全部釋放。但代價(jià)是所有連接瞬間中斷、未提交事務(wù)全部回滾、主從可能重新同步對(duì)業(yè)務(wù)的影響比MDL本身還大。重啟是最不推薦的手段除非你已經(jīng)被高可用方案保護(hù)得很好并確認(rèn)可以接受閃斷。最后分享一個(gè)小細(xì)節(jié)如果你在代碼里用連接池并且顯式開了事務(wù)務(wù)必用try-with-resources或者try加finally來保證commit或rollback一定會(huì)執(zhí)行。很多時(shí)候MDL問題的根因不在數(shù)據(jù)庫而在一個(gè)忘了提交事務(wù)的代碼分支上。管好事務(wù)邊界MDL問題至少能少一半。