1. 從一次線上事故說(shuō)起為什么我們需要深入了解MySQL鎖那天晚上系統(tǒng)監(jiān)控突然告警核心交易接口的響應(yīng)時(shí)間從平時(shí)的幾十毫秒飆升到了十幾秒TPS斷崖式下跌。登錄服務(wù)器一看CPU和IO都挺正常但數(shù)據(jù)庫(kù)連接池幾乎全滿大量線程狀態(tài)顯示為Waiting for table metadata lock。緊急排查后發(fā)現(xiàn)是一個(gè)開(kāi)發(fā)同學(xué)在業(yè)務(wù)低峰期執(zhí)行了一個(gè)ALTER TABLE操作試圖給一個(gè)千萬(wàn)級(jí)的大表加個(gè)索引。就是這個(gè)看似平常的DDL語(yǔ)句與業(yè)務(wù)高峰期仍在進(jìn)行的查詢和更新操作產(chǎn)生了鎖沖突直接“鎖”住了整張表導(dǎo)致服務(wù)近乎癱瘓。最后我們不得不強(qiáng)制Kill掉那個(gè)DDL進(jìn)程并調(diào)整了后續(xù)所有表結(jié)構(gòu)變更的流程——必須在嚴(yán)格的維護(hù)窗口并且先評(píng)估鎖的影響。這次事故讓我深刻意識(shí)到對(duì)于使用MySQL的開(kāi)發(fā)者、DBA甚至架構(gòu)師而言僅僅會(huì)寫SQL是遠(yuǎn)遠(yuǎn)不夠的?！版i”這個(gè)隱藏在數(shù)據(jù)庫(kù)引擎深處的并發(fā)控制機(jī)制平時(shí)悄無(wú)聲息一旦發(fā)威就足以讓整個(gè)系統(tǒng)“卡死”。很多人對(duì)鎖的理解停留在“表鎖”和“行鎖”這兩個(gè)名詞上或者背過(guò)“MyISAM用表鎖InnoDB用行鎖”這樣的面試題。但在真實(shí)的、高并發(fā)的生產(chǎn)環(huán)境中鎖的機(jī)制遠(yuǎn)比這復(fù)雜和微妙。共享鎖、排他鎖、意向鎖、間隙鎖、臨鍵鎖、元數(shù)據(jù)鎖……這些鎖是如何協(xié)同工作的什么情況下行鎖會(huì)升級(jí)為什么我明明只更新一行數(shù)據(jù)卻感覺(jué)鎖住了半張表死鎖是怎么產(chǎn)生的又該如何避免和排查理解MySQL的鎖機(jī)制不是為了應(yīng)付面試而是為了能設(shè)計(jì)出更健壯的數(shù)據(jù)模型編寫出更高效的SQL語(yǔ)句制定出更安全的運(yùn)維策略。它就像數(shù)據(jù)庫(kù)世界的交通規(guī)則不懂規(guī)則遲早會(huì)出“車禍”。接下來(lái)我將結(jié)合多年的實(shí)戰(zhàn)經(jīng)驗(yàn)和案例分析為你層層剝開(kāi)MySQL并發(fā)控制的神秘面紗不僅告訴你鎖是什么更告訴你它們?yōu)槭裁催@樣工作以及如何在實(shí)踐中駕馭它們。2. 鎖的基石共享鎖與排他鎖的本質(zhì)與交互要理解MySQL中紛繁復(fù)雜的鎖類型我們必須從最基礎(chǔ)、最核心的兩個(gè)概念入手共享鎖Shared Lock, S Lock和排他鎖Exclusive Lock, X Lock。這是所有鎖語(yǔ)義的基石。你可以把數(shù)據(jù)庫(kù)中的一條數(shù)據(jù)想象成一個(gè)會(huì)議室。共享鎖S鎖就像是對(duì)這個(gè)會(huì)議室的“讀取權(quán)限”。多個(gè)參與者可以同時(shí)持有這個(gè)會(huì)議室的S鎖一起進(jìn)去查閱資料讀取數(shù)據(jù)互不干擾。在MySQL中普通的SELECT語(yǔ)句在默認(rèn)的REPEATABLE READ隔離級(jí)別下并不會(huì)加鎖它是通過(guò)MVCC多版本并發(fā)控制來(lái)保證一致性的這點(diǎn)后面會(huì)詳述。但是如果你使用了SELECT ... LOCK IN SHARE MODE或者SELECT ... FOR SHAREMySQL 8.0就會(huì)對(duì)被讀取的行加上S鎖。排他鎖X鎖則像是這個(gè)會(huì)議室的“獨(dú)占使用權(quán)”。一旦一個(gè)參與者拿到了X鎖他就獨(dú)占了這間會(huì)議室其他任何人無(wú)論是想查閱還是想使用都不能再進(jìn)入。在MySQL中UPDATE、DELETE、INSERT語(yǔ)句以及使用了SELECT ... FOR UPDATE的查詢都會(huì)對(duì)被操作的行加上X鎖。它們之間的兼容性規(guī)則是理解并發(fā)沖突的關(guān)鍵S鎖與S鎖是兼容的多個(gè)事務(wù)可以同時(shí)讀取同一行數(shù)據(jù)。S鎖與X鎖是不兼容的一個(gè)事務(wù)正在讀取某行持有S鎖另一個(gè)事務(wù)就不能修改它申請(qǐng)X鎖會(huì)被阻塞反之亦然一個(gè)事務(wù)正在修改某行持有X鎖其他事務(wù)也不能讀取它申請(qǐng)S鎖會(huì)被阻塞。X鎖與X鎖是不兼容的這很好理解兩個(gè)事務(wù)不能同時(shí)修改同一行數(shù)據(jù)。注意這里說(shuō)的“讀取”指的是加鎖讀Locking Read。在InnoDB默認(rèn)的RR隔離級(jí)別下普通的快照讀Snapshot Read是不加鎖的它通過(guò)ReadView訪問(wèn)undo log中的歷史版本因此不會(huì)與寫操作沖突。這是MVCC帶來(lái)的巨大優(yōu)勢(shì)也是高并發(fā)讀的保障。但務(wù)必分清“快照讀”和“當(dāng)前讀加鎖讀”的區(qū)別。一個(gè)核心的實(shí)戰(zhàn)場(chǎng)景SELECT ... FOR UPDATEvsUPDATE很多人會(huì)混淆這兩個(gè)語(yǔ)句的加鎖行為。假設(shè)我們有一個(gè)賬戶余額表accounts現(xiàn)在要實(shí)現(xiàn)一個(gè)“查詢并扣款”的操作。錯(cuò)誤示范邏輯漏洞-- 事務(wù)1 START TRANSACTION; SELECT balance FROM accounts WHERE user_id 1001; -- 假設(shè)查到100元 -- 在應(yīng)用層判斷if (balance 50) { ... } UPDATE accounts SET balance balance - 50 WHERE user_id 1001; -- 扣款 COMMIT;在并發(fā)環(huán)境下事務(wù)1的SELECT快照讀和事務(wù)2的SELECT可能看到相同的余額100元都認(rèn)為可以扣款然后相繼執(zhí)行UPDATE。最終余額可能變成-50元這就是典型的“丟失更新”問(wèn)題。正確做法使用悲觀鎖-- 事務(wù)1 START TRANSACTION; SELECT balance FROM accounts WHERE user_id 1001 FOR UPDATE; -- 對(duì)user_id1001的行加上X鎖 -- 此時(shí)其他事務(wù)對(duì)該行的SELECT ... FOR UPDATE或UPDATE都會(huì)被阻塞 UPDATE accounts SET balance balance - 50 WHERE user_id 1001; COMMIT;SELECT ... FOR UPDATE會(huì)對(duì)查找到的行施加X(jué)鎖從而在查詢的瞬間就“鎖定”了數(shù)據(jù)避免了并發(fā)修改。UPDATE語(yǔ)句本身也會(huì)在更新時(shí)對(duì)行加X(jué)鎖但SELECT ... FOR UPDATE的鎖是在UPDATE執(zhí)行前就獲取的可以保證查詢和更新之間數(shù)據(jù)的一致性。這個(gè)例子揭示了鎖的第一個(gè)核心價(jià)值在并發(fā)環(huán)境中保護(hù)數(shù)據(jù)從“讀取”到“修改”這個(gè)關(guān)鍵窗口期的一致性。3. InnoDB的鎖升級(jí)意向鎖、行鎖與表鎖的協(xié)同當(dāng)我們說(shuō)“InnoDB支持行級(jí)鎖”時(shí)并不意味著它只有行鎖。為了實(shí)現(xiàn)高效的鎖管理InnoDB設(shè)計(jì)了一套多粒度的鎖機(jī)制包括意向鎖Intention Locks。為什么需要意向鎖想象一下如果沒(méi)有意向鎖事務(wù)A鎖定了表中的一行行級(jí)X鎖。此時(shí)事務(wù)B想給整個(gè)表加一個(gè)表級(jí)X鎖比如執(zhí)行ALTER TABLE。事務(wù)B如何判斷自己能否加這個(gè)表鎖呢它必須逐行檢查表中是否有任何一行已經(jīng)被加鎖。對(duì)于一個(gè)有上億行記錄的表這種檢查是災(zāi)難性的、不現(xiàn)實(shí)的。意向鎖就是為了解決這個(gè)問(wèn)題而生的。它是一種表級(jí)鎖但它表達(dá)的是一種“意向”Intention而不是真正的鎖定。意向共享鎖Intention Shared Lock, IS鎖事務(wù)打算給表中的某些行加S鎖。在給一行加S鎖之前必須先獲得該表的IS鎖。意向排他鎖Intention Exclusive Lock, IX鎖事務(wù)打算給表中的某些行加X(jué)鎖。在給一行加X(jué)鎖之前必須先獲得該表的IX鎖。規(guī)則是事務(wù)在獲取行鎖S或X之前必須先獲取對(duì)應(yīng)表級(jí)的意向鎖IS或IX。意向鎖之間是兼容的因?yàn)樗鼈冎皇潜磉_(dá)意向不沖突。但意向鎖與真正的表級(jí)S/X鎖之間有特定的兼容性規(guī)則正是這套規(guī)則讓表級(jí)鎖的快速判斷成為可能。請(qǐng)求鎖類型 vs 已存在鎖類型X表IX表S表IS表X表沖突沖突沖突沖突IX表沖突兼容沖突兼容S表沖突沖突兼容兼容IS表沖突兼容兼容兼容從這個(gè)兼容性矩陣我們可以解讀出幾個(gè)關(guān)鍵點(diǎn)表級(jí)X鎖與任何意向鎖都沖突。這意味著如果一個(gè)事務(wù)持有了表級(jí)X鎖如LOCK TABLES ... WRITE其他事務(wù)連獲取意向鎖即打算操作任何行都不被允許。表級(jí)S鎖與IX鎖沖突。這意味著如果一個(gè)事務(wù)持有了表級(jí)S鎖如LOCK TABLES ... READ其他事務(wù)就不能獲取IX鎖即不能“打算”修改任何行。IX鎖與IX鎖是兼容的。這是實(shí)現(xiàn)高并發(fā)寫的關(guān)鍵兩個(gè)事務(wù)可以同時(shí)持有同一個(gè)表的IX鎖意味著它們可以同時(shí)修改表中不同的行因?yàn)樾屑?jí)X鎖是互斥的但表級(jí)意向鎖不互斥?，F(xiàn)在回到開(kāi)頭的場(chǎng)景事務(wù)B想給表加X(jué)鎖。它只需要檢查兩件事1. 當(dāng)前是否有其他事務(wù)持有該表的X鎖2. 當(dāng)前是否有其他事務(wù)持有該表的IS或IX鎖只要發(fā)現(xiàn)任何一個(gè)事務(wù)B的加鎖請(qǐng)求就會(huì)被阻塞。這個(gè)檢查是瞬間完成的無(wú)需遍歷每一行。行鎖升級(jí)為表鎖的常見(jiàn)誤區(qū)網(wǎng)上常說(shuō)“InnoDB的行鎖會(huì)在某些情況下升級(jí)為表鎖”這其實(shí)是一個(gè)容易誤導(dǎo)的說(shuō)法。更準(zhǔn)確的情況是索引失效導(dǎo)致鎖升級(jí)如果UPDATE或DELETE語(yǔ)句的WHERE條件無(wú)法使用索引InnoDB將無(wú)法通過(guò)索引精確定位到需要鎖定的行。為了保證數(shù)據(jù)一致性它可能會(huì)退而求其次鎖定所有被掃描過(guò)的行甚至鎖定整個(gè)索引或整個(gè)表。這本質(zhì)上不是“升級(jí)”而是因?yàn)闊o(wú)法使用行鎖而被迫使用更粗粒度的鎖。這也是為什么我們必須為高頻查詢和更新的字段建立合適索引的重要原因之一。-- 假設(shè)phone字段沒(méi)有索引 UPDATE users SET status 1 WHERE phone 13800138000; -- 這條語(yǔ)句會(huì)進(jìn)行全表掃描對(duì)掃描到的每一行可能是全表都嘗試加X(jué)鎖效果類似于鎖表。顯式的表鎖語(yǔ)句使用了LOCK TABLES ... READ/WRITE這是應(yīng)用層主動(dòng)要求的表鎖與引擎無(wú)關(guān)。所以在InnoDB中表鎖意向鎖、自增鎖等和行鎖是協(xié)同工作的共同構(gòu)建起一套高效的并發(fā)控制體系。理解意向鎖是理解InnoDB鎖機(jī)制如何平衡精度與效率的關(guān)鍵。4. 超越單行間隙鎖、臨鍵鎖與幻讀的防治在“可重復(fù)讀Repeatable Read, RR”隔離級(jí)別下InnoDB引入了兩種更復(fù)雜的鎖用于解決“幻讀Phantom Read”問(wèn)題間隙鎖Gap Lock和臨鍵鎖Next-Key Lock。什么是幻讀在一個(gè)事務(wù)內(nèi)兩次執(zhí)行相同的查詢第二次查詢看到了第一次查詢時(shí)未出現(xiàn)的“幻影行”。注意幻讀強(qiáng)調(diào)的是“看到了新插入的行”。在“讀已提交Read Committed, RC”級(jí)別下幻讀是可能發(fā)生的。間隙鎖Gap Lock間隙鎖鎖定的不是一條具體的記錄而是索引記錄之間的“間隙”。例如表中存在id為5和10的記錄那么間隙鎖可以鎖定區(qū)間(5, 10)。它的存在是為了防止其他事務(wù)在這個(gè)間隙中插入新的記錄。間隙鎖是共享的。多個(gè)事務(wù)可以在同一個(gè)間隙上持有間隙鎖。它的唯一目的就是阻止插入。間隙鎖只存在于RR隔離級(jí)別或以上在RC級(jí)別下不存在。臨鍵鎖Next-Key Lock臨鍵鎖是行鎖Record Lock 間隙鎖Gap Lock的組合。它既鎖定了記錄本身也鎖定了該記錄之前的間隙。假設(shè)索引中有值10, 11, 13, 20。那么臨鍵鎖可能鎖定的區(qū)間是(-∞, 10], (10, 11], (11, 13], (13, 20], (20, ∞)。它是一個(gè)“左開(kāi)右閉”的區(qū)間。當(dāng)InnoDB掃描索引并準(zhǔn)備加鎖時(shí)默認(rèn)使用的就是臨鍵鎖。這是RR級(jí)別下防止幻讀的主要手段。實(shí)戰(zhàn)案例分析范圍查詢?nèi)绾渭渔i假設(shè)表t有一個(gè)主鍵索引id現(xiàn)有數(shù)據(jù)1, 5, 10, 15。-- 事務(wù)A (RR隔離級(jí)別) START TRANSACTION; SELECT * FROM t WHERE id 10 AND id 15 FOR UPDATE;這條語(yǔ)句會(huì)查詢到id10這一條記錄。那么它到底鎖定了什么找到id10的記錄加上行鎖X鎖。為了阻止幻讀InnoDB還會(huì)加上間隙鎖。對(duì)于id 10它會(huì)鎖定[10, ∞)這個(gè)區(qū)間嗎不完全是。因?yàn)闂l件是id 15所以它找到的下一個(gè)索引鍵值是15。因此InnoDB實(shí)際加的是臨鍵鎖鎖定的范圍是[10, 15)。具體來(lái)說(shuō)對(duì)id10的記錄加行鎖。對(duì)間隙(10, 15)加間隙鎖。 所以鎖定的區(qū)間是(5, 15]臨鍵鎖區(qū)間是左開(kāi)右閉但這里id10是右閉端點(diǎn)整體效果是鎖住了10以及10到15之間的空隙。此時(shí)如果事務(wù)B嘗試執(zhí)行以下操作-- 事務(wù)B INSERT INTO t (id) VALUES (12); -- 被阻塞因?yàn)?2在(10, 15)間隙內(nèi) UPDATE t SET ... WHERE id 15; -- 成功因?yàn)?5不在鎖定的臨鍵鎖范圍內(nèi)右邊界是開(kāi)區(qū)間。但如果是SELECT ... FOR UPDATE id15也可能被阻塞取決于具體實(shí)現(xiàn)和是否存在其他鎖。 INSERT INTO t (id) VALUES (8); -- 成功8在(5,10)間隙未被鎖定。如何觀察和驗(yàn)證鎖信息可以通過(guò)performance_schema庫(kù)中的data_locks和data_lock_waits表MySQL 8.0來(lái)查看當(dāng)前的鎖等待情況。對(duì)于更早的版本可以使用SHOW ENGINE INNODB STATUS命令在輸出的TRANSACTIONS部分查看鎖信息。這對(duì)于排查死鎖和鎖超時(shí)問(wèn)題至關(guān)重要。理解間隙鎖和臨鍵鎖是掌握RR隔離級(jí)別下InnoDB行為的關(guān)鍵。它們雖然增強(qiáng)了數(shù)據(jù)一致性但也帶來(lái)了更復(fù)雜的鎖沖突可能性是許多死鎖場(chǎng)景的“元兇”。在設(shè)計(jì)索引和編寫SQL時(shí)必須考慮這些鎖的影響范圍。5. 鎖的沖突與化解死鎖的成因、排查與規(guī)避策略死鎖是并發(fā)系統(tǒng)中經(jīng)典且棘手的問(wèn)題。在MySQL中當(dāng)兩個(gè)或更多事務(wù)相互等待對(duì)方釋放鎖并且都無(wú)法繼續(xù)推進(jìn)時(shí)就形成了死鎖。InnoDB引擎內(nèi)置了死鎖檢測(cè)機(jī)制一旦發(fā)現(xiàn)死鎖會(huì)立即回滾其中一個(gè)代價(jià)最小的事務(wù)通常以修改的行數(shù)為參考讓其他事務(wù)得以繼續(xù)。一個(gè)典型的死鎖場(chǎng)景假設(shè)有賬戶表accounts有兩個(gè)事務(wù)同時(shí)操作-- 事務(wù)1 START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE id 1; -- 持有id1的X鎖 UPDATE accounts SET balance balance 100 WHERE id 2; -- 嘗試獲取id2的X鎖 -- 事務(wù)2 START TRANSACTION; UPDATE accounts SET balance balance - 50 WHERE id 2; -- 持有id2的X鎖 UPDATE accounts SET balance balance 50 WHERE id 1; -- 嘗試獲取id1的X鎖執(zhí)行時(shí)序如下事務(wù)1鎖定id1。事務(wù)2鎖定id2。事務(wù)1嘗試鎖定id2發(fā)現(xiàn)被事務(wù)2持有于是等待。事務(wù)2嘗試鎖定id1發(fā)現(xiàn)被事務(wù)1持有于是等待。 至此事務(wù)1等待事務(wù)2事務(wù)2等待事務(wù)1形成循環(huán)等待死鎖產(chǎn)生。InnoDB檢測(cè)到后會(huì)回滾其中一個(gè)事務(wù)比如事務(wù)2并向客戶端返回1213 - Deadlock found when trying to get lock; try restarting transaction錯(cuò)誤。事務(wù)1隨后可以成功執(zhí)行。死鎖的排查工具SHOW ENGINE INNODB STATUS這是最常用的工具。在輸出中查找LATEST DETECTED DEADLOCK部分它會(huì)詳細(xì)記錄最后一次死鎖發(fā)生的時(shí)間、涉及的事務(wù)、正在執(zhí)行的SQL語(yǔ)句、以及每個(gè)事務(wù)持有和等待的鎖。這是分析死鎖根因的第一手資料。performance_schema在MySQL 5.7 / 8.0中可以啟用performance_schema的相關(guān)消費(fèi)者consumers通過(guò)查詢events_transactions_current,data_locks,data_lock_waits等表來(lái)實(shí)時(shí)監(jiān)控鎖和事務(wù)狀態(tài)進(jìn)行更深入的分析。規(guī)避死鎖的實(shí)戰(zhàn)策略死鎖無(wú)法完全避免但可以通過(guò)良好的設(shè)計(jì)和編碼習(xí)慣大幅降低其發(fā)生概率和影響保持事務(wù)小巧且快速事務(wù)越大持有鎖的時(shí)間越長(zhǎng)與其他事務(wù)沖突的概率就越高。盡快提交或回滾事務(wù)。約定一致的訪問(wèn)順序這是最重要、最有效的原則。在業(yè)務(wù)邏輯中如果存在多個(gè)需要更新的對(duì)象如表、行所有事務(wù)都按照相同的順序去訪問(wèn)它們。例如總是先更新id小的賬戶再更新id大的賬戶。在上面的例子中如果兩個(gè)事務(wù)都按id1 - id2的順序更新就不會(huì)發(fā)生死鎖。為查詢創(chuàng)建合適的索引避免因?yàn)樗饕?dǎo)致鎖范圍擴(kuò)大從行鎖升級(jí)為類似表鎖的行為增加沖突面。使用EXPLAIN檢查SQL的執(zhí)行計(jì)劃。降低隔離級(jí)別如果業(yè)務(wù)允許將隔離級(jí)別從RR降為RC。RC級(jí)別下沒(méi)有間隙鎖可以消除大量因間隙鎖導(dǎo)致的死鎖。但需評(píng)估幻讀風(fēng)險(xiǎn)。使用樂(lè)觀鎖對(duì)于沖突不那么激烈的場(chǎng)景可以考慮使用樂(lè)觀鎖。在表中增加一個(gè)版本號(hào)version字段。更新時(shí)將version作為條件。-- 樂(lè)觀鎖更新示例 UPDATE products SET stock stock - 1, version version 1 WHERE id 100 AND version 5;如果更新影響行數(shù)為0說(shuō)明版本號(hào)已被其他事務(wù)修改本次更新失敗需要在應(yīng)用層重試或提示用戶。設(shè)置合理的鎖等待超時(shí)通過(guò)innodb_lock_wait_timeout參數(shù)默認(rèn)50秒設(shè)置鎖等待超時(shí)時(shí)間。對(duì)于不重要的后臺(tái)任務(wù)可以設(shè)置一個(gè)較短的超時(shí)時(shí)間避免長(zhǎng)時(shí)間阻塞。但這只是緩解不是解決。重試機(jī)制在應(yīng)用層捕獲死鎖異常如1213錯(cuò)誤并進(jìn)行有限次數(shù)的重試。這對(duì)于由短時(shí)鎖競(jìng)爭(zhēng)引起的偶發(fā)死鎖非常有效。死鎖是系統(tǒng)并發(fā)度達(dá)到一定水平后的自然產(chǎn)物不必談之色變。關(guān)鍵在于建立有效的監(jiān)控、分析和應(yīng)對(duì)機(jī)制將其對(duì)業(yè)務(wù)的影響控制在可接受的范圍內(nèi)。6. 隱形的守護(hù)者與性能殺手元數(shù)據(jù)鎖與自增鎖除了我們熟知的用于保護(hù)數(shù)據(jù)的鎖MySQL還有兩種至關(guān)重要的“后臺(tái)鎖”它們不直接參與業(yè)務(wù)數(shù)據(jù)的并發(fā)控制卻深刻影響著數(shù)據(jù)庫(kù)的可用性和性能元數(shù)據(jù)鎖Metadata Lock, MDL和自增鎖AUTO-INC Lock。元數(shù)據(jù)鎖MDL表結(jié)構(gòu)的守護(hù)神文章開(kāi)頭的事故罪魁禍?zhǔn)拙褪荕DL鎖。MDL鎖是Server層實(shí)現(xiàn)的鎖用于保護(hù)表結(jié)構(gòu)元數(shù)據(jù)的一致性防止在查詢或修改表數(shù)據(jù)的同時(shí)表結(jié)構(gòu)被更改如ALTER TABLE,DROP TABLE。MDL鎖也分為不同級(jí)別MDL讀鎖在執(zhí)行DML操作SELECT,INSERT,UPDATE,DELETE時(shí)自動(dòng)獲取。多個(gè)事務(wù)可以同時(shí)持有同一表的MDL讀鎖。MDL寫鎖在執(zhí)行DDL操作ALTER TABLE,DROP TABLE時(shí)自動(dòng)獲取。MDL寫鎖是排他的。MDL鎖的阻塞與死鎖風(fēng)險(xiǎn)MDL鎖的引入帶來(lái)了一個(gè)經(jīng)典的阻塞鏈問(wèn)題一個(gè)長(zhǎng)查詢或長(zhǎng)事務(wù)SELECT * FROM big_table開(kāi)始執(zhí)行獲取了該表的MDL讀鎖。此時(shí)另一個(gè)線程執(zhí)行ALTER TABLE big_table ADD COLUMN ...它需要獲取MDL寫鎖。由于MDL讀鎖與寫鎖互斥這個(gè)DDL操作被阻塞進(jìn)入等待隊(duì)列。后續(xù)所有新的、針對(duì)big_table的查詢需要MDL讀鎖都會(huì)被這個(gè)等待的MDL寫鎖阻塞因?yàn)镸DL鎖的調(diào)度是“寫鎖優(yōu)先”在寫鎖等待期間新的讀鎖申請(qǐng)也會(huì)被堵在后面。 這就導(dǎo)致了“一榮俱榮一損俱損”的雪崩效應(yīng)一個(gè)慢查詢或未提交的事務(wù)可以阻塞整個(gè)表的所有后續(xù)訪問(wèn)包括查詢。如何應(yīng)對(duì)MDL鎖問(wèn)題監(jiān)控與發(fā)現(xiàn)使用SHOW PROCESSLIST命令查看線程狀態(tài)關(guān)注Waiting for table metadata lock。在performance_schema中可以通過(guò)metadata_locks表查看MDL鎖的詳細(xì)信息。規(guī)范DDL操作在業(yè)務(wù)低峰期執(zhí)行這是鐵律。使用pt-online-schema-change或gh-ost等在線改表工具這些工具通過(guò)創(chuàng)建影子表、同步數(shù)據(jù)、原子切換的方式避免了長(zhǎng)時(shí)間持有MDL寫鎖對(duì)業(yè)務(wù)影響極小。對(duì)于核心表的結(jié)構(gòu)變更應(yīng)強(qiáng)制使用此類工具。設(shè)置超時(shí)在MySQL 5.7可以為ALTER TABLE設(shè)置LOCK和ALGORITHM選項(xiàng)有時(shí)能減少鎖持有時(shí)間。但最根本的還是用在線工具。避免長(zhǎng)事務(wù)及時(shí)提交事務(wù)特別是使用了BEGIN或START TRANSACTION顯式開(kāi)啟的事務(wù)?？焖俣ㄎ徊ill阻塞源通過(guò)information_schema.innodb_trx結(jié)合SHOW PROCESSLIST找到持有MDL讀鎖的長(zhǎng)時(shí)間運(yùn)行的事務(wù)或查詢并評(píng)估是否可以將其Kill掉。自增鎖AUTO-INC Lock序列號(hào)的秩序維持者當(dāng)表中有AUTO_INCREMENT列時(shí)InnoDB使用一種特殊的表級(jí)鎖——自增鎖來(lái)保證為每一行新數(shù)據(jù)生成的自增主鍵值是唯一且連續(xù)的。自增鎖的行為模式由innodb_autoinc_lock_mode參數(shù)控制innodb_autoinc_lock_mode 0(“傳統(tǒng)”模式)每次執(zhí)行INSERT語(yǔ)句時(shí)都會(huì)獲取一個(gè)特殊的表級(jí)AUTO-INC鎖并在語(yǔ)句結(jié)束后釋放。這保證了所有INSERT語(yǔ)句的自增ID是連續(xù)且可預(yù)測(cè)的但并發(fā)插入性能最差。innodb_autoinc_lock_mode 1(“連續(xù)”模式默認(rèn)值)這是大多數(shù)情況下的最佳選擇。對(duì)于“簡(jiǎn)單插入”能預(yù)先確定插入行數(shù)的語(yǔ)句如INSERT INTO t VALUES (1), (2), (3)它使用一個(gè)輕量級(jí)的互斥量來(lái)生成ID不需要持有AUTO-INC鎖到語(yǔ)句結(jié)束大大提升了并發(fā)性。對(duì)于“批量插入”如INSERT ... SELECT,LOAD DATA它仍然會(huì)使用AUTO-INC鎖以保證生成的ID對(duì)于當(dāng)前語(yǔ)句是連續(xù)的。innodb_autoinc_lock_mode 2(“交錯(cuò)”模式)所有INSERT語(yǔ)句都不使用AUTO-INC鎖完全依靠互斥量生成ID。這能提供最高的并發(fā)插入性能但會(huì)帶來(lái)兩個(gè)后果1) 同一語(yǔ)句內(nèi)生成的自增ID可能不連續(xù)2) 在基于語(yǔ)句的復(fù)制SBR模式下可能導(dǎo)致主從不一致。因此只有在使用行復(fù)制RBR或混合復(fù)制MBR時(shí)才考慮使用此模式。實(shí)戰(zhàn)建議除非有非常特殊的、要求絕對(duì)連續(xù)ID且并發(fā)插入不高的場(chǎng)景否則保持默認(rèn)的innodb_autoinc_lock_mode 1即可。它很好地平衡了性能、一致性和并發(fā)性。理解MDL鎖和自增鎖能幫助我們?cè)谶M(jìn)行表結(jié)構(gòu)變更和高并發(fā)插入時(shí)提前預(yù)判風(fēng)險(xiǎn)制定更穩(wěn)妥的方案避免它們從“守護(hù)者”變成“性能殺手”。7. 鎖的實(shí)戰(zhàn)觀測(cè)與性能優(yōu)化思路理論最終要服務(wù)于實(shí)踐。我們?nèi)绾沃庇^地看到數(shù)據(jù)庫(kù)中的鎖又該如何基于對(duì)鎖的理解來(lái)優(yōu)化系統(tǒng)性能這是本章要解決的核心問(wèn)題。鎖信息觀測(cè)實(shí)戰(zhàn)SHOW ENGINE INNODB STATUS(經(jīng)典方法) 執(zhí)行該命令后在輸出結(jié)果中重點(diǎn)關(guān)注TRANSACTIONS和LATEST DETECTED DEADLOCK如果有部分。這里會(huì)顯示當(dāng)前活躍事務(wù)、持有的鎖以及鎖等待信息。雖然信息是文本格式不如新系統(tǒng)表直觀但在所有版本中均可用。---TRANSACTION 1234567890, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 100, OS thread handle 0x7f123456, query id 200 localhost root updating UPDATE accounts SET balance balance - 100 WHERE id 2 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 300 page no 3 n bits 72 index PRIMARY of table test.accounts trx id 1234567890 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: ...這段輸出告訴我們事務(wù)1234567890正在等待一個(gè)行鎖lock_mode X鎖的模式是locks rec but not gap記錄鎖非間隙鎖鎖在accounts表的主鍵索引上。information_schema與performance_schema(推薦MySQL 5.7/8.0)information_schema.innodb_locks/innodb_lock_waits(在8.0中已被移除遷移至performance_schema)可以查看當(dāng)前的鎖和鎖等待關(guān)系。performance_schema.data_locks(MySQL 8.0)記錄了所有當(dāng)前持有的鎖包括行鎖、表鎖、意向鎖等的詳細(xì)信息如鎖類型、模式、所屬對(duì)象等。performance_schema.data_lock_waits(MySQL 8.0)記錄了當(dāng)前的鎖等待關(guān)系明確指出哪個(gè)事務(wù)在等待哪個(gè)事務(wù)持有的鎖。一個(gè)常用的排查鎖阻塞的查詢-- MySQL 8.0 SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_engine_transaction_id INNER JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_engine_transaction_id;這個(gè)查詢能清晰地展示出“誰(shuí)被誰(shuí)阻塞了”是診斷鎖等待問(wèn)題的利器。基于鎖機(jī)制的SQL編寫與索引設(shè)計(jì)優(yōu)化對(duì)鎖的理解直接影響我們編寫SQL和設(shè)計(jì)索引的方式盡量使用主鍵或唯一索引進(jìn)行更新/刪除這能確保InnoDB使用行鎖將鎖的粒度控制在最小范圍。避免全表掃描導(dǎo)致的鎖升級(jí)。避免大范圍更新特別是無(wú)索引的更新UPDATE table SET status 0 WHERE status 1如果status字段沒(méi)有索引會(huì)鎖住大量甚至全部記錄極易引發(fā)長(zhǎng)時(shí)間鎖等待和死鎖。務(wù)必為WHERE條件中的字段添加索引。謹(jǐn)慎使用SELECT ... FOR UPDATE明確你真的需要“當(dāng)前讀”并鎖定數(shù)據(jù)。如果只是要保證讀取一致性RR級(jí)別下的普通SELECT快照讀通常就夠了它不會(huì)加鎖并發(fā)性能更高。只在需要基于查詢結(jié)果立即進(jìn)行更新且要防止其他事務(wù)修改時(shí)才使用FOR UPDATE。將大事務(wù)拆分為小事務(wù)這是黃金法則。一個(gè)更新10萬(wàn)行的事務(wù)持有鎖的時(shí)間可能長(zhǎng)達(dá)幾分鐘。拆分成每次更新1000行并在每個(gè)批次后提交能顯著減少鎖的持有時(shí)間和沖突概率。注意間隙鎖的影響范圍在RR級(jí)別下范圍查詢BETWEEN,,和FOR UPDATE會(huì)加間隙鎖。在設(shè)計(jì)業(yè)務(wù)邏輯和索引時(shí)要考慮這個(gè)特性。有時(shí)使用SELECT ... FOR UPDATE SKIP LOCKEDMySQL 8.0可以跳過(guò)已被鎖定的行實(shí)現(xiàn)簡(jiǎn)單的無(wú)鎖隊(duì)列提高并發(fā)處理能力。監(jiān)控innodb_row_lock_*狀態(tài)變量通過(guò)SHOW STATUS LIKE innodb_row_lock%;可以查看行鎖的競(jìng)爭(zhēng)情況。innodb_row_lock_current_waits當(dāng)前正在等待行鎖的數(shù)量。innodb_row_lock_time系統(tǒng)啟動(dòng)以來(lái)行鎖定的總時(shí)間毫秒。innodb_row_lock_time_avg每次行鎖等待的平均時(shí)間。innodb_row_lock_time_max行鎖等待的最長(zhǎng)時(shí)間。innodb_row_lock_waits系統(tǒng)啟動(dòng)以來(lái)行鎖等待發(fā)生的總次數(shù)。 如果innodb_row_lock_waits和innodb_row_lock_time_avg持續(xù)很高說(shuō)明行鎖競(jìng)爭(zhēng)激烈需要優(yōu)化。鎖是數(shù)據(jù)庫(kù)并發(fā)控制的精髓也是性能調(diào)優(yōu)的深水區(qū)。從被動(dòng)地解決鎖超時(shí)、死鎖告警到主動(dòng)地通過(guò)設(shè)計(jì)規(guī)避鎖沖突是每個(gè)后端開(kāi)發(fā)者成長(zhǎng)的必經(jīng)之路。掌握觀測(cè)工具理解鎖的行為模式才能讓我們?cè)跇?gòu)建高并發(fā)、高可用的數(shù)據(jù)服務(wù)時(shí)真正做到心中有數(shù)游刃有余。