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

唯一索引等值查詢

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

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

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

接下里用兩個案例來說明。

先看看記錄是存在的。

來看下面這個例子：

會話1加鎖變化過程如下：

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

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

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

接下來，看看記錄不存在的情況

來看看，下面這個例子：

會話1加鎖變化過程如下：

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

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

所以，會話 2 要往這個間隙里面插入 id=9 的記錄會被鎖住，但是會話 3 修改 id =16 是可以正常執(zhí)行的，因為 id = 16 這條記錄并沒有加鎖。

唯一索引范圍查詢

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

會話 1 加鎖變化過程如下：

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

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

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

非唯一索引等值查詢

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

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

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

接下里用兩個案例來說明。

我們先來看看查詢的值存在的情況。

比如下面這個例子：

會話 1 加鎖變化過程如下：

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

2. 然后因為是非唯一索引，且查詢的記錄是存在的，所以還會加上間隙鎖，規(guī)則是向下遍歷到第一個不符合條件的值才能停止，因此間隙鎖的范圍是(8,16)。

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

接下來，我們看看查詢的值不存在的情況

直接看案例：

會話 1 加鎖變化過程如下：

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

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

會話 2 因為往間隙鎖里插入了 b = 9 的記錄，所以會被鎖住，而 b = 16 是沒有被加鎖的，因此會話 3 的語句可以正常執(zhí)行。

非唯一索引范圍查詢

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

會話 1 加鎖變化過程如下：

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

2. 但是由于是范圍查找，就會繼續(xù)往后找存在的記錄，也就是會找到 b = 16 這一行停下來，然后加 next-key lock (8, 16]，因為是普通索引查詢，所以并不會退化成間隙鎖。

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

總結(jié)

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

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

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

非唯一索引等值查詢：

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

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

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

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

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

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