寫文章本質(zhì)上是在勸人少踩坑。MySQL的InnoDB行鎖看著像是“鎖住一行不就是鎖住那一條記錄嗎”實(shí)際用起來卻有一堆前置條件索引、隔離級別、事務(wù)長度都在背后管著你。我平時在線上排查鎖等待和死鎖最后基本都會回到同一個結(jié)論多數(shù)不是MySQL故意使壞而是表結(jié)構(gòu)、SQL寫法或者事務(wù)設(shè)計(jì)沒對上InnoDB的行鎖規(guī)則。這篇文章就把我這些年踩過的坑、查過的innodb_trx、改過的慢SQL串起來把InnoDB使用行鎖的限制講透。1. 從一條慢SQL說起行鎖在InnoDB中的地位1.1 行鎖到底鎖的是什么很多人以為行鎖鎖的是“數(shù)據(jù)行本身”這個理解在邏輯上沒錯但在InnoDB的實(shí)現(xiàn)層面要修正一下InnoDB的鎖是加在索引記錄上的。也就是說行鎖鎖的是索引條目不是堆表里那一行物理數(shù)據(jù)。更準(zhǔn)確地說如果表上有主鍵那主鍵索引就是聚簇索引數(shù)據(jù)行本身就被存在聚簇索引的葉子節(jié)點(diǎn)里所以加在主鍵索引上的鎖看起來就是在鎖那一行數(shù)據(jù)。但如果SQL走的是二級索引InnoDB會在二級索引記錄上加鎖同時還會回表去聚簇索引上加鎖這兩把鎖是一起生效的。這個區(qū)別直接引出了一個核心限制想讓行鎖真正生效SQL的WHERE條件必須能通過索引定位到記錄。能定位到索引InnoDB才知道要鎖哪些索引記錄定位不到它就不知道邊界在哪里只能擴(kuò)大加鎖范圍。我見過不少開發(fā)在排查死鎖時盯著SHOW ENGINE INNODB STATUS里的行鎖信息看半天最后發(fā)現(xiàn)那條UPDATE的WHERE子句壓根沒走索引InnoDB實(shí)際上把所有掃描過的行都鎖了一遍。因?yàn)镸ySQL的加鎖邏輯是“掃描到哪一行就鎖定哪一行”全表掃描就等于把所有行的鎖都拿了一遍事務(wù)并發(fā)一上來自然互相阻塞。1.2 為什么“行鎖”經(jīng)常被人說成“表鎖”這里有個常見的誤會。有人會在社區(qū)里問“我的表加了索引為什么還是鎖表”實(shí)際上InnoDB在絕大多數(shù)情況下不會主動加表級鎖DML語句用的還是行鎖。但當(dāng)一條SQL因?yàn)闆]走索引而掃描全表時它會對掃描過程中遇到的每一行都加上鎖外部看起來的表現(xiàn)就和表鎖一樣任意其他事務(wù)的操作都會被阻塞。這就是限制的核心雛形行鎖并不是按表開綠燈而是按索引命中率來開綠燈。索引沒命中行鎖的“行”就變成了“行集合”甚至整個表。理解這一點(diǎn)后面再談鎖等待、死鎖、事務(wù)隔離級別就都有了下半句的前提。與此同時MySQL鎖的分類里還有一個很容易被忽略的層InnoDB在行鎖之上還會配合共享鎖、排他鎖、意向鎖這些概念檢修線上的鎖問題時光看行鎖本身是不夠的意向鎖與行鎖的兼容關(guān)系也會影響并發(fā)度。但為了不大跑偏我們先把重點(diǎn)放在行鎖的那些硬性限制上。2. 最容易翻車的限制索引與行鎖的強(qiáng)綁定2.1 沒有索引行鎖為什么會退化成“準(zhǔn)表鎖”先給結(jié)論InnoDB不會因?yàn)槟銢]索引就自動升級成LOCK TABLES級別的表鎖但它的行鎖范圍會膨脹到無法接受。我舉個真實(shí)場景。有一張訂單表結(jié)構(gòu)很簡單CREATE TABLE order_info ( id int NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id int NOT NULL, status tinyint NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;某天開發(fā)同學(xué)執(zhí)行了這條UPDATEUPDATE order_info SET status 2 WHERE order_no SO20240001;order_no上沒建索引。此時MySQL只能走全表掃描InnoDB在掃描過程中會給所有讀到的記錄逐一加排他鎖。如果表里有一萬條記錄這一條UPDATE有可能把上萬條記錄的鎖全部拿住雖然形式上是行鎖但效果等同于鎖表。排查時有幾個特征可以參考EXPLAIN中的type列為ALL說明全表掃描行鎖范圍失控的概率極高。鎖等待期間information_schema.innodb_trx里能看到這條事務(wù)長時間處于LOCK WAIT狀態(tài)。并發(fā)UPDATE其他行也會被阻塞因?yàn)槠渌幸惨呀?jīng)被這條全表掃描的事務(wù)鎖住了。這類問題的修復(fù)方案也很直接給查詢條件建立合理的索引讓優(yōu)化器從ALL變成ref或range行鎖才能回到它本來的意義。這里有個值得注意的細(xì)節(jié)在MySQL 5.6之前的版本里UPDATE全表掃描時加鎖是逐條加的而5.6之后引入了半同步讀優(yōu)化掃描后鎖定的記錄可能沒有以前那么夸張但核心風(fēng)險仍然存在線上真實(shí)表現(xiàn)依然是“一條慢SQL拖死一波事務(wù)”。所以別把優(yōu)化器層面的補(bǔ)償當(dāng)成救命稻草字段上該加的索引還是要加。2.2 聯(lián)合索引與行鎖的“錯位”問題如果說單列索引沒有還容易識別聯(lián)合索引用錯了才是更高頻的翻車點(diǎn)。假設(shè)表上有個聯(lián)合索引idx_user_status(user_id, status)一條SQL這樣寫UPDATE order_info SET remark 已處理 WHERE status 1 AND order_no SO20240001;優(yōu)化器可以用idx_user_status嗎多數(shù)情況下不會因?yàn)閟tatus不在聯(lián)合索引的最左前綴中而order_no也不在這條索引里。所以這條UPDATE大概率會選擇走主鍵索引全掃描或者全表掃描行鎖的范圍又失控了。更隱蔽的情況是SQL同時用了兩個索引條件但優(yōu)化器只選了一個另一個條件被當(dāng)作Using where filter過濾掉那加鎖范圍依然會超出預(yù)期。這類問題的特征比較難從EXPLAIN一眼看出來因?yàn)閗ey列顯示用了索引看起來像是“走了索引”但實(shí)際鎖定的記錄數(shù)量遠(yuǎn)大于實(shí)際需要修改的記錄數(shù)。比如條件組合能命中100行但鎖了1000行等修改完成時對應(yīng)用其他行的UPDATE就被阻塞了。我在實(shí)際排查里一般會讓開發(fā)把執(zhí)行計(jì)劃拉出來重點(diǎn)看兩個字段key真正用到的索引是哪個。rows優(yōu)化器估算的掃描行數(shù)。如果rows遠(yuǎn)大于最終修改的行數(shù)鎖范圍多半也超了。調(diào)整辦法通常有兩個一是調(diào)整聯(lián)合索引的字段順序把高頻過濾字段放到左邊二是把WHERE條件里的字段順序和聯(lián)合索引的最左前綴對齊而不是簡單把字段堆上去。這里面的根本邏輯是行鎖的粒度不是由你要改多少行決定的而是由你要掃多少索引記錄決定的。掃描的索引記錄越多鎖的范圍就越大這是所有行鎖限制里最容易被忽略、影響卻最直接的一條。3. 隔離級別對行鎖的限制比你想的更大3.1 幻讀、間隙鎖與Next-Key Lock搞行鎖繞不開隔離級別尤其是默認(rèn)的REPEATABLE READ。MySQL官方說InnoDB在RR級別下能很大程度上避免幻讀靠的不是只鎖住命中行而是引入了間隙鎖和Next-Key Lock。間隙鎖鎖的是索引記錄之間的“間隙”它鎖的不是某個具體行而是一個范圍比如WHERE id BETWEEN 10 AND 20如果這個區(qū)間里有幾條記錄InnoDB不僅會鎖這幾條記錄還會鎖住10到20這個范圍內(nèi)的空隙防止其他事務(wù)插入新的記錄。Next-Key Lock則是“記錄鎖間隙鎖”的組合鎖的范圍是左開右閉區(qū)間既鎖住了已有記錄又鎖住了這條記錄前面的間隙。用生活化的類比來說記錄鎖像是你占了一個車位間隙鎖則是你連車位前后的通道也占了別人想在你旁邊新劃一個車位都做不到。這種設(shè)計(jì)滿足了RR級別對“同一事務(wù)內(nèi)多次查詢結(jié)果一致”的需求但也帶來一個限制鎖的邊界從“行”擴(kuò)大到了“范圍”。實(shí)際案例很典型。表里的訂單狀態(tài)字段是普通索引idx_status(status)事務(wù)A執(zhí)行SELECT * FROM order_info WHERE status 1 FOR UPDATE;事務(wù)B想往status 1的間隙里插入一條新訂單比如status 1還沒有對應(yīng)的記錄但插入位置處在事務(wù)A鎖定的間隙之內(nèi)那B就必須等。哪怕B插入的是一行“全新”的、A剛才沒讀到的記錄一樣會被阻塞。這就是很多人困惑的地方明明只是鎖行為什么別人插一條新記錄也被鎖住了3.2 RC與RR下行鎖行為的差異從限制的角度看READ COMMITTED級別下InnoDB會把Next-Key Lock退化大部分情況只鎖命中行不再鎖間隙。但請注意不是所有場景都完全不鎖間隙在涉及外鍵約束檢查和duplicate key檢查時依然會有間隙鎖存在。只是從日常DML的典型場景來看RC級別下的鎖競爭確實(shí)比RR小很多。這給了我們一個非常重要的調(diào)優(yōu)思路如果業(yè)務(wù)對事務(wù)隔離級別沒有強(qiáng)一致需求從RR切換到RC往往能直接減少鎖等待。但切換前要考慮兩點(diǎn)binlog的格式是不是ROW。MySQL 5.7.7之后默認(rèn)就是ROW但有些老環(huán)境還沿用STATEMENT這種情況下RC下出現(xiàn)的主從數(shù)據(jù)不一致風(fēng)險會更高。應(yīng)用層是否依賴RR的“可重復(fù)讀”語義比如在一個事務(wù)里先查后寫且多次查詢結(jié)果必須完全一致。我處理過的很多死鎖其實(shí)都發(fā)生在RR級別下一個事務(wù)鎖了范圍A另一個事務(wù)鎖了范圍B然后互相等對方釋放。把隔離級別降到RC之后很多死鎖直接消失。畢竟行鎖的限制不等同于“鎖一定越少越好”但能少鎖一個間隙就少一個阻塞點(diǎn)。這里還要提醒一句如果業(yè)務(wù)確實(shí)需要RR的間隙鎖來防止幻讀那你做的就不是“去掉間隙鎖”而是“讓間隙盡量小”。比如把范圍查詢改成等值查詢把大區(qū)間拆成事務(wù)內(nèi)部多次小查詢這都是在保留隔離性的前提下給鎖“瘦身”。4. 行鎖限制背后的性能陷阱與優(yōu)化思路4.1 鎖等待與死鎖的高發(fā)場景行鎖限制落到真實(shí)業(yè)務(wù)里最常見的就是兩類事故鎖等待超時和死鎖。鎖等待超時通常是事務(wù)A拿著某些行的鎖遲遲不提交事務(wù)B在等這些鎖超過innodb_lock_wait_timeout默認(rèn)50秒之后直接報(bào)錯。這種問題大多跟長事務(wù)有關(guān)。開發(fā)同學(xué)在一個事務(wù)里先UPDATE了一條記錄然后又去做外部接口調(diào)用等接口響應(yīng)20秒后再去UPDATE第二條記錄第二個事務(wù)只能干等。一旦接口超時事務(wù)回滾用戶側(cè)看到的就是“操作失敗請重試”但數(shù)據(jù)庫上的鎖可能還沒釋放干凈。死鎖則更微妙一點(diǎn)。兩個事務(wù)都持有部分鎖同時請求對方持有的鎖InnoDB會檢測到這種循環(huán)等待選擇一個事務(wù)回滾另一個繼續(xù)執(zhí)行。死鎖最常見的觸發(fā)場景是多個事務(wù)以不同的順序更新同一組記錄。比如事務(wù)1先更新id1再更新id2事務(wù)2先更新id2再更新id1并發(fā)跑到臨界點(diǎn)就撞上了。我在實(shí)際項(xiàng)目里還見過另一種高發(fā)場景批量UPDATE的SQL寫得范圍過大兩個事務(wù)同時UPDATE同一片區(qū)域里的大部分記錄雖然最終更新的記錄集合有交集但加鎖過程中需要掃描和鎖定的索引記錄數(shù)量巨大導(dǎo)致鎖沖突概率指數(shù)上升。說白了SQL寫得太粗鎖的范圍就被迫變大死鎖概率就被動拉高。4.2 從鎖的角度反推SQL改寫與索引設(shè)計(jì)優(yōu)化行鎖限制不是去改InnoDB的鎖規(guī)則而是去改我們的SQL和索引讓規(guī)則對我們的懲罰最小。第一優(yōu)先級是給UPDATE和DELETE的WHERE條件建立合適索引。這條我在第二部分講得比較細(xì)但還是忍不住再強(qiáng)調(diào)一次沒有索引的UPDATE不僅慢而且鎖得廣是數(shù)據(jù)庫事故的溫床。建索引時優(yōu)先考慮等值條件字段再考慮排序字段最后才考慮范圍字段盡量讓優(yōu)化器鎖定更小的記錄集合。第二個優(yōu)化方向是縮短事務(wù)持有鎖的時間。不要在一個事務(wù)里做太多事尤其是不要做RPC調(diào)用、消息隊(duì)列推送這類外部操作。事務(wù)只做數(shù)據(jù)庫相關(guān)操作事務(wù)里的SQL盡量提前準(zhǔn)備好提交前不要有長等待。把一個大事務(wù)拆成多個小事務(wù)雖然整體執(zhí)行時間可能沒有變短但每個事務(wù)持鎖時間都壓縮了其他事務(wù)的等待時間就顯著下降。第三個方向是保證多個事務(wù)訪問相同資源時順序保持一致。比如業(yè)務(wù)規(guī)則規(guī)定了更新必選按id從小到大那所有事務(wù)都按這個順序執(zhí)行就不會出現(xiàn)互相持有對方需要的鎖的死鎖局面。這個在代碼里可以做也可以在SQL里強(qiáng)行加ORDER BY id以穩(wěn)定加鎖順序讓InnoDB按順序去鎖定索引記錄。第四點(diǎn)是關(guān)注innodb_lock_wait_timeout和innodb_deadlock_detect的配置。死鎖檢測默認(rèn)開啟它在高并發(fā)下會消耗一部分CPU但絕大多數(shù)業(yè)務(wù)場景下不建議關(guān)閉因?yàn)殛P(guān)掉之后死鎖會被藏起來等鎖超時才報(bào)錯更難排查。鎖等待超時則應(yīng)該根據(jù)業(yè)務(wù)容忍度調(diào)整不要盲目調(diào)大調(diào)大只會讓用戶等得更久系統(tǒng)資源也被無效鎖占著。這些都是MySQL性能調(diào)優(yōu)里最常被翻出來的模塊。其實(shí)性能問題很多都不是計(jì)算上的瓶頸而是鎖層面的排隊(duì)。計(jì)算慢一點(diǎn)可能只影響那一條SQL鎖排隊(duì)影響的是全體并發(fā)事務(wù)。5. 實(shí)戰(zhàn)實(shí)錄一次行鎖超時與死鎖的排查5.1 問題現(xiàn)象與鎖等待的監(jiān)控手段有一回線上訂單服務(wù)突然出現(xiàn)大量“Lock wait timeout exceeded”報(bào)錯同時伴隨著零星的死鎖日志。我先看了主機(jī)監(jiān)控CPU和IO都不高基本排除硬件瓶頸問題鎖定在鎖競爭上。排查鎖等待的第一步是看當(dāng)前有哪些事務(wù)在跑、哪些事務(wù)在等。我進(jìn)了MySQL命令行執(zhí)行SELECT * FROM information_schema.innodb_trx\G重點(diǎn)看三個字段trx_state是RUNNING還是LOCK WAIT。trx_started事務(wù)開始時間判斷是否是長事務(wù)。trx_query事務(wù)當(dāng)前執(zhí)行的SQL。結(jié)果發(fā)現(xiàn)有一條事務(wù)從5分鐘前就開始了一直處于RUNNING狀態(tài)但它的SQL已經(jīng)執(zhí)行完了遲遲沒有提交原因是在代碼里事務(wù)方法返回前多做了一個Redis操作拖了幾秒。還有幾條事務(wù)全部卡在LOCK WAIT上等的那把鎖恰好就是前面那條長事務(wù)持有的行鎖。下一步用鎖等待關(guān)系表查出誰在等誰SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM information_schema.innodb_lock_waits;這張表直接給了鎖等待的鏈條。事務(wù)A持有鎖事務(wù)B、C、D都在排隊(duì)等A。問題已經(jīng)很清楚不是鎖不夠而是事務(wù)A占著鎖不提交。5.2 定位到具體行鎖的排查流程光知道事務(wù)A占鎖還不夠我還得確認(rèn)它鎖了哪些行以及為什么鎖了這么多行。這時拿出SHOW ENGINE INNODB STATUS在LATEST DETECTED DEADLOCK或TRANSACTIONS段落下能看到事務(wù)A持有的鎖記錄。日志里通常會有類似這樣的信息*** (1) TRANSACTION: TRANSACTION 1001, ACTIVE 300 sec MySQL thread id 8, OS thread handle 140123, query id 123 update order_info set status 2 where status 1看到這條UPDATE我基本就鎖定了問題根因status字段是一個普通索引但操作方在SQL里用了一個范圍條件把大量記錄納入鎖范圍。更致命的是這是一個批量更新任務(wù)本來只需要更新100條卻因?yàn)樗饕x擇不當(dāng)掃描了幾千條索引記錄行鎖的范圍被放大到幾十倍并且事務(wù)遲遲不提交后面的正常訂單更新全部被堵住。排查思路落實(shí)到操作上我總結(jié)了一套固定流程先看innodb_trx找出長時間不提交的事務(wù)記下trx_id和trx_started。再看innodb_lock_waits畫出誰持有鎖、誰在等待的依賴鏈。SHOW ENGINE INNODB STATUS里查鎖模式區(qū)分是RECORD LOCKS還是GAP LOCKS判斷是否和隔離級別、間隙鎖有關(guān)。最后回到業(yè)務(wù)SQL本身EXPLAIN看執(zhí)行計(jì)劃檢查索引使用和掃描行數(shù)。那次改動的最終方案有兩步。第一步是給批量更新任務(wù)加了更精準(zhǔn)的篩選條件把WHERE里的范圍條件改成等值或者明顯縮小范圍的組合同時確保該條件能用上聯(lián)合索引的最左前綴。第二步是把大事務(wù)拆成了每批100條的小事務(wù)每批執(zhí)行完立刻COMMIT持鎖時間從分鐘級降到了秒級。上線后鎖等待報(bào)錯直接清零。排查過程中我還發(fā)現(xiàn)一個容易被忽視的細(xì)節(jié)information_schema.innodb_trx里的trx_query字段在事務(wù)處理完SQL但尚未提交時經(jīng)常顯示為NULL因?yàn)楫?dāng)前沒有正在執(zhí)行的語句。很多同事看到trx_query是NULL就以為事務(wù)閑著沒事其實(shí)它只是等提交鎖一根沒少。這時候判斷依據(jù)要轉(zhuǎn)向trx_started和trx_state別被trx_query帶偏。6. 常見問題速查與經(jīng)驗(yàn)沉淀6.1 行鎖相關(guān)的常見問題對照表我把平時群里的高頻問題整理成表方便直接對照排查現(xiàn)象可能原因排查方向UPDATE后其他事務(wù)全部卡住WHERE條件無索引行鎖范圍擴(kuò)大到全表掃描看EXPLAIN加了索引再試鎖等待超時偶發(fā)SQL掃描行數(shù)過大事務(wù)持鎖時間過長看innodb_lock_waits縮短事務(wù)死鎖日志反復(fù)出現(xiàn)多個事務(wù)對同一批記錄更新順序不一致統(tǒng)一更新順序加ORDER BY同一行數(shù)據(jù)并發(fā)更新頻繁熱點(diǎn)行競爭激烈長事務(wù)堆積考慮合并更新或異步化插入被阻塞但已有記錄沒被修改RR級別下間隙鎖生效評估是否可降為RC或縮小范圍事務(wù)已結(jié)束但鎖未釋放事務(wù)未提交連接池復(fù)用舊連接檢查應(yīng)用層提交邏輯這張表不止參考也建議團(tuán)隊(duì)里做數(shù)據(jù)庫開發(fā)的同事人手一份。很多鎖問題是典型的“表結(jié)構(gòu)事務(wù)設(shè)計(jì)”問題不是MySQL一時抽風(fēng)。6.2 我在實(shí)際項(xiàng)目中的幾條鎖經(jīng)驗(yàn)最后分享幾條頂著生產(chǎn)環(huán)境壓力換來的經(jīng)驗(yàn)。第一別把事務(wù)殼子包得太大。我見過一個導(dǎo)入功能把幾千條INSERT放進(jìn)一個事務(wù)跑的時候其他業(yè)務(wù)寫操作全部排隊(duì)后來改成每500條一個批次問題立刻緩解。行鎖限制不討論“你的業(yè)務(wù)是否合規(guī)”只看“你的鎖占了多少時間”。第二UPDATE和DELETE前先看執(zhí)行計(jì)劃。EXPLAIN不花幾秒鐘但它能告訴你這次操作是不是又要鎖全表了。我習(xí)慣在開發(fā)自測階段就強(qiáng)制要求看過執(zhí)行計(jì)劃寧可多跑一次也很少帶病上線。第三批量更新時加鎖順序要統(tǒng)一。如果一條業(yè)務(wù)邏輯要更新多條記錄最好在SQL里加ORDER BY讓InnoDB按固定順序加鎖降低死鎖概率。這個邏輯對高并發(fā)下的訂單、庫存、賬戶類操作尤其重要。第四監(jiān)控是最后的保底。我常用的監(jiān)控項(xiàng)有information_schema.innodb_trx里的活躍事務(wù)數(shù)量、innodb_lock_waits里的等待次數(shù)、SHOW GLOBAL STATUS LIKE Innodb_row_lock_waits的累計(jì)等待次數(shù)。這些指標(biāo)結(jié)合在一起能在業(yè)務(wù)真正卡死之前給出預(yù)警信號。行鎖的精髓不在鎖本身而在“怎么讓鎖的范圍比業(yè)務(wù)需要的小”。索引合理了事務(wù)變短了隔離級別選對了InnoDB行鎖的大多數(shù)限制就不會在業(yè)務(wù)側(cè)炸開。最后再分享一個我看過很多次的場景有人為了省一次查詢把一個后臺任務(wù)里幾千條數(shù)據(jù)的更新放進(jìn)了一個事務(wù)本來業(yè)務(wù)邏輯沒跑完但數(shù)據(jù)庫已經(jīng)被一道鎖擋住。記得第一次處理這種問題時我盯著SHOW ENGINE INNODB STATUS看了很久最后發(fā)現(xiàn)只是因?yàn)橐粭lUPDATE的WHERE條件沒走索引整個表都在等他放鎖。從那之后我對行鎖的敬畏就變成了一個習(xí)慣寫UPDATE先看索引開事務(wù)先想清楚要等多久上線前先查一次innodb_trx。這套習(xí)慣救了我很多次也值得成為你的默認(rèn)動作。