大家好，我是樹哥。

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

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

文章思維導(dǎo)圖

影響因素

在開始之前，我們需要聲明的是：本文所有測(cè)試及結(jié)論的前提均是在「可重復(fù)讀」隔離級(jí)別下，以及 Innodb 存儲(chǔ)疫情下。

根據(jù)網(wǎng)上資料，我們大概可以知道，影響其使用哪種行級(jí)鎖的因素有：

• 索引類型（聚簇索引、唯一二級(jí)索引、普通二級(jí)索引）
• 匹配類型（精確匹配、唯一匹配、范圍匹配）
• 事務(wù)隔離級(jí)別
• 是否開啟 Innodb_locks_unsafe_for_binlog 系統(tǒng)變量
• 記錄是否被標(biāo)記刪除
• 具體的執(zhí)行語句類型（SELECT、INSERT、DELETE、UPDATE）

為了讓文章相對(duì)易懂一些，我準(zhǔn)備重點(diǎn)測(cè)試索引類型與匹配類型兩個(gè)影響因素。對(duì)于其他的影響因素，我將不做改動(dòng)。例如：事務(wù)隔離級(jí)別固定為「可重復(fù)讀」，Innodb_locks_unsafe_for_binlog 固定為 false。而第 5、6 點(diǎn)相對(duì)來說簡單一些，則我們會(huì)簡單帶過。

針對(duì)上面幾個(gè)影響因素，我們指定了幾個(gè)測(cè)試實(shí)驗(yàn)，分別是：

• 聚簇索引 + 精確匹配
• 聚簇索引 + 范圍匹配
• 唯一二級(jí)索引 + 精確匹配
• 唯一二級(jí)索引 + 范圍匹配
• 普通二級(jí)索引 + 精確匹配
• 普通二級(jí)索引 + 范圍匹配

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

聚簇索引 + 精確匹配

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

事務(wù) A 執(zhí)行下面命令：

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

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

可以看到，其是對(duì) id 為 2 的索引加了一個(gè)記錄鎖。

此時(shí)事務(wù) B 執(zhí)行下面命令：

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

執(zhí)行之后，我們會(huì)發(fā)現(xiàn)事務(wù) B 阻塞住了。

那如果聚簇索引的值找不到對(duì)應(yīng)的記錄呢，將會(huì)是一個(gè)什么樣的結(jié)果呢？

我們?cè)賮頊y(cè)試一下，開始之前記得將事務(wù) A 和 B 回滾恢復(fù)。

事務(wù) A 執(zhí)行下面命令，其中 id 為 5 的記錄是不存在的：

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

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

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

我們可以通過在事務(wù) B 執(zhí)行如下命令來測(cè)試下間隙鎖的范圍。

beign;
// 執(zhí)行下面任何一個(gè)命令，可以通過
update price_test set price = 25 where id = 2;
update price_test set price = 25 where id = 50;
// 執(zhí)行下面任何一個(gè)命令，都將阻塞
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);

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

聚簇索引 + 范圍匹配

事務(wù) A 執(zhí)行下面命令：

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

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

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

在事務(wù) B 執(zhí)行下面命令可以驗(yàn)證間隙鎖的加鎖區(qū)間：

beign;
// 執(zhí)行下面任意一條語句，都會(huì)阻塞
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);

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

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

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

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

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

此時(shí)事務(wù) B 執(zhí)行下面命令，應(yīng)該都會(huì)阻塞。

beign;
// 執(zhí)行下面任意一條語句，都會(huì)阻塞
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);

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

唯一二級(jí)索引 + 精確匹配

事務(wù) A 執(zhí)行下面命令：

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

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

可以看到，其加的行級(jí)鎖是 2 個(gè)記錄鎖，應(yīng)該是 price = 10 這條索引記錄的鎖。

此時(shí)，如果在事務(wù) B 執(zhí)行下面命令：

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

執(zhí)行之后，我們會(huì)發(fā)現(xiàn)事務(wù) B 阻塞住了。

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

唯一二級(jí)索引 + 范圍匹配

事務(wù) A 執(zhí)行下面命令：

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

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

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

為了驗(yàn)證我們上面的結(jié)論，我們?cè)谑聞?wù) B 執(zhí)行下面命令，每條 SQL 都會(huì)阻塞住：

beign;
// 執(zhí)行下面任意一條語句，都會(huì)阻塞
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);

執(zhí)行之后，我們會(huì)發(fā)現(xiàn)事務(wù) B 阻塞住了。

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

普通二級(jí)索引 + 精確匹配

事務(wù) A 執(zhí)行下面命令：

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

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

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

我們可在事務(wù) B 執(zhí)行如下命令驗(yàn)證一下：

begin;
// 執(zhí)行下面任意一條語句，都會(huì)阻塞
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);
// 執(zhí)行下面的語句正常執(zhí)行
update price_test set name = 'orange-new' where name = 'orange';
insert into price_test(id,name,price) values(5,"orb", 20);

之所以二級(jí)索引的精確匹配會(huì)有間隙鎖，是因?yàn)槎?jí)索引可能匹配到多個(gè)。因此當(dāng)匹配到一個(gè)的時(shí)候，會(huì)繼續(xù)往后匹配，直到匹配到一個(gè)不符合的記錄，隨后就會(huì)以該不符合的記錄（這里是 orange）作為值做一個(gè)間隙鎖。

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

普通二級(jí)索引 + 范圍匹配

事務(wù) A 執(zhí)行下面命令：

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

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

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

我們可在事務(wù) B 執(zhí)行如下命令驗(yàn)證一下：

begin;
// 執(zhí)行下面任意一條語句，都會(huì)阻塞
// 驗(yàn)證記錄鎖
update price_test set price = 1 where name = 'orange';
update price_test set price = 1 where name = 'perl';
// 驗(yàn)證間隙鎖
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);
// 執(zhí)行下面的語句正常執(zhí)行
update price_test set price = 1 where name = 'apple';
insert into price_test(id,name,price) values(5,"aa", 20);

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

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

總結(jié)

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

由此，我們大致可以得出結(jié)論：

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

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

參考資料

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