MySQL 啥时候用记录锁，啥时候用间隙锁？

一时失言乱红尘 2023-09-26 15:43 129阅读 0赞

MySQL 啥时候会用记录锁，啥时候会用间隙锁，啥时候又会用 Next-Key 锁呢？今天我们就来做一些测试，弄清楚这个问题。

![4e219e7e186be08557364c0e7098875a.png][]

文章思维导图

### 影响因素 ###

*在开始之前，我们需要声明的是：本文所有测试及结论的前提均是在「可重复读」隔离级别下，以及 Innodb 存储疫情下。*

根据网上资料，我们大概可以知道，影响其使用哪种行级锁的因素有：

1.  索引类型（聚簇索引、唯一二级索引、普通二级索引）
2.  匹配类型（精确匹配、唯一匹配、范围匹配）
3.  事务隔离级别
4.  是否开启 Innodb\_locks\_unsafe\_for\_binlog 系统变量
5.  记录是否被标记删除
6.  具体的执行语句类型（SELECT、INSERT、DELETE、UPDATE）

为了让文章相对易懂一些，我准备重点测试索引类型与匹配类型两个影响因素。对于其他的影响因素，我将不做改动。例如：事务隔离级别固定为「可重复读」，Innodb\_locks\_unsafe\_for\_binlog 固定为 false。而第 5、6 点相对来说简单一些，则我们会简单带过。

针对上面几个影响因素，我们指定了几个测试实验，分别是：

1.  聚簇索引 + 精确匹配
2.  聚簇索引 + 范围匹配
3.  唯一二级索引 + 精确匹配
4.  唯一二级索引 + 范围匹配
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;` 查看锁信息如下图所示。

![8bcb9b432f973624becfb14d0f3a8a6a.jpeg][]

可以看到，其是对 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;` 查看锁信息如下图所示。

![f343b9969cb295eab26d0a8447df9358.jpeg][]

可以看到，其加了一个间隙锁，该间隙锁应该是 (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;` 查看锁信息如下图所示。

![3365d1fae12d1a6b4e2205fd1d890ee1.jpeg][]

可以看到，事务 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;` 查看锁信息如下图所示。

![9b2ec9a880f59fca768f215b1ea8ce67.jpeg][]

可以看到，其实加了 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;` 查看锁信息如下图所示。

![d15a56e6af95674feb49125696658a7b.jpeg][]

可以看到，其加的行级锁是 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;` 查看锁信息如下图所示。

![360974e92bb45898091927f17e045d17.jpeg][]

可以看到，事务 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;` 查看锁信息如下图所示。

![c0017192623ce5b1b7f80f630f56f53c.jpeg][]

可以看到，其不仅有一个记录锁，还有一个间隙锁。这里可以猜测记录锁是 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;` 查看锁信息如下图所示。

![a50e3984dceda1d09bebb0f21e939c24.jpeg][]

从上图可以看到起一共有 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 锁」。**

### 总结 ###

我们做了这么多个测试，虽然有 3 种索引类型（聚簇索引、唯一二级索引、普通二级索引）和 2 种匹配类型（精确匹配、范围匹配），它们两两组合可以得出 6 种情况，再加上查询的值是否存在，可能有更多的可能性。**但是我们发现它们的结构都非常类似，基本上都跟查找的记录是否存在，以及查找的记录是否是唯一的相关。**

由此，我们大致可以得出结论：

1.  **如果查找的记录是唯一且存在的，那么只会使用记录锁，而不会使用间隙锁或 Next-Key 锁。**
2.  **如果查找的记录不唯一或者不存在，那么就会使用 Next-Key 锁和间隙锁。**

[4e219e7e186be08557364c0e7098875a.png]: https://img-blog.csdnimg.cn/img_convert/4e219e7e186be08557364c0e7098875a.png
[8bcb9b432f973624becfb14d0f3a8a6a.jpeg]: https://img-blog.csdnimg.cn/img_convert/8bcb9b432f973624becfb14d0f3a8a6a.jpeg
[f343b9969cb295eab26d0a8447df9358.jpeg]: https://img-blog.csdnimg.cn/img_convert/f343b9969cb295eab26d0a8447df9358.jpeg
[3365d1fae12d1a6b4e2205fd1d890ee1.jpeg]: https://img-blog.csdnimg.cn/img_convert/3365d1fae12d1a6b4e2205fd1d890ee1.jpeg
[9b2ec9a880f59fca768f215b1ea8ce67.jpeg]: https://img-blog.csdnimg.cn/img_convert/9b2ec9a880f59fca768f215b1ea8ce67.jpeg
[d15a56e6af95674feb49125696658a7b.jpeg]: https://img-blog.csdnimg.cn/img_convert/d15a56e6af95674feb49125696658a7b.jpeg
[360974e92bb45898091927f17e045d17.jpeg]: https://img-blog.csdnimg.cn/img_convert/360974e92bb45898091927f17e045d17.jpeg
[c0017192623ce5b1b7f80f630f56f53c.jpeg]: https://img-blog.csdnimg.cn/img_convert/c0017192623ce5b1b7f80f630f56f53c.jpeg
[a50e3984dceda1d09bebb0f21e939c24.jpeg]: https://img-blog.csdnimg.cn/img_convert/a50e3984dceda1d09bebb0f21e939c24.jpeg