MySQL是怎么加行級(jí)鎖的？有什么規(guī)則？

是不是很多人都對(duì) MySQL 加行級(jí)鎖的規(guī)則搞的迷迷糊糊，對(duì)記錄一會(huì)加的是 next-key 鎖，一會(huì)加是間隙鎖，一會(huì)又是記錄鎖。

坦白說(shuō)，確實(shí)還挺復(fù)雜的，但是好在我找點(diǎn)了點(diǎn)規(guī)律，也知道如何如何用命令分析加了什么類(lèi)型的行級(jí)鎖。

為了說(shuō)清楚這三件事情：

1、MySQL 是怎么加行級(jí)鎖的？有什么規(guī)則？

2、為什么MySQL 要這么加行級(jí)鎖？

3、如何用命令分析加了什么行級(jí)鎖？

什么 SQL 語(yǔ)句會(huì)加行級(jí)鎖？

InnoDB 引擎是支持行級(jí)鎖的，而 MyISAM 引擎并不支持行級(jí)鎖，所以后面的內(nèi)容都是基于 InnoDB 引擎 的。

所以，在說(shuō) MySQL 是怎么加行級(jí)鎖的時(shí)候，其實(shí)是在說(shuō) InnoDB 引擎是怎么加行級(jí)鎖的。

普通的 select 語(yǔ)句是不會(huì)對(duì)記錄加鎖的，因?yàn)樗鼘儆诳煺兆x，是通過(guò) MVCC（多版本并發(fā)控制）實(shí)現(xiàn)的。

如果要在查詢(xún)時(shí)對(duì)記錄加行級(jí)鎖，可以使用下面這兩個(gè)方式，這兩種查詢(xún)會(huì)加鎖的語(yǔ)句稱(chēng)為鎖定讀。

//對(duì)讀取的記錄加共享鎖(S型鎖)
select...lockinsharemode;

//對(duì)讀取的記錄加獨(dú)占鎖(X型鎖)
select...forupdate;

上面這兩條語(yǔ)句必須在一個(gè)事務(wù)中，因?yàn)楫?dāng)事務(wù)提交了，鎖就會(huì)被釋放，所以在使用這兩條語(yǔ)句的時(shí)候，要加上 begin 或者 start transaction 開(kāi)啟事務(wù)的語(yǔ)句。

**除了上面這兩條鎖定讀語(yǔ)句會(huì)加行級(jí)鎖之外，update 和 delete 操作都會(huì)加行級(jí)鎖，且鎖的類(lèi)型都是獨(dú)占鎖(X型鎖)**。

//對(duì)操作的記錄加獨(dú)占鎖(X型鎖)
updaettable....whereid=1;

//對(duì)操作的記錄加獨(dú)占鎖(X型鎖)
deletefromtablewhereid=1;

共享鎖（S鎖）滿(mǎn)足讀讀共享，讀寫(xiě)互斥。獨(dú)占鎖（X鎖）滿(mǎn)足寫(xiě)寫(xiě)互斥、讀寫(xiě)互斥。

行級(jí)鎖有哪些種類(lèi)？

不同隔離級(jí)別下，行級(jí)鎖的種類(lèi)是不同的。

在讀已提交隔離級(jí)別下，行級(jí)鎖的種類(lèi)只有記錄鎖，也就是僅僅把一條記錄鎖上。

在可重復(fù)讀隔離級(jí)別下，行級(jí)鎖的種類(lèi)除了有記錄鎖，還有間隙鎖（目的是為了避免幻讀），所以行級(jí)鎖的種類(lèi)主要有三類(lèi)：

Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；

Gap Lock，間隙鎖，鎖定一個(gè)范圍，但是不包含記錄本身；

Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個(gè)范圍，并且鎖定記錄本身。

接下來(lái)，分別介紹這三種行級(jí)鎖。

Record Lock

Record Lock 稱(chēng)為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的：

當(dāng)一個(gè)事務(wù)對(duì)一條記錄加了 S 型記錄鎖后，其他事務(wù)也可以繼續(xù)對(duì)該記錄加 S 型記錄鎖（S 型與 S 鎖兼容），但是不可以對(duì)該記錄加 X 型記錄鎖（S 型與 X 鎖不兼容）;

當(dāng)一個(gè)事務(wù)對(duì)一條記錄加了 X 型記錄鎖后，其他事務(wù)既不可以對(duì)該記錄加 S 型記錄鎖（S 型與 X 鎖不兼容），也不可以對(duì)該記錄加 X 型記錄鎖（X 型與 X 鎖不兼容）。

舉個(gè)例子，當(dāng)一個(gè)事務(wù)執(zhí)行了下面這條語(yǔ)句：

mysql>begin;
mysql>select*fromt_testwhereid=1forupdate;

事務(wù)會(huì)對(duì)表中主鍵 id = 1 的這條記錄加上 X 型的記錄鎖，如果這時(shí)候其他事務(wù)對(duì)這條記錄進(jìn)行刪除或者更新操作，那么這些操作都會(huì)被阻塞。

注意，其他事務(wù)插入一條 id = 1 的新記錄并不會(huì)被阻塞，而是會(huì)報(bào)主鍵沖突的錯(cuò)誤，這是因?yàn)橹麈I有唯一性的約束。

當(dāng)事務(wù)執(zhí)行 commit 后，事務(wù)過(guò)程中生成的鎖都會(huì)被釋放。

Gap Lock

Gap Lock 稱(chēng)為間隙鎖，只存在于可重復(fù)讀隔離級(jí)別，目的是為了解決可重復(fù)讀隔離級(jí)別下幻讀的現(xiàn)象。

假設(shè)，表中有一個(gè)范圍 id 為（3，5）間隙鎖，那么其他事務(wù)就無(wú)法插入 id = 4 這條記錄了，這樣就有效的防止幻讀現(xiàn)象的發(fā)生。

間隙鎖雖然存在 X 型間隙鎖和 S 型間隙鎖，但是并沒(méi)有什么區(qū)別，間隙鎖之間是兼容的，即兩個(gè)事務(wù)可以同時(shí)持有包含共同間隙范圍的間隙鎖，并不存在互斥關(guān)系，因?yàn)殚g隙鎖的目的是防止插入幻影記錄而提出的。

Next-Key Lock

Next-Key Lock 稱(chēng)為臨鍵鎖，是 Record Lock + Gap Lock 的組合，鎖定一個(gè)范圍，并且鎖定記錄本身。

假設(shè)，表中有一個(gè)范圍 id 為（3，5] 的 next-key lock，那么其他事務(wù)即不能插入 id = 4 記錄，也不能修改和刪除 id = 5 這條記錄。

所以，next-key lock 即能保護(hù)該記錄，又能阻止其他事務(wù)將新記錄插入到被保護(hù)記錄前面的間隙中。

next-key lock 是包含間隙鎖+記錄鎖的，如果一個(gè)事務(wù)獲取了 X 型的 next-key lock，那么另外一個(gè)事務(wù)在獲取相同范圍的 X 型的 next-key lock 時(shí)，是會(huì)被阻塞的。

比如，一個(gè)事務(wù)持有了范圍為 (1, 10] 的 X 型的 next-key lock，那么另外一個(gè)事務(wù)在獲取相同范圍的 X 型的 next-key lock 時(shí)，就會(huì)被阻塞。

雖然相同范圍的間隙鎖是多個(gè)事務(wù)相互兼容的，但對(duì)于記錄鎖，我們是要考慮 X 型與 S 型關(guān)系，X 型的記錄鎖與 X 型的記錄鎖是沖突的。

MySQL 是怎么加行級(jí)鎖的？

行級(jí)鎖加鎖規(guī)則比較復(fù)雜，不同的場(chǎng)景，加鎖的形式是不同的。

加鎖的對(duì)象是索引，加鎖的基本單位是 next-key lock，它是由記錄鎖和間隙鎖組合而成的，next-key lock 是前開(kāi)后閉區(qū)間，而間隙鎖是前開(kāi)后開(kāi)區(qū)間。

但是，next-key lock 在一些場(chǎng)景下會(huì)退化成記錄鎖或間隙鎖。

那到底是什么場(chǎng)景呢？總結(jié)一句，在能使用記錄鎖或者間隙鎖就能避免幻讀現(xiàn)象的場(chǎng)景下， next-key lock 就會(huì)退化成退化成記錄鎖或間隙鎖。

這次會(huì)以下面這個(gè)表結(jié)構(gòu)來(lái)進(jìn)行實(shí)驗(yàn)說(shuō)明：

CREATETABLE`user`(
`id`bigintNOTNULLAUTO_INCREMENT,
`name`varchar(30)COLLATEutf8mb4_unicode_ciNOTNULL,
`age`intNOTNULL,
PRIMARYKEY(`id`),
KEY`index_age`(`age`)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

其中，id 是主鍵索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。

表中的有這些行記錄：

這次實(shí)驗(yàn)環(huán)境的 MySQL 版本是 8.0.26，隔離級(jí)別是「可重復(fù)讀」。

不同版本的加鎖規(guī)則可能是不同的，但是大體上是相同的。

唯一索引等值查詢(xún)

當(dāng)我們用唯一索引進(jìn)行等值查詢(xún)的時(shí)候，查詢(xún)的記錄存不存在，加鎖的規(guī)則也會(huì)不同：

當(dāng)查詢(xún)的記錄是「存在」的，在索引樹(shù)上定位到這一條記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「記錄鎖」。

當(dāng)查詢(xún)的記錄是「不存在」的，在索引樹(shù)找到第一條大于該查詢(xún)記錄的記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「間隙鎖」。

接下里用兩個(gè)案例來(lái)說(shuō)明。

1、記錄存在的情況

假設(shè)事務(wù) A 執(zhí)行了這條等值查詢(xún)語(yǔ)句，查詢(xún)的記錄是「存在」于表中的。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid=1forupdate;
+----+--------+-----+
|id|name|age|
+----+--------+-----+
|1|路飛|19|
+----+--------+-----+
1rowinset(0.02sec)

那么，事務(wù) A 會(huì)為 id 為 1 的這條記錄就會(huì)加上 X 型的記錄鎖。

接下來(lái)，如果有其他事務(wù)，對(duì) id 為 1 的記錄進(jìn)行更新或者刪除操作的話，這些操作都會(huì)被阻塞，因?yàn)楦禄蛘邉h除操作也會(huì)對(duì)記錄加 X 型的記錄鎖，而 X 鎖和 X 鎖之間是互斥關(guān)系。

比如，下面這個(gè)例子：

因?yàn)槭聞?wù) A 對(duì) id = 1的記錄加了 X 型的記錄鎖，所以事務(wù) B 在修改 id=1 的記錄時(shí)會(huì)被阻塞，事務(wù) C 在刪除 id=1 的記錄時(shí)也會(huì)被阻塞。

有什么命令可以分析加了什么鎖？

我們可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句，查看事務(wù)執(zhí)行 SQL 過(guò)程中加了什么鎖。

我們以前面的事務(wù) A 作為例子，分析下下它加了什么鎖。

從上圖可以看到，共加了兩個(gè)鎖，分別是：

表鎖：X 類(lèi)型的意向鎖；

行鎖：X 類(lèi)型的記錄鎖；

這里我們重點(diǎn)關(guān)注行級(jí)鎖，圖中 LOCK_TYPE 中的 RECORD 表示行級(jí)鎖，而不是記錄鎖的意思。

通過(guò) LOCK_MODE 可以確認(rèn)是 next-key 鎖，還是間隙鎖，還是記錄鎖：

如果 LOCK_MODE 為 X，說(shuō)明是 next-key 鎖；

如果 LOCK_MODE 為 X, REC_NOT_GAP，說(shuō)明是記錄鎖；

如果 LOCK_MODE 為 X, GAP，說(shuō)明是間隙鎖；

因此，此時(shí)事務(wù) A 在 id = 1 記錄的主鍵索引上加的是記錄鎖，鎖住的范圍是 id 為 1 的這條記錄。這樣其他事務(wù)就無(wú)法對(duì) id 為 1 的這條記錄進(jìn)行更新和刪除操作了。

從這里我們也可以得知，加鎖的對(duì)象是針對(duì)索引，因?yàn)檫@里查詢(xún)語(yǔ)句掃描的 B+ 樹(shù)是聚簇索引樹(shù)，即主鍵索引樹(shù)，所以是對(duì)主鍵索引加鎖。將對(duì)應(yīng)記錄的主鍵索引加 記錄鎖后，就意味著其他事務(wù)無(wú)法對(duì)該記錄進(jìn)行更新和刪除操作了。

為什么唯一索引等值查詢(xún)并且查詢(xún)記錄存在的場(chǎng)景下，該記錄的索引中的 next-key lock 會(huì)退化成記錄鎖？

原因就是在唯一索引等值查詢(xún)并且查詢(xún)記錄存在的場(chǎng)景下，僅靠記錄鎖也能避免幻讀的問(wèn)題。

幻讀的定義就是，當(dāng)一個(gè)事務(wù)前后兩次查詢(xún)的結(jié)果集，不相同時(shí)，就認(rèn)為發(fā)生幻讀。所以，要避免幻讀就是避免結(jié)果集某一條記錄被其他事務(wù)刪除，或者有其他事務(wù)插入了一條新記錄，這樣前后兩次查詢(xún)的結(jié)果集就不會(huì)出現(xiàn)不相同的情況。

由于主鍵具有唯一性，所以其他事務(wù)插入 id = 1 的時(shí)候，會(huì)因?yàn)橹麈I沖突，導(dǎo)致無(wú)法插入 id = 1 的新記錄。這樣事務(wù) A 在多次查詢(xún) id = 1 的記錄的時(shí)候，不會(huì)出現(xiàn)前后兩次查詢(xún)的結(jié)果集不同，也就避免了幻讀的問(wèn)題。

由于對(duì) id = 1 加了記錄鎖，其他事務(wù)無(wú)法刪除該記錄，這樣事務(wù) A 在多次查詢(xún) id = 1 的記錄的時(shí)候，不會(huì)出現(xiàn)前后兩次查詢(xún)的結(jié)果集不同，也就避免了幻讀的問(wèn)題。

2、記錄不存在的情況

假設(shè)事務(wù) A 執(zhí)行了這條等值查詢(xún)語(yǔ)句，查詢(xún)的記錄是「不存在」于表中的。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid=2forupdate;
Emptyset(0.03sec)

接下來(lái)，通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句，查看事務(wù)執(zhí)行 SQL 過(guò)程中加了什么鎖。

從上圖可以看到，共加了兩個(gè)鎖，分別是：

表鎖：X 類(lèi)型的意向鎖；

行鎖：X 類(lèi)型的間隙鎖；

因此，此時(shí)事務(wù) A 在 id = 5 記錄的主鍵索引上加的是間隙鎖，鎖住的范圍是 (1, 5)。

接下來(lái)，如果有其他事務(wù)插入 id 值為 2、3、4 這一些記錄的話，這些插入語(yǔ)句都會(huì)發(fā)生阻塞。

注意，如果其他事務(wù)插入的 id = 1 或者 id = 5 的記錄話，并不會(huì)發(fā)生阻塞，而是報(bào)主鍵沖突的錯(cuò)誤，因?yàn)楸碇幸呀?jīng)存在 id = 1 和 id = 5 的記錄了。

比如，下面這個(gè)例子：

因?yàn)槭聞?wù) A 在 id = 5 記錄的主鍵索引上加了范圍為 (1, 5) 的 X 型間隙鎖，所以事務(wù) B 在插入一條 id 為 3 的記錄時(shí)會(huì)被阻塞住，即無(wú)法插入 id = 3 的記錄。

間隙鎖的范圍(1, 5) ，是怎么確定的？

根據(jù)我的經(jīng)驗(yàn)，如果 LOCK_MODE 是 next-key 鎖或者間隙鎖，那么 LOCK_DATA 就表示鎖的范圍「右邊界」，此次的事務(wù) A 的 LOCK_DATA 是 5。

然后鎖范圍的「左邊界」是表中 id 為 5 的上一條記錄的 id 值，即 1。

因此，間隙鎖的范圍(1, 5)。

為什么唯一索引等值查詢(xún)并且查詢(xún)記錄「不存在」的場(chǎng)景下，在索引樹(shù)找到第一條大于該查詢(xún)記錄的記錄后，要將該記錄的索引中的 next-key lock 會(huì)退化成「間隙鎖」？

原因就是在唯一索引等值查詢(xún)并且查詢(xún)記錄不存在的場(chǎng)景下，僅靠間隙鎖就能避免幻讀的問(wèn)題。

為什么 id = 5 記錄上的主鍵索引的鎖不可以是 next-key lock？如果是 next-key lock，就意味著其他事務(wù)無(wú)法刪除 id = 5 這條記錄，但是這次的案例是查詢(xún) id = 2 的記錄，只要保證前后兩次查詢(xún) id = 2 的結(jié)果集相同，就能避免幻讀的問(wèn)題了，所以即使 id =5 被刪除，也不會(huì)有什么影響，那就沒(méi)必須加 next-key lock，因此只需要在 id = 5 加間隙鎖，避免其他事務(wù)插入 id = 2 的新記錄就行了。

為什么不可以針對(duì)不存在的記錄加記錄鎖？鎖是加在索引上的，而這個(gè)場(chǎng)景下查詢(xún)的記錄是不存在的，自然就沒(méi)辦法鎖住這條不存在的記錄。

唯一索引范圍查詢(xún)

范圍查詢(xún)和等值查詢(xún)的加鎖規(guī)則是不同的。

當(dāng)唯一索引進(jìn)行范圍查詢(xún)時(shí)，會(huì)對(duì)每一個(gè)掃描到的索引加 next-key 鎖，然后如果遇到下面這些情況，會(huì)退化成記錄鎖或者間隙鎖：

情況一：針對(duì)「大于等于」的范圍查詢(xún)，因?yàn)榇嬖诘戎挡樵?xún)的條件，那么如果等值查詢(xún)的記錄是存在于表中，那么該記錄的索引中的 next-key 鎖會(huì)退化成記錄鎖。

情況二：針對(duì)「小于或者小于等于」的范圍查詢(xún)，要看條件值的記錄是否存在于表中：

當(dāng)條件值的記錄不在表中，那么不管是「小于」還是「小于等于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖。

當(dāng)條件值的記錄在表中，如果是「小于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖；如果「小于等于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的索引 next-key 鎖不會(huì)退化成間隙鎖。其他掃描到的記錄，都是在這些記錄的索引上加 next-key 鎖。

接下來(lái)，通過(guò)幾個(gè)實(shí)驗(yàn)，才驗(yàn)證我上面說(shuō)的結(jié)論。

1、針對(duì)「大于或者大于等于」的范圍查詢(xún)

實(shí)驗(yàn)一：針對(duì)「大于」的范圍查詢(xún)的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢(xún)語(yǔ)句：

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid>15forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|20|香克斯|39|
+----+-----------+-----+
1rowinset(0.01sec)

事務(wù) A 加鎖變化過(guò)程如下：

最開(kāi)始要找的第一行是 id = 20，由于查詢(xún)?cè)撚涗洸皇且粋€(gè)等值查詢(xún)（不是大于等于條件查詢(xún)），所以對(duì)該主鍵索引加的是范圍為 (15, 20] 的 next-key 鎖；

由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，雖然我們看見(jiàn)表中最后一條記錄是 id = 20 的記錄，但是實(shí)際在 Innodb 存儲(chǔ)引擎中，會(huì)用一個(gè)特殊的記錄來(lái)標(biāo)識(shí)最后一條記錄，該特殊的記錄的名字叫 supremum pseudo-record ，所以掃描第二行的時(shí)候，也就掃描到了這個(gè)特殊記錄的時(shí)候，會(huì)對(duì)該主鍵索引加的是范圍為 (20, +∞] 的 next-key 鎖。

停止掃描。

可以得知，事務(wù) A 在主鍵索引上加了兩個(gè) X 型 的 next-key 鎖：

在 id = 20 這條記錄的主鍵索引上，加了范圍為 (15, 20] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 20 的記錄，同時(shí)無(wú)法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了范圍為 (20, +∞] 的 next-key 鎖，意味著其他事務(wù)無(wú)法插入 id 值大于 20 的這一些新記錄。

我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

從上圖中的分析中，也可以得到事務(wù) A 在主鍵索引上加了兩個(gè) X 型 的next-key 鎖：

在 id = 20 這條記錄的主鍵索引上，加了范圍為 (15, 20] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 20 的記錄，同時(shí)無(wú)法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了范圍為 (20, +∞] 的 next-key 鎖，意味著其他事務(wù)無(wú)法插入 id 值大于 20 的這一些新記錄。

實(shí)驗(yàn)二：針對(duì)「大于等于」的范圍查詢(xún)的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢(xún)語(yǔ)句：

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid>=15forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|15|烏索普|20|
|20|香克斯|39|
+----+-----------+-----+
2rowsinset(0.00sec)

事務(wù) A 加鎖變化過(guò)程如下：

最開(kāi)始要找的第一行是 id = 15，由于查詢(xún)?cè)撚涗浭且粋€(gè)等值查詢(xún)（等于 15），所以該主鍵索引的 next-key 鎖會(huì)退化成記錄鎖，也就是僅鎖住 id = 15 這一行記錄。

由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，掃描到的第二行是 id = 20，于是對(duì)該主鍵索引加的是范圍為 (15, 20] 的 next-key 鎖；

接著掃描到第三行的時(shí)候，掃描到了特殊記錄（ supremum pseudo-record），于是對(duì)該主鍵索引加的是范圍為 (20, +∞] 的 next-key 鎖。

停止掃描。

可以得知，事務(wù) A 在主鍵索引上加了三個(gè) X 型 的鎖，分別是：

在 id = 15 這條記錄的主鍵索引上，加了記錄鎖，范圍是 id = 15 這一行記錄；意味著其他事務(wù)無(wú)法更新或者刪除 id = 15 的這一條記錄；

在 id = 20 這條記錄的主鍵索引上，加了 next-key 鎖，范圍是 (15, 20] 。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 20 的記錄，同時(shí)無(wú)法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了 next-key 鎖，范圍是 (20, +∞] 。意味著其他事務(wù)無(wú)法插入 id 值大于 20 的這一些新記錄。

我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

通過(guò)前面這個(gè)實(shí)驗(yàn)，我們證明了：

針對(duì)「大于等于」條件的唯一索引范圍查詢(xún)的情況下， 如果條件值的記錄存在于表中，那么由于查詢(xún)?cè)摋l件值的記錄是包含一個(gè)等值查詢(xún)的操作，所以該記錄的索引中的 next-key 鎖會(huì)退化成記錄鎖。

2、針對(duì)「小于或者小于等于」的范圍查詢(xún)

實(shí)驗(yàn)一：針對(duì)「小于」的范圍查詢(xún)時(shí)，查詢(xún)條件值的記錄「不存在」表中的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢(xún)語(yǔ)句，注意查詢(xún)條件值的記錄（id 為 6）并不存在于表中。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid

	

	事務(wù) A 加鎖變化過(guò)程如下：

	最開(kāi)始要找的第一行是 id = 1，于是對(duì)該主鍵索引加的是范圍為 (-∞, 1] 的 next-key 鎖；

	由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，掃描到的第二行是 id = 5，所以對(duì)該主鍵索引加的是范圍為 (1, 5] 的 next-key 鎖；

	由于掃描到的第二行記錄（id = 5），滿(mǎn)足 id < 6 條件，而且也沒(méi)有達(dá)到終止掃描的條件，接著會(huì)繼續(xù)掃描。

	掃描到的第三行是 id = 10，該記錄不滿(mǎn)足 id < 6 條件的記錄，所以 id = 10 這一行記錄的鎖會(huì)退化成間隙鎖，于是對(duì)該主鍵索引加的是范圍為 (5, 10) 的間隙鎖。

	由于掃描到的第三行記錄（id = 10），不滿(mǎn)足 id < 6 條件，達(dá)到了終止掃描的條件，于是停止掃描。

	從上面的分析中，可以得知事務(wù) A 在主鍵索引上加了三個(gè) X 型的鎖：

	

	在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。

	在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 5 的這一條記錄，同時(shí)也無(wú)法插入 id 值為 2、3、4 的這一些新記錄。

	在 id = 10 這條記錄的主鍵索引上，加了范圍為 (5, 10) 的間隙鎖，意味著其他事務(wù)無(wú)法插入 id 值為 6、7、8、9 的這一些新記錄。

	我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

	輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

	

	從上圖中的分析中，也可以得知事務(wù) A 在主鍵索引加的三個(gè)鎖，就是我們前面分析出那三個(gè)鎖。

	雖然這次范圍查詢(xún)的條件是「小于」，但是查詢(xún)條件值的記錄不存在于表中（ id 為 6 的記錄不在表中），所以如果事務(wù) A 的范圍查詢(xún)的條件改成 <= 6 的話，加的鎖還是和范圍查詢(xún)條件為 < 6 是一樣的。大家自己也驗(yàn)證下這個(gè)結(jié)論。

	因此，針對(duì)「小于或者小于等于」的唯一索引范圍查詢(xún)，如果條件值的記錄不在表中，那么不管是「小于」還是「小于等于」的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄中索引的 next-key 鎖會(huì)退化成間隙鎖，其他掃描的記錄，則是在這些記錄的索引上加 next-key 鎖。

	實(shí)驗(yàn)二：針對(duì)「小于等于」的范圍查詢(xún)時(shí)，查詢(xún)條件值的記錄「存在」表中的情況。

	假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢(xún)語(yǔ)句，注意查詢(xún)條件值的記錄（id 為 5）存在于表中。

	
mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid<=?5?for?update;
+----+--------+-----+
|?id?|?name???|?age?|
+----+--------+-----+
|??1?|?路飛???|??19?|
|??5?|?索隆???|??21?|
+----+--------+-----+
2?rows?in?set?(0.00?sec)


	

	事務(wù) A 加鎖變化過(guò)程如下：

	最開(kāi)始要找的第一行是 id = 1，于是對(duì)該記錄加的是范圍為 (-∞, 1] 的 next-key 鎖；

	由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，掃描到的第二行是 id = 5，于是對(duì)該記錄加的是范圍為 (1, 5] 的 next-key 鎖。

	由于主鍵索引具有唯一性，不會(huì)存在兩個(gè) id = 5 的記錄，所以不會(huì)再繼續(xù)掃描，于是停止掃描。

	從上面的分析中，可以得到事務(wù) A 在主鍵索引上加了 2 個(gè) X 型的鎖：

	

	在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。

	在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖。意味著其他事務(wù)即無(wú)法更新或者刪除 id = 5 的這一條記錄，同時(shí)也無(wú)法插入 id 值為 2、3、4 的這一些新記錄。

	我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

	輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

	

	從上圖中的分析中，可以得到事務(wù) A 在主鍵索引上加了兩個(gè) X 型 next-key 鎖，分別是：

	在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖；

	在 id = 5 這條記錄的主鍵索引上，加了范圍為(1, 5 ] 的 next-key 鎖。

	實(shí)驗(yàn)三：再來(lái)看針對(duì)「小于」的范圍查詢(xún)時(shí)，查詢(xún)條件值的記錄「存在」表中的情況。

	如果事務(wù) A 的查詢(xún)語(yǔ)句是小于的范圍查詢(xún)，且查詢(xún)條件值的記錄（id 為 5）存在于表中。

	
select*fromuserwhereid

	

	事務(wù) A 加鎖變化過(guò)程如下：

	最開(kāi)始要找的第一行是 id = 1，于是對(duì)該記錄加的是范圍為 (-∞, 1] 的 next-key 鎖；

	由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，掃描到的第二行是 id = 5，該記錄是第一條不滿(mǎn)足 id < 5 條件的記錄，于是**該記錄的鎖會(huì)退化為間隙鎖，鎖范圍是 (1,5)**。

	由于找到了第一條不滿(mǎn)足 id < 5 條件的記錄，于是停止掃描。

	可以得知，此時(shí)事務(wù) A 在主鍵索引上加了兩種 X 型鎖：

	

	在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖，意味著其他事務(wù)即無(wú)法更新或者刪除 id = 1 的這一條記錄，同時(shí)也無(wú)法插入 id 小于 1 的這一些新記錄。

	在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1,5) 的間隙鎖，意味著其他事務(wù)無(wú)法插入 id 值為 2、3、4 的這一些新記錄。

	我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

	輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

	

	從上圖中的分析中，可以得到事務(wù) A 在主鍵索引上加了 X 型的范圍為 (-∞, 1] 的 next-key 鎖，和 X 型的范圍為 (1, 5) 的間隙鎖。

	因此，通過(guò)前面這三個(gè)實(shí)驗(yàn)，可以得知。

	在針對(duì)「小于或者小于等于」的唯一索引（主鍵索引）范圍查詢(xún)時(shí)，存在這兩種情況會(huì)將索引的 next-key 鎖會(huì)退化成間隙鎖的：

	當(dāng)條件值的記錄「不在」表中時(shí)，那么不管是「小于」還是「小于等于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的主鍵索引中的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的主鍵索引上加 next-key 鎖。

	當(dāng)條件值的記錄「在」表中時(shí)：

	如果是「小于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的主鍵索引中的 next-key 鎖會(huì)退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的主鍵索引上，加 next-key 鎖。

	如果是「小于等于」條件的范圍查詢(xún)，掃描到終止范圍查詢(xún)的記錄時(shí)，該記錄的主鍵索引中的 next-key 鎖「不會(huì)」退化成間隙鎖，其他掃描到的記錄，都是在這些記錄的主鍵索引上加 next-key 鎖。

	非唯一索引等值查詢(xún)

	當(dāng)我們用非唯一索引進(jìn)行等值查詢(xún)的時(shí)候，因?yàn)榇嬖趦蓚€(gè)索引，一個(gè)是主鍵索引，一個(gè)是非唯一索引（二級(jí)索引），所以在加鎖時(shí)，同時(shí)會(huì)對(duì)這兩個(gè)索引都加鎖，但是對(duì)主鍵索引加鎖的時(shí)候，只有滿(mǎn)足查詢(xún)條件的記錄才會(huì)對(duì)它們的主鍵索引加鎖。

	針對(duì)非唯一索引等值查詢(xún)時(shí)，查詢(xún)的記錄存不存在，加鎖的規(guī)則也會(huì)不同：

	當(dāng)查詢(xún)的記錄「存在」時(shí)，由于不是唯一索引，所以肯定存在索引值相同的記錄，于是非唯一索引等值查詢(xún)的過(guò)程是一個(gè)掃描的過(guò)程，直到掃描到第一個(gè)不符合條件的二級(jí)索引記錄就停止掃描，然后在掃描的過(guò)程中，對(duì)掃描到的二級(jí)索引記錄加的是 next-key 鎖，而對(duì)于第一個(gè)不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。同時(shí)，在符合查詢(xún)條件的記錄的主鍵索引上加記錄鎖。

	當(dāng)查詢(xún)的記錄「不存在」時(shí)，掃描到第一條不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。因?yàn)椴淮嬖跐M(mǎn)足查詢(xún)條件的記錄，所以不會(huì)對(duì)主鍵索引加鎖。

	接下里用兩個(gè)實(shí)驗(yàn)來(lái)說(shuō)明。

	1、記錄不存在的情況

	實(shí)驗(yàn)一：針對(duì)非唯一索引等值查詢(xún)時(shí)，查詢(xún)的值不存在的情況。

	先來(lái)說(shuō)說(shuō)非唯一索引等值查詢(xún)時(shí)，查詢(xún)的記錄不存在的情況，因?yàn)檫@個(gè)比較簡(jiǎn)單。

	假設(shè)事務(wù) A 對(duì)非唯一索引（age）進(jìn)行了等值查詢(xún)，且表中不存在 age = 25 的記錄。

	
mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage=25forupdate;
Emptyset(0.00sec)


	

	事務(wù) A 加鎖變化過(guò)程如下：

	定位到第一條不符合查詢(xún)條件的二級(jí)索引記錄，即掃描到 age = 39，于是**該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖，范圍是 (22, 39)**。

	停止查詢(xún)

	事務(wù) A 在 age = 39 記錄的二級(jí)索引上，加了 X 型的間隙鎖，范圍是 (22, 39)。意味著其他事務(wù)無(wú)法插入 age 值為 23、24、25、26、....、38 這些新記錄。不過(guò)對(duì)于插入 age = 22 和 age = 39 記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入，具體哪些情況，會(huì)在后面說(shuō)。

	

	我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

	輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

	

	從上圖的分析，可以看到，事務(wù) A 在 age = 39 記錄的二級(jí)索引上（INDEX_NAME: index_age ），加了范圍為 (22, 39) 的 X 型間隙鎖。

	此時(shí)，如果有其他事務(wù)插入了 age 值為 23、24、25、26、....、38 這些新記錄，那么這些插入語(yǔ)句都會(huì)發(fā)生阻塞。不過(guò)對(duì)于插入 age = 39 記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入，具體哪些情況，接下來(lái)我們就說(shuō)！

	當(dāng)有一個(gè)事務(wù)持有二級(jí)索引的間隙鎖 (22, 39) 時(shí)，什么情況下，可以讓其他事務(wù)的插入 age = 22 或者 age = 39 記錄的語(yǔ)句成功？又是什么情況下，插入 age = 22 或者 age = 39 記錄時(shí)的語(yǔ)句會(huì)被阻塞？

	我們先要清楚，什么情況下插入語(yǔ)句會(huì)發(fā)生阻塞。

	插入語(yǔ)句在插入一條記錄之前，需要先定位到該記錄在 B+樹(shù) 的位置，如果插入的位置的下一條記錄的索引上有間隙鎖，才會(huì)發(fā)生阻塞。

	在分析二級(jí)索引的間隙鎖是否可以成功插入記錄時(shí)，我們要先要知道二級(jí)索引樹(shù)是如何存放記錄的？

	二級(jí)索引樹(shù)是按照二級(jí)索引值（age列）按順序存放的，在相同的二級(jí)索引值情況下， 再按主鍵 id 的順序存放。知道了這個(gè)前提，我們才能知道執(zhí)行插入語(yǔ)句的時(shí)候，插入的位置的下一條記錄是誰(shuí)。

	基于前面的實(shí)驗(yàn)，事務(wù) A 是在 age = 39 記錄的二級(jí)索引上，加了 X 型的間隙鎖，范圍是 (22, 39)。

	插入 age = 22 記錄的成功和失敗的情況分別如下：

	當(dāng)其他事務(wù)插入一條 age = 22，id = 3 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 10、age = 22 的記錄，該記錄的二級(jí)索引上沒(méi)有間隙鎖，所以這條插入語(yǔ)句可以執(zhí)行成功。

	當(dāng)其他事務(wù)插入一條 age = 22，id = 12 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 20、age = 39 的記錄，正好該記錄的二級(jí)索引上有間隙鎖，所以這條插入語(yǔ)句會(huì)被阻塞，無(wú)法插入成功。

	插入 age = 39 記錄的成功和失敗的情況分別如下：

	當(dāng)其他事務(wù)插入一條 age = 39，id = 3 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 20、age = 39 的記錄，正好該記錄的二級(jí)索引上有間隙鎖，所以這條插入語(yǔ)句會(huì)被阻塞，無(wú)法插入成功。

	當(dāng)其他事務(wù)插入一條 age = 39，id = 21 的記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條記錄不存在，也就沒(méi)有間隙鎖了，所以這條插入語(yǔ)句可以插入成功。

	所以，當(dāng)有一個(gè)事務(wù)持有二級(jí)索引的間隙鎖 (22, 39) 時(shí)，插入 age = 22 或者 age = 39 記錄的語(yǔ)句是否可以執(zhí)行成功，關(guān)鍵還要考慮插入記錄的主鍵值，因?yàn)椤付?jí)索引值（age列）+主鍵值（id列）」才可以確定插入的位置，確定了插入位置后，就要看插入的位置的下一條記錄是否有間隙鎖，如果有間隙鎖，就會(huì)發(fā)生阻塞，如果沒(méi)有間隙鎖，則可以插入成功。

	知道了這個(gè)結(jié)論之后，我們?cè)倩剡^(guò)頭看，非唯一索引等值查詢(xún)時(shí)，查詢(xún)的記錄不存在時(shí)，執(zhí)行select * from performance_schema.data_locksG; 輸出的結(jié)果。

	

	在前面分析輸出結(jié)果的時(shí)候，我說(shuō)的結(jié)論是：「事務(wù) A 在 age = 39 記錄的二級(jí)索引上（INDEX_NAME: index_age ），加了范圍為 (22, 39) 的 X 型間隙鎖」。這個(gè)結(jié)論其實(shí)還不夠準(zhǔn)確，因?yàn)橹豢紤]了 LOCK_DATA 第一個(gè)數(shù)值（39），沒(méi)有考慮 LOCK_DATA 第二個(gè)數(shù)值（20）。

	那 LOCK_DATA：39，20 是什么意思？

	LOCK_DATA 第一個(gè)數(shù)值，也就是 39， 它代表的是 age 值。從前面我們也知道了，LOCK_DATA 第一個(gè)數(shù)值是 next-key 鎖和間隙鎖鎖住的范圍的右邊界值。

	LOCK_DATA 第二個(gè)數(shù)值，也就是 20， 它代表的是 id 值。

	之所以 LOCK_DATA 要多顯示一個(gè)數(shù)值（ID值），是因?yàn)獒槍?duì)「當(dāng)某個(gè)事務(wù)持有非唯一索引的 (22, 39) 間隙鎖的時(shí)候，其他事務(wù)是否可以插入 age = 39 新記錄」的問(wèn)題，還需要考慮插入記錄的 id 值。而 LOCK_DATA 的第二個(gè)數(shù)值，就是說(shuō)明在插入 age = 39 新記錄時(shí)，哪些范圍的 id 值是不可以插入的。

	因此， LOCK_DATA：39，20 + LOCK_MODE : X, GAP 的意思是，事務(wù) A 在 age = 39 記錄的二級(jí)索引上（INDEX_NAME: index_age ），加了 age 值范圍為 (22, 39) 的 X 型間隙鎖，**同時(shí)針對(duì)其他事務(wù)插入 age 值為 39 的新記錄時(shí)，不允許插入的新記錄的 id 值小于 20 **。如果插入的新記錄的 id 值大于 20，則可以插入成功。

	但是我們無(wú)法從select * from performance_schema.data_locksG; 輸出的結(jié)果分析出「在插入 age =22 新記錄時(shí)，哪些范圍的 id 值是可以插入成功的」，這時(shí)候就得自己畫(huà)出二級(jí)索引的 B+ 樹(shù)的結(jié)構(gòu)，然后確定插入位置后，看下該位置的下一條記錄是否存在間隙鎖，如果存在間隙鎖，則無(wú)法插入成功，如果不存在間隙鎖，則可以插入成功。

	2、記錄存在的情況

	實(shí)驗(yàn)二：針對(duì)非唯一索引等值查詢(xún)時(shí)，查詢(xún)的值存在的情況。

	假設(shè)事務(wù) A 對(duì)非唯一索引（age）進(jìn)行了等值查詢(xún)，且表中存在 age = 22 的記錄。

	
mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage=22forupdate;
+----+--------+-----+
|id|name|age|
+----+--------+-----+
|10|山治|22|
+----+--------+-----+
1rowinset(0.00sec)


	

	事務(wù) A 加鎖變化過(guò)程如下：

	由于不是唯一索引，所以肯定存在值相同的記錄，于是非唯一索引等值查詢(xún)的過(guò)程是一個(gè)掃描的過(guò)程，最開(kāi)始要找的第一行是 age = 22，于是對(duì)該二級(jí)索引記錄加上范圍為 (21, 22] 的 next-key 鎖。同時(shí)，因?yàn)?age = 22 符合查詢(xún)條件，于是對(duì) age = 22 的記錄的主鍵索引加上記錄鎖，即對(duì) id = 10 這一行加記錄鎖。

	接著繼續(xù)掃描，掃描到的第二行是 age = 39，該記錄是第一個(gè)不符合條件的二級(jí)索引記錄，所以該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖，范圍是 (22, 39)。

	停止查詢(xún)。

	可以看到，事務(wù) A 對(duì)主鍵索引和二級(jí)索引都加了 X 型的鎖：

	

	主鍵索引：

	在 id = 10 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 10 的這一行記錄。

	二級(jí)索引（非唯一索引）：

	在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的這一些新記錄，不過(guò)對(duì)于插入 age = 20 和 age = 21 新記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入，具體哪些情況，會(huì)在后面說(shuō)。

	在 age = 39 這條記錄的二級(jí)索引上，加了范圍 (22, 39) 的間隙鎖。意味著其他事務(wù)無(wú)法插入 age 值為 23、24、..... 、38 的這一些新記錄。不過(guò)對(duì)于插入 age = 22 和 age = 39 記錄的語(yǔ)句，在一些情況是可以成功插入的，而一些情況則無(wú)法成功插入，具體哪些情況，會(huì)在后面說(shuō)。

	我們也可以通過(guò) select * from performance_schema.data_locksG; 這條語(yǔ)句來(lái)看看事務(wù) A 加了什么鎖。

	輸出結(jié)果如下，我這里只截取了行級(jí)鎖的內(nèi)容。

	

	從上圖的分析，可以看到，事務(wù) A 對(duì)二級(jí)索引（INDEX_NAME: index_age ）加了兩個(gè) X 型鎖，分別是：

	在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的這一些新記錄，針對(duì)是否可以插入 age = 21 和 age = 22 的新記錄，分析如下：

	是否可以插入 age = 21 的新記錄，還要看插入的新記錄的 id 值，如果插入 age = 21 新記錄的 id 值小于 5，那么就可以插入成功，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 5，age = 21 的記錄，該記錄的二級(jí)索引上沒(méi)有間隙鎖。如果插入 age = 21 新記錄的 id 值大于 5，那么就無(wú)法插入成功，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 20，age = 39 的記錄，該記錄的二級(jí)索引上有間隙鎖。

	是否可以插入 age = 22 的新記錄，還要看插入的新記錄的 id 值，從 LOCK_DATA : 22, 10 可以得知，其他事務(wù)插入 age 值為 22 的新記錄時(shí)，如果插入的新記錄的 id 值小于 10，那么插入語(yǔ)句會(huì)發(fā)生阻塞；如果插入的新記錄的 id 大于 10，還要看該新記錄插入的位置的下一條記錄是否有間隙鎖，如果沒(méi)有間隙鎖則可以插入成功，如果有間隙鎖，則無(wú)法插入成功。

	在 age = 39 這條記錄的二級(jí)索引上，加了范圍 (22, 39) 的間隙鎖。意味著其他事務(wù)無(wú)法插入 age 值為 23、24、..... 、38 的這一些新記錄，針對(duì)是否可以插入 age = 22 和 age = 39 的新記錄，分析如下：

	是否可以插入 age = 22 的新記錄，還要看插入的新記錄的 id 值，如果插入 age = 22 新記錄的 id 值小于 10，那么插入語(yǔ)句會(huì)被阻塞，無(wú)法插入，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 10，age = 22 的記錄，該記錄的二級(jí)索引上有間隙鎖（ age = 22 這條記錄的二級(jí)索引上有 next-key 鎖）。如果插入 age = 21 新記錄的 id 值大于 10，也無(wú)法插入，因?yàn)榇藭r(shí)插入的位置的下一條記錄是 id = 20，age = 39 的記錄，該記錄的二級(jí)索引上有間隙鎖。

	是否可以插入 age = 39 的新記錄，還要看插入的新記錄的 id 值，從 LOCK_DATA : 39, 20 可以得知，其他事務(wù)插入 age 值為 39 的新記錄時(shí)，如果插入的新記錄的 id 值小于 20，那么插入語(yǔ)句會(huì)發(fā)生阻塞，如果插入的新記錄的 id 大于 20，則可以插入成功。

	同時(shí)，事務(wù) A 還對(duì)主鍵索引（INDEX_NAME: PRIMARY ）加了X 型的記錄鎖：

	在 id = 10 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 10 的這一行記錄。

	為什么這個(gè)實(shí)驗(yàn)案例中，需要在二級(jí)索引索引上加范圍 (22, 39) 的間隙鎖？

	要找到這個(gè)問(wèn)題的答案，我們要明白 MySQL 在可重復(fù)讀的隔離級(jí)別場(chǎng)景下，為什么要引入間隙鎖？其實(shí)是為了避免幻讀現(xiàn)象的發(fā)生。

	如果這個(gè)實(shí)驗(yàn)案例中：

	
select*fromuserwhereage=22forupdate;


	

	如果事務(wù) A 不在二級(jí)索引索引上加范圍 (22, 39) 的間隙鎖，只在二級(jí)索引索引上加范圍為 (21, 22] 的 next-key 鎖的話，那么就會(huì)有幻讀的問(wèn)題。

	前面我也說(shuō)過(guò)，在非唯一索引上加了范圍為 (21, 22] 的 next-key 鎖，是無(wú)法完全鎖住 age = 22 新記錄的插入，因?yàn)閷?duì)于是否可以插入 age = 22 的新記錄，還要看插入的新記錄的 id 值，從 LOCK_DATA : 22, 10 可以得知，其他事務(wù)插入 age 值為 22 的新記錄時(shí)，如果插入的新記錄的 id 值小于 10，那么插入語(yǔ)句會(huì)發(fā)生阻塞，如果插入的新記錄的 id 值大于 10，則可以插入成功。

	也就是說(shuō)，只在二級(jí)索引索引（非唯一索引）上加范圍為 (21, 22] 的 next-key 鎖，其他事務(wù)是有可能插入 age 值為 22 的新記錄的（比如插入一個(gè) age = 22，id = 12 的新記錄），那么如果事務(wù) A 再一次查詢(xún) age = 22 的記錄的時(shí)候，前后兩次查詢(xún) age = 22 的結(jié)果集就不一樣了，這時(shí)就發(fā)生了幻讀的現(xiàn)象。

	那么當(dāng)在 age = 39 這條記錄的二級(jí)索引索引上加了范圍為 (22, 39) 的間隙鎖后，其他事務(wù)是無(wú)法插入一個(gè) age = 22，id = 12 的新記錄，因?yàn)楫?dāng)其他事務(wù)插入一條 age = 22，id = 12 的新記錄的時(shí)候，在二級(jí)索引樹(shù)上定位到插入的位置，而該位置的下一條是 id = 20、age = 39 的記錄，正好該記錄的二級(jí)索引上有間隙鎖，所以這條插入語(yǔ)句會(huì)被阻塞，無(wú)法插入成功，這樣就避免幻讀現(xiàn)象的發(fā)生。

	所以，為了避免幻讀現(xiàn)象的發(fā)生，就需要在二級(jí)索引索引上加范圍 (22, 39) 的間隙鎖。

	非唯一索引范圍查詢(xún)

	非唯一索引和主鍵索引的范圍查詢(xún)的加鎖也有所不同，不同之處在于非唯一索引范圍查詢(xún)，索引的 next-key lock 不會(huì)有退化為間隙鎖和記錄鎖的情況，也就是非唯一索引進(jìn)行范圍查詢(xún)時(shí)，對(duì)二級(jí)索引記錄加鎖都是加 next-key 鎖。

	就帶大家簡(jiǎn)單分析一下，事務(wù) A 的這條范圍查詢(xún)語(yǔ)句：

	
mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage>=22forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|10|山治|22|
|20|香克斯|39|
+----+-----------+-----+
2rowsinset(0.01sec)


	

	事務(wù) A 的加鎖變化：

	最開(kāi)始要找的第一行是 age = 22，雖然范圍查詢(xún)語(yǔ)句包含等值查詢(xún)，但是這里不是唯一索引范圍查詢(xún)，所以是不會(huì)發(fā)生退化鎖的現(xiàn)象，因此對(duì)該二級(jí)索引記錄加 next-key 鎖，范圍是 (21, 22]。同時(shí)，對(duì) age = 22 這條記錄的主鍵索引加記錄鎖，即對(duì) id = 10 這一行記錄的主鍵索引加記錄鎖。

	由于是范圍查詢(xún)，接著繼續(xù)掃描已經(jīng)存在的二級(jí)索引記錄。掃面的第二行是 age = 39 的二級(jí)索引記錄，于是對(duì)該二級(jí)索引記錄加 next-key 鎖，范圍是 (22, 39]，同時(shí)，對(duì) age = 39 這條記錄的主鍵索引加記錄鎖，即對(duì) id = 20 這一行記錄的主鍵索引加記錄鎖。

	雖然我們看見(jiàn)表中最后一條二級(jí)索引記錄是 age = 39 的記錄，但是實(shí)際在 Innodb 存儲(chǔ)引擎中，會(huì)用一個(gè)特殊的記錄來(lái)標(biāo)識(shí)最后一條記錄，該特殊的記錄的名字叫 supremum pseudo-record ，所以掃描第二行的時(shí)候，也就掃描到了這個(gè)特殊記錄的時(shí)候，會(huì)對(duì)該二級(jí)索引記錄加的是范圍為 (39, +∞] 的 next-key 鎖。

	停止查詢(xún)

	可以看到，事務(wù) A 對(duì)主鍵索引和二級(jí)索引都加了 X 型的鎖：

	

	主鍵索引（id 列）：

	在 id = 10 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 10 的這一行記錄。

	在 id = 20 這條記錄的主鍵索引上，加了記錄鎖，意味著其他事務(wù)無(wú)法更新或者刪除 id = 20 的這一行記錄。

	二級(jí)索引（age 列）：

	在 age = 22 這條記錄的二級(jí)索引上，加了范圍為 (21, 22] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 22 的這一些新記錄，不過(guò)對(duì)于是否可以插入 age = 21 和 age = 22 的新記錄，還需要看新記錄的 id 值，有些情況是可以成功插入的，而一些情況則無(wú)法插入，具體哪些情況，我們前面也講了。

	在 age = 39 這條記錄的二級(jí)索引上，加了范圍為 (22, 39] 的 next-key 鎖，意味著其他事務(wù)無(wú)法更新或者刪除 age = 39 的這一些記錄，也無(wú)法插入 age 值為 23、24、25、...、38 的這一些新記錄。不過(guò)對(duì)于是否可以插入 age = 22 和 age = 39 的新記錄，還需要看新記錄的 id 值，有些情況是可以成功插入的，而一些情況則無(wú)法插入，具體哪些情況，我們前面也講了。

	在特殊的記錄（supremum pseudo-record）的二級(jí)索引上，加了范圍為 (39, +∞] 的 next-key 鎖，意味著其他事務(wù)無(wú)法插入 age 值大于 39 的這些新記錄。

	在 age >= 22 的范圍查詢(xún)中，明明查詢(xún) age = 22 的記錄存在并且屬于等值查詢(xún)，為什么不會(huì)像唯一索引那樣，將 age = 22 記錄的二級(jí)索引上的 next-key 鎖退化為記錄鎖？

	因?yàn)?age 字段是非唯一索引，不具有唯一性，所以如果只加記錄鎖（記錄鎖無(wú)法防止插入，只能防止刪除或者修改），就會(huì)導(dǎo)致其他事務(wù)插入一條 age = 22 的記錄，這樣前后兩次查詢(xún)的結(jié)果集就不相同了，出現(xiàn)了幻讀現(xiàn)象。

	沒(méi)有加索引的查詢(xún)

	前面的案例，我們的查詢(xún)語(yǔ)句都有使用索引查詢(xún)，也就是查詢(xún)記錄的時(shí)候，是通過(guò)索引掃描的方式查詢(xún)的，然后對(duì)掃描出來(lái)的記錄進(jìn)行加鎖。

	如果鎖定讀查詢(xún)語(yǔ)句，沒(méi)有使用索引列作為查詢(xún)條件，或者查詢(xún)語(yǔ)句沒(méi)有走索引查詢(xún)，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會(huì)加 next-key 鎖，這樣就相當(dāng)于鎖住的全表，這時(shí)如果其他事務(wù)對(duì)該表進(jìn)行增、刪、改操作的時(shí)候，都會(huì)被阻塞。

	不只是鎖定讀查詢(xún)語(yǔ)句不加索引才會(huì)導(dǎo)致這種情況，update 和 delete 語(yǔ)句如果查詢(xún)條件不加索引，那么由于掃描的方式是全表掃描，于是就會(huì)對(duì)每一條記錄的索引上都會(huì)加 next-key 鎖，這樣就相當(dāng)于鎖住的全表。

	因此，在線上在執(zhí)行 update、delete、select ... for update 等具有加鎖性質(zhì)的語(yǔ)句，一定要檢查語(yǔ)句是否走了索引，如果是全表掃描的話，會(huì)對(duì)每一個(gè)索引加 next-key 鎖，相當(dāng)于把整個(gè)表鎖住了，這是挺嚴(yán)重的問(wèn)題。

	總結(jié)

	這次我以 MySQL 8.0.26 版本，在可重復(fù)讀隔離級(jí)別之下，做了幾個(gè)實(shí)驗(yàn)，讓大家了解了唯一索引和非唯一索引的行級(jí)鎖的加鎖規(guī)則。

	我這里總結(jié)下， MySQL 行級(jí)鎖的加鎖規(guī)則。

	唯一索引等值查詢(xún)：

	當(dāng)查詢(xún)的記錄是「存在」的，在索引樹(shù)上定位到這一條記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「記錄鎖」。

	當(dāng)查詢(xún)的記錄是「不存在」的，在索引樹(shù)找到第一條大于該查詢(xún)記錄的記錄后，將該記錄的索引中的 next-key lock 會(huì)退化成「間隙鎖」。

	非唯一索引等值查詢(xún)：

	當(dāng)查詢(xún)的記錄「存在」時(shí)，由于不是唯一索引，所以肯定存在索引值相同的記錄，于是非唯一索引等值查詢(xún)的過(guò)程是一個(gè)掃描的過(guò)程，直到掃描到第一個(gè)不符合條件的二級(jí)索引記錄就停止掃描，然后在掃描的過(guò)程中，對(duì)掃描到的二級(jí)索引記錄加的是 next-key 鎖，而對(duì)于第一個(gè)不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。同時(shí)，在符合查詢(xún)條件的記錄的主鍵索引上加記錄鎖。

	當(dāng)查詢(xún)的記錄「不存在」時(shí)，掃描到第一條不符合條件的二級(jí)索引記錄，該二級(jí)索引的 next-key 鎖會(huì)退化成間隙鎖。因?yàn)椴淮嬖跐M(mǎn)足查詢(xún)條件的記錄，所以不會(huì)對(duì)主鍵索引加鎖。

	非唯一索引和主鍵索引的范圍查詢(xún)的加鎖規(guī)則不同之處在于：

	唯一索引在滿(mǎn)足一些條件的時(shí)候，索引的 next-key lock 退化為間隙鎖或者記錄鎖。

	非唯一索引范圍查詢(xún)，索引的 next-key lock 不會(huì)退化為間隙鎖和記錄鎖。

	其實(shí)理解 MySQL 為什么要這樣加鎖，主要要以避免幻讀角度去分析，這樣就很容易理解這些加鎖的規(guī)則了。

	還有一件很重要的事情，在線上在執(zhí)行 update、delete、select ... for update 等具有加鎖性質(zhì)的語(yǔ)句，一定要檢查語(yǔ)句是否走了索引，如果是全表掃描的話，會(huì)對(duì)每一個(gè)索引加 next-key 鎖，相當(dāng)于把整個(gè)表鎖住了，這是挺嚴(yán)重的問(wèn)題。








	審核編輯：劉清