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