大白話解釋 MySQL InnoDB 行鎖機制

介紹

我在學習 MySQL 原理時，大部分内容看書、網上的文章都基本能了解，唯獨 InnoDB 的行鎖，加鎖規則看着很多，什麼從 Next-key Lock 退化成間隙鎖或者記錄鎖巴拉巴拉的，一旦陷入這些規則内容裡，很容易看着看着就暈了。

今天寫這篇文章的目的是為了分享加鎖規則背後的思路，不用特地記這些規則，隻需要畫出圖跟着這種思路就能想出加了哪些鎖。

那麼加鎖規則背後的思路是什麼呢？或者說這麼加鎖是為了達到什麼目的呢？

可以簡單用一句話總結：

為了避免幻讀的出現，并減少加鎖影響的範圍。

那麼什麼是幻讀呢？

一個事務内兩次同樣的查詢，結果不一樣。

比如我們在一個事務内，第一次查詢資料庫裡年齡大于 20 的人，傳回了兩條記錄，第二次查詢傳回不是兩條（可能大于兩條或者小于兩條），這就是幻讀。

為了容易了解，本文預設加的鎖都是 X 鎖（因為加 S 鎖的話隻能讀，不會出現幻讀的情況），隔離級别為可重複讀，MySQL 版本是 8.0.27。

資料準備

假設我們有如下的資料：

其中 id 是主鍵（唯一索引），age 是年齡（普通索引），name 是名稱（無索引）。

這個圖中的資料就是主鍵索引的排列圖，注意因為索引資料都是存在 MySQL 的頁（預設16 KB）裡的，每頁裡的記錄會組成一條連結清單，這個連結清單的頭尾固定是虛拟的節點。

如果不懂的話，建議先了解一下 MySQL InnoDB 的索引結構後再回來看。

age 的普通索引資料結構排列圖：

大家注意一下，這裡 name 行置灰了，因為 InnoDB 的非聚簇索引的葉子節點上是隻存主鍵的，這裡我為了了解友善畫了上去，實際上是不存在的。

前置知識

這裡簡單介紹一點這篇文章的前置知識。

資料删除

假設我們删除貝吉塔（id = 10）

就變成下面這樣：

資料插入

如果我們新增一列：

insert into lock_test(id, age, name) values(7, 20, '布瑪');
複制代碼           

首先會在主鍵索引增加一條記錄：

然後在其他索引位置添加記錄，在本文的資料例子中隻有一個 age 索引（根據索引排序，如果值重複則按照主鍵值排序）：

什麼時候會加行鎖

在 InnoDB 中有以下幾種情況會加行鎖：

• select ... for update
• 鎖定讀，會對符合條件的記錄加上行鎖，本文舉的例子都是鎖定讀。
• 普通 select 語句是快照讀，根據 MVCC 機制确定讀取的記錄版本内容。
• delete...
• 删除語句。
• update...
• 更新語句。

行鎖分類

Record Lock 記錄鎖

記錄鎖顧名思義，就是加在記錄上的鎖。如果一條記錄被加了記錄鎖，那麼有别的使用者想對同一條記錄加鎖的時候就會阻塞等待。

比如下圖對悟空這條記錄加上了鎖（本文中紅色代表記錄鎖），我們都知道，删除記錄前是需要獲得鎖的，加了記錄鎖，在釋放鎖前（一般是在事務送出時），這條記錄就不允許被删除。

Gap Lock 間隙鎖

間隙鎖時加在某一個記錄上的，會鎖住這條記錄跟前一條記錄的間隙，在持有間隙鎖的期間，不允許在這個間隙中插入新紀錄。

下圖的例子就是給悟空加上了間隙鎖，鎖住了悟空跟前一條記錄的間隙。

如果這時插入 id < 0 的新紀錄，會阻塞等待直到間隙鎖釋放後才會插入成功。

也就是說在持有間隙鎖時，不會有新紀錄插入這個間隙。

Next-key Lock

Next-key Lock = 記錄鎖 + 間隙鎖，下圖就是給悟空這條記錄加上 Next-key Lock：

結合上面兩種鎖的效果分析，在鎖釋放前我們可以得出以下結論：

• 悟空這個節點不能被編輯和删除
• 悟空和前一個節點的間隙不允許新增記錄

分析加鎖思路

我們分析加鎖的思路：

為了避免幻讀的出現，并減少加鎖影響的範圍。

可以分析出目的有兩個：

1. 避免出現幻讀
2. 把加鎖影響的範圍降到最低

我們要牢記這個兩個目的。

加鎖影響的範圍降到最低的意思是避免幻讀時，使用加鎖範圍最小的鎖

就是我們要靈活運用上面提到的三種鎖：

• Record Lock
• Gap Lock
• Next-key Lock

因為唯一索引和普通索引的差別是能否有重複值，這個差別所對應的加鎖分析思路會不一樣，是以我們分開一個個讨論。

唯一索引

讓我們先來分析一下唯一索引的情況，唯一索引的特點就是索引值沒有重複的。

資料如圖：

查詢分兩種等值查詢和範圍查詢。

等值查詢

我們執行以下語句：

begin;
select * from lock_test where id = 0 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
|  0 |   15 | 悟空 |
+----+------+--------+
1 row in set (0.00 sec)
複制代碼           

第一次查詢後，傳回一條記錄，我們看着上面那幅圖，想一件事：

執行了這條 SQL 後，在事務送出前，如何避免幻讀？

隻要我們第二次查詢，傳回的還是一條記錄，這就避免了幻讀。那麼我們隻需要把 id = 0 這條記錄鎖起來，讓别人删除不了就行（因為删除記錄也是要加鎖的）。

因為是唯一索引，是以不用擔心别的使用者再插入了一條 id 為 0 的記錄導緻出現幻讀的情況。

效果圖如下：

剛剛那是我們加鎖的資料正好存在資料庫裡的情況，如果不存在呢？

begin;
select * from lock_test where id = 3 for update;

Empty set (0.01 sec)
複制代碼           

第一次查詢是 0 條記錄，第二次查詢為了避免幻讀是以也得是 0 條。

為了達到這兩個目的：

避免出現幻讀 把加鎖影響的範圍降到最低

你可以看着資料圖先自己思考下如何達到達到目的，然後再看下面内容。

我們隻需要給比克加上間隙鎖，禁止插入資料就能避免幻讀。

這些就是唯一索引等值查詢的加鎖規則，我覺得隻要記住兩個目的，還是很容易能分析出來加了什麼鎖的。

避免出現幻讀 把加鎖影響的範圍降到最低

順便附上網上文章寫的的加鎖規則，現在看是不是比較清晰明白了呢？

當查詢的記錄是「存在」的，在索引樹上定位到這一條記錄後，将該記錄的索引中的 next-key lock 會退化成「記錄鎖」。 當查詢的記錄是「不存在」的，在索引樹找到第一條大于該查詢記錄的記錄後，将該記錄的索引中的 next-key lock 會退化成「間隙鎖」。

範圍查詢

範圍查詢有大于、小于兩種情況，對于分析思路來說是一樣的。

小于

我們先來看看小于的情況。

begin;
select * from lock_test where id < 5 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
|  0 |   15 | 悟空 |
+----+------+--------+
1 row in set (0.00 sec)
複制代碼           

你可以看着資料圖，想着兩個目的，自己思考下怎麼加鎖，然後再往下看。

避免出現幻讀 把加鎖影響的範圍降到最低

第一次查詢會傳回一條記錄（悟空），要保證下次查詢還是隻傳回這條記錄，是以得這麼做：

1. 保證這條記錄不被删除，悟空這條記錄加上記錄鎖
2. 保證隻傳回一條記錄，這條記錄的兩側不能插入資料，悟空和比克加上間隙鎖

小于等于

看小于等于的情況。

begin;
select * from lock_test where id <= 5 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
|  0 |   15 | 悟空 |
|  5 |   20 | 比克 |
+----+------+--------+
2 rows in set (0.00 sec)
複制代碼           

第一次查詢會傳回悟空和比克兩條記錄。

如何保證下次查詢還是隻有這兩條呢？

• 這兩條記錄不能被删掉，兩條記錄加記錄鎖
• 不能新增 id <= 5 的記錄，悟空和比克加間隙鎖
• 以上兩條結合起來，就是給悟空和比克加上 Next-key Lock
• 為什麼貝吉塔不用加間隙鎖？因為是唯一索引，不可能再插入一條 id = 5 的記錄，是以不用

大于

接着看大于的例子，其實原理跟上面是差不多的。

begin;
select * from lock_test where id > 20 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 25 |   31 | 餃子 |
| 30 |   35 | 沙魯 |
+----+------+--------+
2 rows in set (0.00 sec)
複制代碼           

第一次查詢會傳回餃子和沙魯兩條記錄。

如何保證下次查詢還是隻有這兩條呢？

• 這兩條記錄不能被删掉
• 不能新增 id > 20 的記錄

是以給 餃子、沙魯、最大虛拟節點 加上 Next-key Lock。

我通過 SQL 指令查詢鎖的狀态，最大虛拟節點就是加的 Next-key Lock，可以按照邏輯了解為虛拟節點不能删除。

大于等于

接着看大于等于的例子

begin;
select * from lock_test where id >= 20 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 20 |   17 | 克林 |
| 25 |   31 | 餃子 |
| 30 |   35 | 沙魯 |
+----+------+--------+
3 rows in set (0.00 sec)
複制代碼           

跟上面大于的例子相比，就是多鎖了條記錄的差别。

為什麼不用給克林加上間隙鎖？因為是唯一索引，不會再插入一條 id = 20 的記錄。

普通索引

上面分析完了唯一索引的加鎖分析思路，現在我們接着來分析普通索引，在我們的資料例子中，普通索引就是 age。

其實原理都是一樣的，隻是普通索引因為允許重複的值，需要在唯一索引的思路上多考慮這個因素。

age 索引資料排列如下：

等值查詢

begin;
select * from lock_test where age = 20 for update;

+----+------+-----------+
| id | age  | name      |
+----+------+-----------+
|  5 |   20 | 比克    |
| 10 |   20 | 貝吉塔 |
+----+------+-----------+
2 rows in set (0.00 sec)
複制代碼           

可以看到傳回了兩條資料，那麼我們應該怎麼加鎖，使得索引資料下次查詢還是隻有這兩條呢？可以先自己想想。

在普通索引上，我們給比克和貝吉塔建立了 Next-key Lock，給餃子建立了間隙鎖。很符合直覺吧，這樣子就能保證再次查詢的時候，age 索引隻會有這兩條資料。

這裡說了隻保證了 age 索引，因為我們在 age 索引上沒有 name 字段，是以查詢的時候還需要回表到主鍵索引上取資料。是以主鍵索引上也要鎖住這兩條資料，防止這兩條資料被删除。

剛剛的等值查詢是資料存在的情況，那麼資料如果不存在呢？

begin;
select * from lock_test where age = 19 for update;

Empty set (0.00 sec)
複制代碼           

隻要防止事務送出之前（事務送出的時候鎖也會釋放）插入 age = 19 的記錄就可以了。

隻需要在比克這條記錄加上間隙鎖：

範圍查詢

範圍搜尋還是分為大于小于的情況。

小于

begin;
select * from lock_test where age < 17 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 15 |    7 | 悟飯 |
|  0 |   15 | 悟空 |
+----+------+--------+
2 rows in set (0.00 sec)
複制代碼           

額，按照我的分析，普通索引應該是這樣加鎖才對：

主鍵索引是這麼加鎖的：

可是在我查了加鎖情況後，主鍵索引和我們預想的一樣，但是普通索引有點不同，MySQL 是這樣子加鎖的：

這就引出了一個特殊的地方，在普通索引範圍查找的時候，是會查找到第一條不符合條件的記錄，并且加上 Next-key Lock。

在上面的例子中要查找的是 age < 17 的記錄，那麼第一條不符合條件的記錄就是 age = 17 的記錄。

這裡就當作個特殊點記住吧。

小于等于

按照上面發現的特殊的地方，我們來分析這個例子：

在普通索引範圍查找的時候，是會查找到第一條不符合條件的記錄，并且加上 Next-key Lock。

資料存在的情況：

begin;
select * from lock_test where age <= 17 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 15 |    7 | 悟飯 |
|  0 |   15 | 悟空 |
| 20 |   17 | 克林 |
+----+------+--------+
3 rows in set (0.00 sec)
複制代碼           

資料不存在的情況：

begin;
select * from lock_test where age <= 19 for update;


+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 15 |    7 | 悟飯 |
|  0 |   15 | 悟空 |
| 20 |   17 | 克林 |
+----+------+--------+
3 rows in set (0.00 sec)
複制代碼           

加鎖的情況都是一樣的。

普通索引加鎖：

不管是查找 age <= 17 或者 age <=19，第一條不符合條件的記錄都是比克（age = 20）。

主鍵索引加鎖：

大于

後面的例子跟上面小于等于都是一樣的思路，就不贅述了。

begin;
select * from lock_test where age > 20 for update;

+----+------+--------+
| id | age  | name   |
+----+------+--------+
| 25 |   31 | 餃子 |
| 30 |   35 | 沙魯 |
+----+------+--------+
2 rows in set (0.00 sec)
複制代碼           

普通索引索引：

主鍵索引：

大于等于

begin;
select * from lock_test where age >= 20 for update;

+----+------+-----------+
| id | age  | name      |
+----+------+-----------+
|  5 |   20 | 比克    |
| 10 |   20 | 貝吉塔 |
| 25 |   31 | 餃子    |
| 30 |   35 | 沙魯    |
+----+------+-----------+
4 rows in set (0.00 sec)
複制代碼           

普通索引：

主鍵索引：

總結

從上面的分析可以看出來，在唯一索引的等值查找、範圍查找和普通索引的等值查找，我們隻需要牢記兩個目的，看着圖就能很容易的想到加鎖的方法。

避免出現幻讀 把加鎖影響的範圍降到最低

唯一有點特殊的是普通索引的範圍查找，似乎違背了第二條原則：把加鎖影響的範圍降到最低。

按理說，隻要将第一條不符合條件的記錄加上間隙鎖，既可以避免幻讀，也可以降加鎖的影響範圍降到最低。至于為什麼要給第一條不符合條件的記錄（克林）加上記錄鎖，我還不清楚，如果有大神懂這個可以在評論區解釋下（ORZ）。