MySQL 啥時候用記錄鎖，啥時候用間隙鎖？

作者：陳樹義 2022-10-24 08:02:14

我們做了這么多個測試，雖然有 3 種索引類型（聚簇索引、唯一二級索引、普通二級索引）和 2 種匹配類型（精確匹配、范圍匹配），它們兩兩組合可以得出 6 種情況，再加上查詢的值是否存在，可能有更多的可能性。但是我們發現它們的結構都非常類似，基本上都跟查找的記錄是否存在，以及查找的記錄是否是唯一的相關。

大家好，我是樹哥。

在前面的文章「MySQL 不同隔離級別，都使用了什么鎖？」里，我們得出結論：在「讀未提交」和「讀已提交」隔離級別下，都只會使用記錄鎖，不會用間隙鎖和 Next-Key 鎖。而對于「可重復讀」隔離級別來說，會使用記錄鎖、間隙鎖和 Next-Key 鎖。

那么 MySQL 啥時候會用記錄鎖，啥時候會用間隙鎖，啥時候又會用 Next-Key 鎖呢？今天我們就來做一些測試，弄清楚這個問題。

文章思維導圖

影響因素

在開始之前，我們需要聲明的是：本文所有測試及結論的前提均是在「可重復讀」隔離級別下，以及 Innodb 存儲疫情下。

根據網上資料，我們大概可以知道，影響其使用哪種行級鎖的因素有：

索引類型（聚簇索引、唯一二級索引、普通二級索引）
匹配類型（精確匹配、唯一匹配、范圍匹配）
事務隔離級別
是否開啟 Innodb_locks_unsafe_for_binlog 系統變量
記錄是否被標記刪除
具體的執行語句類型（SELECT、INSERT、DELETE、UPDATE）

為了讓文章相對易懂一些，我準備重點測試索引類型與匹配類型兩個影響因素。對于其他的影響因素，我將不做改動。例如：事務隔離級別固定為「可重復讀」，Innodb_locks_unsafe_for_binlog 固定為 false。而第 5、6 點相對來說簡單一些，則我們會簡單帶過。

針對上面幾個影響因素，我們指定了幾個測試實驗，分別是：

聚簇索引 + 精確匹配
聚簇索引 + 范圍匹配
唯一二級索引 + 精確匹配
唯一二級索引 + 范圍匹配
普通二級索引 + 精確匹配
普通二級索引 + 范圍匹配

// 表結構
CREATE TABLE `test`.`price_test` (
  `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
  `price` INT(4) NULL,
  PRIMARY KEY (`id`));
// 表中數據
1, apple, 10
2, orange, 30
50, perl, 60

聚簇索引 + 精確匹配

為了測試「聚簇索引 + 精確匹配」下加鎖的類型，我們采用如下的測試方法。

事務 A 執行下面命令：

begin;
select * from price_test where id = 2 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，其是對 id 為 2 的索引加了一個記錄鎖。

此時事務 B 執行下面命令：

beign;
update price_test set price = 25 where id = 2;

執行之后，我們會發現事務 B 阻塞住了。

那如果聚簇索引的值找不到對應的記錄呢，將會是一個什么樣的結果呢？

我們再來測試一下，開始之前記得將事務 A 和 B 回滾恢復。

事務 A 執行下面命令，其中 id 為 5 的記錄是不存在的：

begin;
select * from price_test where id = 5 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，其加了一個間隙鎖，該間隙鎖應該是 (2, 50) 這個范圍。

我們可以通過在事務 B 執行如下命令來測試下間隙鎖的范圍。

beign;
// 執行下面任何一個命令，可以通過
update price_test set price = 25 where id = 2;
update price_test set price = 25 where id = 50;
// 執行下面任何一個命令，都將阻塞
insert into price_test(id,name,price) values(3,"test",25);
insert into price_test(id,name,price) values(5,"test",25);
insert into price_test(id,name,price) values(49,"test",25);

由此我們可以得出結論：「聚簇索引 + 精確匹配」，如果能夠定位到唯一一條存在的記錄，那么其會使用記錄鎖。如果該記錄不存在，那么則會使用間隙鎖。

聚簇索引 + 范圍匹配

事務 A 執行下面命令：

begin;
select * from price_test where id >= 2 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，事務 A 一共加了 3 個鎖，其中 1 個記錄鎖，2 個 Next-Key 鎖。其中 1 個記錄鎖是對 id 為 2 的索引加的鎖，Next-Key 鎖是對 (2, 50] 和 (50, 正無窮) 這兩個區間加的鎖。

在事務 B 執行下面命令可以驗證間隙鎖的加鎖區間：

beign;
// 執行下面任意一條語句，都會阻塞
update price_test set price = 25 where id = 2;
update price_test set price = 25 where id = 50;
insert into price_test(id,name,price) values(5,"test",25);
insert into price_test(id,name,price) values(60,"test",25);

這里我們思考一下，如果范圍匹配的值并不存在，那么會是什么情況呢？

即事務 A 執行如下語句，其中 id 為 5 的記錄是不存在的。

begin;
select * from price_test where id >= 5 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，其實加了 2 個 Next-Key 鎖，鎖的范圍應該是 (2, 50）和 [50, + 無窮)。

此時事務 B 執行下面命令，應該都會阻塞。

beign;
// 執行下面任意一條語句，都會阻塞
update price_test set price = 25 where id = 50;
insert into price_test(id,name,price) values(5,"test",25);
insert into price_test(id,name,price) values(45,"test",25);
insert into price_test(id,name,price) values(60,"test",25);

由此我們可以得出結論：「聚簇索引 + 范圍匹配」，會使用「記錄鎖 + 間隙鎖 + Next-Key 鎖」。

唯一二級索引 + 精確匹配

事務 A 執行下面命令：

begin;
select * from price_test where price = 10 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，其加的行級鎖是 2 個記錄鎖，應該是 price = 10 這條索引記錄的鎖。

此時，如果在事務 B 執行下面命令：

beign;
// 執行下面任意一條語句，都會阻塞
update price_test set name = 'test-name' where price = 10;

執行之后，我們會發現事務 B 阻塞住了。

由此我們可以得出結論：唯一二級索引與聚簇索引非常類似，都只有一個唯一值，都是使用記錄鎖。

唯一二級索引 + 范圍匹配

事務 A 執行下面命令：

begin;
select * from price_test where price >= 30 for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，事務 A 一共有 5 個行鎖，其中 3 個 Next-Key 鎖， 2 個記錄鎖。大致可以猜測出兩個記錄鎖分別是 price 為 30 和 60 的記錄鎖。3 個 Next-Key 鎖則是 (10, 30)、(30,60)、(60, 正無窮）三個范圍。

為了驗證我們上面的結論，我們在事務 B 執行下面命令，每條 SQL 都會阻塞住：

beign;
// 執行下面任意一條語句，都會阻塞
update price_test set name = 'price30' where price = 30;
update price_test set name = 'price60' where price = 60;
insert into price_test(id,name,price) values(5,"test", 20);
insert into price_test(id,name,price) values(5,"test", 40);
insert into price_test(id,name,price) values(5,"test", 70);

執行之后，我們會發現事務 B 阻塞住了。

由此我們可以得出結論：「唯一二級索引 + 范圍匹配」，會使用「記錄鎖 + 間隙鎖 + Next-Key 鎖」。

普通二級索引 + 精確匹配

事務 A 執行下面命令：

begin;
select * from price_test where name = 'apple' for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

可以看到，其不僅有一個記錄鎖，還有一個間隙鎖。這里可以猜測記錄鎖是 apple 索引的記錄鎖，而間隙鎖則是 (負無窮，orange) 的間隙鎖。

我們可在事務 B 執行如下命令驗證一下：

begin;
// 執行下面任意一條語句，都會阻塞
update price_test set name = 'apple-new' where name = 'apple';
insert into price_test(id,name,price) values(5,"aa", 20);
insert into price_test(id,name,price) values(5,"ha", 20);
// 執行下面的語句正常執行
update price_test set name = 'orange-new' where name = 'orange';
insert into price_test(id,name,price) values(5,"orb", 20);

之所以二級索引的精確匹配會有間隙鎖，是因為二級索引可能匹配到多個。因此當匹配到一個的時候，會繼續往后匹配，直到匹配到一個不符合的記錄，隨后就會以該不符合的記錄（這里是 orange）作為值做一個間隙鎖。

由此我們可以得出結論：「普通二級索引 + 精確匹配」，會使用「記錄鎖 + 間隙鎖 + Next-Key 鎖」。

普通二級索引 + 范圍匹配

事務 A 執行下面命令：

begin;
select * from price_test where name >= 'orange' for update;

執行 show engine innodb status\G; 查看鎖信息如下圖所示。

從上圖可以看到起一共有 2 個記錄鎖，3 個 Next-Key 鎖。其中 2 個記錄鎖應該是 orange 和 perl 兩個記錄，3 個 Next-Key 鎖，應該是 (apple, orange]、[orange, perl)、[perl, 正無窮)。

我們可在事務 B 執行如下命令驗證一下：

begin;
// 執行下面任意一條語句，都會阻塞
// 驗證記錄鎖
update price_test set price = 1 where name = 'orange';
update price_test set price = 1 where name = 'perl';
// 驗證間隙鎖
insert into price_test(id,name,price) values(5,"ba", 20);
insert into price_test(id,name,price) values(5,"orb", 20);
insert into price_test(id,name,price) values(5,"pes", 20);
// 執行下面的語句正常執行
update price_test set price = 1 where name = 'apple';
insert into price_test(id,name,price) values(5,"aa", 20);

可以看到「普通二級索引 + 范圍匹配」與「普通二級索引 + 精確匹配」結果是類似的。

我們可以得出結論：「普通二級索引 + 范圍匹配」，會使用「記錄鎖 + 間隙鎖 + Next-Key 鎖」。

總結

由此，我們大致可以得出結論：

如果查找的記錄是唯一且存在的，那么只會使用記錄鎖，而不會使用間隙鎖或 Next-Key 鎖。
如果查找的記錄不唯一或者不存在，那么就會使用 Next-Key 鎖和間隙鎖。

通過這次測試，我們大概知道了加鎖的一些原則，但實際上 Innodb 的關于加鎖的源碼還是比較復雜的。有一篇文章講得還是比較好的，本文可以說是做了一些簡化，有興趣的朋友可以自行閱讀看看：完整版：Innodb 到底是怎么加鎖的。

參考資料

完整版：Innodb 到底是怎么加鎖的
【鎖】MySQL 間隙鎖 - 阿里云開發者社區
MySQL next-key lock 加鎖范圍是什么？- SegmentFault 思否

責任編輯：武曉燕來源：樹哥聊編程

MySQL 索引類型