大家好，我是小林。在前一篇文章我講了下 MySQL 的全局鎖、表記鎖和行級(jí)別鎖，其中行級(jí)鎖只提了概念，并沒(méi)有具體說(shuō)。因?yàn)樾屑?jí)鎖加鎖規(guī)則比較復(fù)雜，不同的場(chǎng)景，加鎖的形式還不同，所以這次就來(lái)好好介紹下行級(jí)鎖。對(duì)記錄加鎖時(shí)，加鎖的基本單位是 next-key lock，它是由記錄鎖和間隙鎖組合而成的，next-key lock 是前開(kāi)后閉區(qū)間，而間隙鎖是前開(kāi)后開(kāi)區(qū)間。但是，next-key lock 在一些場(chǎng)景下會(huì)退化成記錄鎖或間隙鎖。那到底是什么場(chǎng)景呢？今天，我們就以下面這個(gè)表來(lái)進(jìn)行實(shí)驗(yàn)說(shuō)明。其中，id 是主鍵索引（唯一索引），b 是普通索引（非唯一索引），a 是普通的列。注意，我的 MySQL 的版本是 8.0.26，不同版本的加鎖規(guī)則可能是不同的。

唯一索引等值查詢

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

• 當(dāng)查詢的記錄是存在的，在用「唯一索引進(jìn)行等值查詢」時(shí)，next-key lock 會(huì)退化成「記錄鎖」。

• 當(dāng)查詢的記錄是不存在的，在用「唯一索引進(jìn)行等值查詢」時(shí)，next-key lock 會(huì)退化成「間隙鎖」。

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

先看看記錄是存在的。

來(lái)看下面這個(gè)例子：

會(huì)話1加鎖變化過(guò)程如下：

1. 加鎖的基本單位是 next-key lock，因此會(huì)話1的加鎖范圍是(8, 16];

2. 但是由于是用唯一索引進(jìn)行等值查詢，且查詢的記錄存在，所以?next-key lock 退化成記錄鎖，因此最終加鎖的范圍是 id = 16 這一行。

所以，會(huì)話 2 在修改 id=16 的記錄時(shí)會(huì)被鎖住，而會(huì)話 3 插入 id=9 的記錄可以被正常執(zhí)行。

接下來(lái)，看看記錄不存在的情況

來(lái)看看，下面這個(gè)例子：

會(huì)話1加鎖變化過(guò)程如下：

1. 加鎖的基本單位是 next-key lock，因此主鍵索引 id 的加鎖范圍是(8, 16];

2. 但是由于查詢記錄不存在，next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (8,16)。

所以，會(huì)話 2 要往這個(gè)間隙里面插入 id=9 的記錄會(huì)被鎖住，但是會(huì)話 3 修改 id =16 是可以正常執(zhí)行的，因?yàn)?id = 16 這條記錄并沒(méi)有加鎖。

唯一索引范圍查詢

范圍查詢和等值查詢的加鎖規(guī)則是不同的。舉個(gè)例子，下面這兩條查詢語(yǔ)句，查詢的結(jié)果雖然是一樣的，但是加鎖的范圍是不一樣的。select?*?from?t_test?where?id=8?for?update;
select?*?from?t_test?where?id>=8?and?id<9?for?update;
做個(gè)實(shí)驗(yàn)就知道了。

會(huì)話 1 加鎖變化過(guò)程如下：

1. 最開(kāi)始要找的第一行是 id = 8，因此 next-key lock(4,8]，但是由于 id 是唯一索引，且該記錄是存在的，因此會(huì)退化成記錄鎖，也就是只會(huì)對(duì) id = 8 這一行加鎖；

2. 由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，也就是會(huì)找到 id = 16 這一行停下來(lái)，然后加 next-key lock (8, 16]，但由于 id = 16 不滿足 id < 9，所以會(huì)退化成間隙鎖，加鎖范圍變?yōu)?(8, 16)。

所以，會(huì)話 1 這時(shí)候主鍵索引的鎖是記錄鎖 id=8 和間隙鎖(8, 16)。會(huì)話 2 由于往間隙鎖里插入了 id = 9 的記錄，所以會(huì)被鎖住了，而 id = 8 是被加鎖的，因此會(huì)話 3 的語(yǔ)句也會(huì)被阻塞。由于 id = 16 并沒(méi)有加鎖，所以會(huì)話 4 是可以正常被執(zhí)行。

非唯一索引等值查詢

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

• 當(dāng)查詢的記錄存在時(shí)，除了會(huì)加 next-key lock 外，還額外加間隙鎖，也就是會(huì)加兩把鎖。

• 當(dāng)查詢的記錄不存在時(shí)，只會(huì)加 next-key lock，然后會(huì)退化為間隙鎖，也就是只會(huì)加一把鎖。

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

我們先來(lái)看看查詢的值存在的情況。

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

會(huì)話 1 加鎖變化過(guò)程如下：

1. 先會(huì)對(duì)普通索引 b 加上 next-key lock，范圍是(4,8];

2. 然后因?yàn)槭欠俏ㄒ凰饕?，且查詢的記錄是存在的，所以還會(huì)加上間隙鎖，規(guī)則是向下遍歷到第一個(gè)不符合條件的值才能停止，因此間隙鎖的范圍是(8,16)。

所以，會(huì)話1的普通索引 b 上共有兩個(gè)鎖，分別是 next-key lock (4,8] 和間隙鎖 (8,16) 。那么，當(dāng)會(huì)話 2 往間隙鎖里插入 id = 9 的記錄就會(huì)被鎖住，而會(huì)話 3 和會(huì)話 4 是因?yàn)楦牧?next-key lock 范圍里的記錄而被鎖住的。然后因?yàn)?b = 16 這條記錄沒(méi)有加鎖，所以會(huì)話 5 是可以正常執(zhí)行的。

接下來(lái)，我們看看查詢的值不存在的情況

直接看案例：

會(huì)話 1 加鎖變化過(guò)程如下：

1. 先會(huì)對(duì)普通索引 b 加上 next-key lock，范圍是(8,16];

2. 但是由于查詢的記錄是不存在的，所以不會(huì)再額外加個(gè)間隙鎖，但是 next-key lock 會(huì)退化為間隙鎖，最終加鎖范圍是 (8,16)。

會(huì)話 2 因?yàn)橥g隙鎖里插入了 b = 9 的記錄，所以會(huì)被鎖住，而 b = 16 是沒(méi)有被加鎖的，因此會(huì)話 3 的語(yǔ)句可以正常執(zhí)行。

非唯一索引范圍查詢

非唯一索引和主鍵索引的范圍查詢的加鎖也有所不同，不同之處在于普通索引范圍查詢，next-key lock 不會(huì)退化為間隙鎖和記錄鎖。來(lái)看下面這個(gè)案例：

會(huì)話 1 加鎖變化過(guò)程如下：

1. 最開(kāi)始要找的第一行是 b = 8，因此 next-key lock(4,8]，但是由于 b 不是唯一索引，并不會(huì)退化成記錄鎖。

2. 但是由于是范圍查找，就會(huì)繼續(xù)往后找存在的記錄，也就是會(huì)找到 b = 16 這一行停下來(lái)，然后加 next-key lock (8, 16]，因?yàn)槭瞧胀ㄋ饕樵儯圆⒉粫?huì)退化成間隙鎖。

所以，會(huì)話 1 的普通索引 b 有兩個(gè) next-key lock，分別是 (4,8] 和(8, 16]。這樣，你就明白為什么會(huì)話 2 、會(huì)話 3 、會(huì)話 4 的語(yǔ)句都會(huì)被鎖住了。

總結(jié)

這次我以?MySQL 8.0.26?版本做了幾個(gè)實(shí)驗(yàn)，讓大家了解了唯一索引和非唯一索引的行級(jí)鎖的加鎖規(guī)則。這里需要注意的是，不同的版本加鎖規(guī)則可能會(huì)有所不同。我這里總結(jié)下， 我這個(gè) MySQL 版本的行級(jí)鎖的加鎖規(guī)則。唯一索引等值查詢：

• 當(dāng)查詢的記錄是存在的，next-key lock 會(huì)退化成「記錄鎖」。

• 當(dāng)查詢的記錄是不存在的，next-key lock 會(huì)退化成「間隙鎖」。

非唯一索引等值查詢：

• 當(dāng)查詢的記錄存在時(shí)，除了會(huì)加 next-key lock 外，還額外加間隙鎖，也就是會(huì)加兩把鎖。

• 當(dāng)查詢的記錄不存在時(shí)，只會(huì)加 next-key lock，然后會(huì)退化為間隙鎖，也就是只會(huì)加一把鎖。

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

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

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

這些加鎖規(guī)則其實(shí)很好總結(jié)的，大家自己可以用我文中的案例測(cè)試一遍，看一下你的 MySQL 版本和我的 MySQL 版本的加鎖規(guī)則有什么不同。就說(shuō)到這啦， 我們下次見(jiàn)啦！