面试官:MySQL 啥时候用记录锁,啥时候用间隙锁?

本文涉及的产品
服务治理 MSE Sentinel/OpenSergo,Agent数量 不受限
可观测可视化 Grafana 版,10个用户账号 1个月
简介: MySQL 啥时候会用记录锁,啥时候会用间隙锁,啥时候又会用 Next-Key 锁呢?今天我们就来做一些测试,弄清楚这个问题。

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

文章思维导图

影响因素

在开始之前,我们需要声明的是:本文所有测试及结论的前提均是在「可重复读」隔离级别下,以及 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; 查看锁信息如下图所示。

可以看到,其是对 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 锁」。

总结

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

由此,我们大致可以得出结论:

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

本文就是愿天堂没有BUG给大家分享的内容,大家有收获的话可以分享下,想学习更多的话可以到微信公众号里找我,我等你哦。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
存储 SQL 关系型数据库
MYSQL--锁机制*
MYSQL--锁机制*
|
2天前
|
SQL 关系型数据库 MySQL
字节面试:MySQL自增ID用完会怎样?
字节面试:MySQL自增ID用完会怎样?
10 0
字节面试:MySQL自增ID用完会怎样?
|
3天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
21 2
|
3天前
|
SQL 存储 算法
【MySQL技术内幕】6.4-锁的算法
【MySQL技术内幕】6.4-锁的算法
17 1
|
3天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
136 57
|
3天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.1-锁、lock和latch
【MySQL技术内幕】6.1-锁、lock和latch
11 0
|
3天前
|
存储 关系型数据库 MySQL
深入研究MySQL意向锁
MySQL意向锁是一种特殊的表级锁,由InnoDB存储引擎在操作数据之前自动添加,无需用户干预。它分为意向共享锁(IS)和意向排他锁(IX)两种。意向锁的主要作用是协调行锁和表锁的关系,优化加锁策略,避免全表扫描判断是否存在行锁。意向锁之间不会冲突,但会与表级别的排他锁冲突,从而确保数据库并发访问的一致性和完整性。简而言之,意向锁提高了数据库并发操作的性能和效率。
|
11天前
|
存储 算法 Java
JAVA后端开发面试题库
JAVA后端开发面试题库
20 1
|
15天前
|
缓存 安全 Java
【Java面试——并发基础、并发关键字】
随着硬件指令集的发展,我们可以使用基于冲突检测的乐观并发策略: 先进行操作,如果没有其它线程争用共享数据,那操作就成功了,否则采取补偿措施(不断地重试,直到成功为止)。这种乐观的并发策略的许多实现都不需要将线程阻塞,因此这种同步操作称为非阻塞同步。 乐观锁需要操作和冲突检测这两个步骤具备原子性,这里就不能再使用互斥同步来保证了,只能靠硬件来完成。硬件支持的原子性操作最典型的是: 比较并交换(Compare-and-Swap,CAS)。CAS 指令需要有 3 个操作数,分别是内存地址 V、旧的预期值 A 和新值 B。当执行操作时,只有当 V 的值等于 A,才将 V 的值更新为 B。
|
24天前
|
SQL 存储 Java
致远互联java实习生面试
致远互联java实习生面试
34 0

推荐镜像

更多