各位看官內(nèi)容喜歡的話(huà)，動(dòng)動(dòng)手指點(diǎn)個(gè) ，點(diǎn)個(gè)關(guān)注唄！！謝謝支持！

如下圖所示，數(shù)據(jù)庫(kù)的隔離等級(jí)，SQL 語(yǔ)句和當(dāng)前數(shù)據(jù)庫(kù)數(shù)據(jù)會(huì)共同影響該條 SQL 執(zhí)行時(shí)數(shù)據(jù)庫(kù)生成的鎖模式，鎖類(lèi)型和鎖數(shù)量。

下面，我們會(huì)首先講解一下隔離等級(jí)、不同 SQL 語(yǔ)句 和 當(dāng)前數(shù)據(jù)庫(kù)數(shù)據(jù)對(duì)生成鎖影響的基本規(guī)則，然后再依次具體 SQL 的加鎖場(chǎng)景。

隔離等級(jí)對(duì)加鎖的影響

MySQL 的隔離等級(jí)對(duì)加鎖有影響，所以在分析具體加鎖場(chǎng)景時(shí)，首先要確定當(dāng)前的隔離等級(jí)。讀未提交(Read Uncommitted 后續(xù)簡(jiǎn)稱(chēng) RU)：可以讀到未提交的讀，基本上不會(huì)使用該隔離等級(jí)，所以暫時(shí)忽略。

讀已提交(Read Committed 后續(xù)簡(jiǎn)稱(chēng) RC)：存在幻讀問(wèn)題，對(duì)當(dāng)前讀獲取的數(shù)據(jù)加記錄鎖。

可重復(fù)讀(Repeatable Read 后續(xù)簡(jiǎn)稱(chēng) RR)：不存在幻讀問(wèn)題，對(duì)當(dāng)前讀獲取的數(shù)據(jù)加記錄鎖，同時(shí)對(duì)涉及的范圍加間隙鎖，防止新的數(shù)據(jù)插入，導(dǎo)致幻讀。

序列化(Serializable)：從 MVCC 并發(fā)控制退化到基于鎖的并發(fā)控制，不存在快照讀，都是當(dāng)前讀，并發(fā)效率急劇下降，不建議使用。

這里說(shuō)明一下，RC 總是讀取記錄的最新版本，而 RR 是讀取該記錄事務(wù)開(kāi)始時(shí)的那個(gè)版本，雖然這兩種讀取的版本不同，但是都是快照數(shù)據(jù)，并不會(huì)被寫(xiě)操作阻塞，所以這種讀操作稱(chēng)為 快照讀(Snapshot Read)

MySQL 還提供了另一種讀取方式叫當(dāng)前讀(Current Read)，它讀的不再是數(shù)據(jù)的快照版本，而是數(shù)據(jù)的最新版本，并會(huì)對(duì)數(shù)據(jù)加鎖，根據(jù)語(yǔ)句和加鎖的不同，又分成三種情況：SELECT ... LOCK IN SHARE MODE：加共享(S)鎖

SELECT ... FOR UPDATE：加排他(X)鎖

INSERT / UPDATE / DELETE：加排他(X)鎖

當(dāng)前讀在 RR 和 RC 兩種隔離級(jí)別下的實(shí)現(xiàn)也是不一樣的：RC 只加記錄鎖，RR 除了加記錄鎖，還會(huì)加間隙鎖，用于解決幻讀問(wèn)題。

不同 SQL 語(yǔ)句對(duì)加鎖的影響

不同的 SQL 語(yǔ)句當(dāng)然會(huì)加不同的鎖，總結(jié)起來(lái)主要分為五種情況：SELECT ... 語(yǔ)句正常情況下為快照讀，不加鎖；

SELECT ... LOCK IN SHARE MODE 語(yǔ)句為當(dāng)前讀，加 S 鎖；

SELECT ... FOR UPDATE 語(yǔ)句為當(dāng)前讀，加 X 鎖；

常見(jiàn)的 DML 語(yǔ)句(如 INSERT、DELETE、UPDATE)為當(dāng)前讀，加 X 鎖；

常見(jiàn)的 DDL 語(yǔ)句(如 ALTER、CREATE 等)加表級(jí)鎖，且這些語(yǔ)句為隱式提交，不能回滾。

其中，當(dāng)前讀的 SQL 語(yǔ)句的 where 從句的不同也會(huì)影響加鎖，包括是否使用索引，索引是否是唯一索引等等。

當(dāng)前數(shù)據(jù)對(duì)加鎖的影響

SQL 語(yǔ)句執(zhí)行時(shí)數(shù)據(jù)庫(kù)中的數(shù)據(jù)也會(huì)對(duì)加鎖產(chǎn)生影響。

比如一條最簡(jiǎn)單的根據(jù)主鍵進(jìn)行更新的 SQL 語(yǔ)句，如果主鍵存在，則只需要對(duì)其加記錄鎖，如果不存在，則需要在加間隙鎖。

至于其他非唯一性索引更新或者插入時(shí)的加鎖也都不同程度的受到現(xiàn)存數(shù)據(jù)的影響，后續(xù)我們會(huì)一一說(shuō)明。

具體場(chǎng)景分析

具體 SQL 場(chǎng)景分析主要借鑒何登成前輩的《MySQL 加鎖處理分析》文章和 aneasystone 的系列文章，在他們的基礎(chǔ)上進(jìn)行了總結(jié)和整理。

我們使用下面這張 book 表作為實(shí)例，其中 id 為主鍵，ISBN(書(shū)號(hào))為二級(jí)唯一索引，Author(作者)為二級(jí)非唯一索引，score(評(píng)分)無(wú)索引。

UPDATE 語(yǔ)句加鎖分析

下面，我們先來(lái)分析 UPDATE 相關(guān) SQL 在使用較為簡(jiǎn)單 where 從句情況下加鎖情況。其中的分析原則也適用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等當(dāng)前讀的語(yǔ)句。

聚簇索引，查詢(xún)命中

聚簇索引就是 InnoDB 存儲(chǔ)引擎下的主鍵索引，具體可參考《MySQL索引》。

下圖展示了使用 UPDATE book SET score = 9.2 WHERE ID = 10 語(yǔ)句命中的情況下在 RC 和 RR 隔離等級(jí)下的加鎖，兩種隔離等級(jí)下沒(méi)有任何區(qū)別，都是對(duì) ID = 10 這個(gè)索引加排他記錄鎖。

聚簇索引，查詢(xún)未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ID = 16 語(yǔ)句未命中時(shí) RR 隔離級(jí)別下的加鎖情況。

在 RC 隔離等級(jí)下，不需要加鎖；而在 RR 隔離級(jí)別會(huì)在 ID = 16 前后兩個(gè)索引之間加上間隙鎖。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入新行，導(dǎo)致幻讀，所以加間隙 S 鎖和加間隙 X 鎖沒(méi)有任何區(qū)別。

二級(jí)唯一索引，查詢(xún)命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔離等級(jí)下命中時(shí)的加鎖情況。

在 InnoDB 存儲(chǔ)引擎中，二級(jí)索引的葉子節(jié)點(diǎn)保存著主鍵索引的值，然后再拿主鍵索引去獲取真正的數(shù)據(jù)行，所以在這種情況下，二級(jí)索引和主鍵索引都會(huì)加排他記錄鎖。

二級(jí)唯一索引，查詢(xún)未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 語(yǔ)句在 RR 隔離等級(jí)下未命中時(shí)的加鎖情況，RC 隔離等級(jí)下該語(yǔ)句未命中不會(huì)加鎖。

因?yàn)?N0008 大于 N0007，所以要鎖住 (N0007,正無(wú)窮)這段區(qū)間，而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 來(lái)分別表示記錄的上下邊界。Infimum 是比該頁(yè)中任何記錄都要小的值，而 Supremum 比該頁(yè)中最大的記錄值還要大，這兩條記錄在創(chuàng)建頁(yè)的時(shí)候就有了，并且不會(huì)刪除。

所以，在 N0007 和 Suprenum Record 之間加了間隙鎖。

為什么不在主鍵上也加 GAP 鎖呢？歡迎留言說(shuō)出你的想法。

二級(jí)非唯一索引，查詢(xún)命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 語(yǔ)句在 RC 隔離等級(jí)下命中時(shí)的加鎖情況。

我們可以看到，在 RC 等級(jí)下，二級(jí)唯一索引和二級(jí)非唯一索引的加鎖情況是一致的，都是在涉及的二級(jí)索引和對(duì)應(yīng)的主鍵索引上加上排他記錄鎖。

但是在 RR 隔離等級(jí)下，加鎖的情況產(chǎn)生了變化，它不僅對(duì)涉及的二級(jí)索引和主鍵索引加了排他記錄鎖，還在非唯一二級(jí)索引上加了三個(gè)間隙鎖，鎖住了兩個(gè) Tom 索引值相關(guān)的三個(gè)范圍。

那為什么唯一索引不需要加間隙鎖呢？間隙鎖的作用是為了解決幻讀，防止其他事務(wù)插入相同索引值的記錄，而唯一索引和主鍵約束都已經(jīng)保證了該索引值肯定只有一條記錄，所以無(wú)需加間隙鎖。

需要注意的是，上圖雖然畫(huà)著 4 個(gè)記錄鎖，三個(gè)間隙鎖，但是實(shí)際上間隙鎖和它右側(cè)的記錄鎖會(huì)合并成 Next-Key 鎖。

所以實(shí)際情況有兩個(gè) Next-Key 鎖，一個(gè)間隙鎖(Tom60,正無(wú)窮)和兩個(gè)記錄鎖。

二級(jí)非唯一索引，查詢(xún)未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔離等級(jí)下未命中的加鎖情況，它會(huì)在二級(jí)索引 Rose 和 Tom 之間加間隙鎖。而 RC 隔離等級(jí)下不需要加鎖。

無(wú)索引

當(dāng) Where 從句的條件并不使用索引時(shí)，則會(huì)對(duì)全表進(jìn)行掃描，在 RC 隔離等級(jí)下對(duì)所有的數(shù)據(jù)加排他記錄鎖。在RR 隔離等級(jí)下，除了給記錄加鎖，還會(huì)對(duì)記錄和記錄之間加間隙鎖。和上邊一樣，間隙鎖會(huì)和左側(cè)的記錄鎖合并成 Next-Key 鎖。

下圖就是 UPDATE book SET score = 9.2 WHERE score = 22 語(yǔ)句在兩種隔離等級(jí)下的加鎖情況。

聚簇索引，范圍查詢(xún)

上面介紹的場(chǎng)景都是 where 從句的等值查詢(xún)，而范圍查詢(xún)的加鎖又是怎么樣的呢？我們慢慢來(lái)看。

下圖是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔離等級(jí)下的加鎖情況。

RC 場(chǎng)景下與等值查詢(xún)類(lèi)似，只會(huì)在涉及的 ID = 10，ID = 18 和 ID = 25 索引上加排他記錄鎖。

而在 RR 隔離等級(jí)下則有所不同，它會(huì)加上間隙鎖，和對(duì)應(yīng)的記錄鎖合并稱(chēng)為 Next-Key 鎖。除此之外，它還會(huì)在(25, 30] 上分別加 Next-Key 鎖。這一點(diǎn)是十分特殊的，具體原因還需要再探究。

二級(jí)索引，范圍查詢(xún)

下圖展示了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 級(jí)別下的加鎖情況。

修改索引值

UPDATE 語(yǔ)句修改索引值的情況可以分開(kāi)分析，首先 Where 從句的加鎖分析如上文所述，多了一步 Set 部分的加鎖。

下圖展示了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔離等級(jí)下的加鎖情況。除了在主鍵 ID 上進(jìn)行加鎖，還會(huì)對(duì)二級(jí)索引上的 Bob(就值) 和 John(新值) 上進(jìn)行加鎖。

DELETE 語(yǔ)句加鎖分析

一般來(lái)說(shuō)，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒(méi)有太大的差異。

因?yàn)?#xff0c;在 MySQL 數(shù)據(jù)庫(kù)中，執(zhí)行 DELETE 語(yǔ)句其實(shí)并沒(méi)有直接刪除記錄，而是在記錄上打上一個(gè)刪除標(biāo)記，然后通過(guò)后臺(tái)的一個(gè)叫做 purge 的線程來(lái)清理。從這一點(diǎn)來(lái)看，DELETE 和 UPDATE 確實(shí)是非常相像。事實(shí)上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的。

INSERT 語(yǔ)句加鎖分析

接下來(lái)，我們來(lái)看一下 Insert 語(yǔ)句的加鎖情況。

Insert 語(yǔ)句在兩種情況下會(huì)加鎖：為了防止幻讀，如果記錄之間加有間隙鎖，此時(shí)不能 Insert；

如果 Insert 的記錄和已有記錄造成唯一鍵沖突，此時(shí)不能 Insert；

除了上述情況，Insert 語(yǔ)句的鎖都是隱式鎖。隱式鎖是 InnoDB 實(shí)現(xiàn)的一種延遲加鎖的機(jī)制來(lái)減少加鎖的數(shù)量。

隱式鎖的特點(diǎn)是只有在可能發(fā)生沖突時(shí)才加鎖，減少了鎖的數(shù)量。另外，隱式鎖是針對(duì)被修改的 B+Tree 記錄，因此都是記錄類(lèi)型的鎖，不可能是間隙鎖或 Next-Key 類(lèi)型。

具體 Insert 語(yǔ)句的加鎖流程如下：首先對(duì)插入的間隙加插入意向鎖(Insert Intension Locks)如果該間隙已被加上了間隙鎖或 Next-Key 鎖，則加鎖失敗進(jìn)入等待；

如果沒(méi)有，則加鎖成功，表示可以插入；

然后判斷插入記錄是否有唯一鍵，如果有，則進(jìn)行唯一性約束檢查如果不存在相同鍵值，則完成插入

如果存在相同鍵值，則判斷該鍵值是否加鎖如果沒(méi)有鎖， 判斷該記錄是否被標(biāo)記為刪除如果標(biāo)記為刪除，說(shuō)明事務(wù)已經(jīng)提交，還沒(méi)來(lái)得及 purge，這時(shí)加 S 鎖等待；

如果沒(méi)有標(biāo)記刪除，則報(bào) duplicate key 錯(cuò)誤；

如果有鎖，說(shuō)明該記錄正在處理(新增、刪除或更新)，且事務(wù)還未提交，加 S 鎖等待；

插入記錄并對(duì)記錄加 X 記錄鎖；各位看官內(nèi)容喜歡的話(huà)，動(dòng)動(dòng)手指點(diǎn)個(gè) ，點(diǎn)個(gè)關(guān)注唄！！謝謝支持！

后續(xù)將有更多的技術(shù)內(nèi)容分享

鏈接：https://my.oschina.net/u/4215228/blog/4294120

總結(jié)

以上是生活随笔為你收集整理的mysql死锁影响_MySQL死锁系列-常见加锁场景分析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。