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

MySQL 啥时候用记录锁,啥时候用间隙锁? 我们做了这么多个测试,虽然有 3 种索引类型(聚簇索引、唯一二级索引、普通二级索引)和 2 种匹配类型(精确匹配、范围匹配),它们两两组合可以得出 6 种情况,再加上查询的值是否存在,可能有更多的可能性。但是我们发现它们的结构都非常类似,基本上都跟查找的记录是否存在,以及查找的记录是否是唯一的相关。如果查找的记录是唯一且存在的,那么只会使用记录锁,而不会使用间隙锁或 Next-Key 锁。如果查找的记录不唯一或者不存在,那么就会使用 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;

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

【MySQL】记录锁+间隙锁可以防止删除操作而导致的幻读吗? 阅读详情

相关推荐

从零搭建Arduino仿真开发环境:SimulIDE与IDE配置全攻略

本文详细介绍了如何从零搭建Arduino仿真开发环境,涵盖SimulIDE与Arduino IDE的配置全流程。通过仿真模拟环境,开发者可以零成本试错,快速验证电路设计和程序逻辑,特别适合初学者和便携开发需求。文章提供了软件安装、环境配置、项目实战及问题排查的完整指南,帮助读者高效掌握Arduino仿真开发技巧。

weixin_29284657的博客 525

【MySQL】MySQL的锁机制 - 记录锁、间隙锁、临键锁

MySQL的锁机制 - 记录锁、间隙锁、临键锁

sfh2018的博客 7141

centos-stream-9上安装nvidia驱动和cuda-toolkit

centos-stream-9上安装nvidia驱动和cuda-toolkit

qq_19072921的博客 3022

【mysql45讲】InnoDB间隙锁与加锁规则

一、间隙锁的概念 1.间隙锁的作用 解决幻读问题 2.间隙锁的意思 间隙锁,锁的就是两个值之间的间隙。 3.间隙锁的冲突关系 间隙锁只跟“向间隙中插入记录”存在冲突关系。 4.next-key-lock 间隙锁和行锁合称next-key-lock。每个next-key-lock是左开右闭区间。 比如有两条记录a和b,执行语句select * from t for update时,要把整个表锁起来,就形成了三个next-key-lock: (-∞,a],(a,b],(b,supernum]。 ps:supe

乾坤未定,你我皆是黑马 1814

HAMi 核心模块与架构设计

HAMi 的架构设计是围绕 Kubernetes 生态系统构建的,通过扩展 Kubernetes 的调度能力和设备管理能力,为用户提供了高效、灵活的异构设备虚拟化和管理解决方案。

北斗云的博客 2552

PX4 EKF2协方差矩阵‘负定’了怎么办?保姆级排查与数值稳定性修复指南

本文深入解析PX4 EKF2协方差矩阵出现‘负定’问题的原因,并提供全面的排查与修复方案。从理论分析到实践操作,详细介绍了数值稳定性危机的诊断工具、算法加固措施及工程实践中的防御性编程技巧,帮助飞控工程师有效解决滤波算法中的数值稳定性问题。

weixin_28730927的博客 319

MySQL的锁机制 - 记录锁、间隙锁、临键锁

记录锁(Record Locks) 记录锁是封锁记录,记录锁也叫行锁,例如: SELECT * FROM `test` WHERE `id`=1 FOR UPDATE; 它会在 id=1 的记录上加上记录锁,以阻止其他事务插入,更新,删除 id=1 这一行。 记录锁、间隙锁、临键锁都是排它锁,而记录锁的使用方法跟之前的一篇文章共享/排它锁里的排它锁介绍一致,这里就不详细多讲。 间隙锁(Gap Locks)(重点) 间隙锁是封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者...

song_myth的博客 4375

电源完整性之仿真设计原理

电源完整性(Power Integrity),简称为PI,也就是大家平常听说的PI。PCB板上的电源设计也是非常重要的,不当的设计也会引起很重要的影响。所以电源完整性PI和信号完整性SI,是我们互连设计人员应该同等重视的问题。今天整理本人接触的电源完整性PI的一些原理,从不的角度去看PI,会有不同的理解,下面开始喽!!! 提到互连的电源设计,可能首先想到的电源分配网络(Power Distribution Nets),因为互连电源会有许多种,直流DC和交流AC,还有不同的电压调节模块(VRM)与不同的负载端

刘小同学的博客 7379

间隙锁(Gap Lock)

间隙锁。

qq_43985303的博客 1097

Vivado FIR Complier_IP核使用方法

文章依靠该IP核对应手册PG149写的,因为笔者需求简单,没有深入挖掘功能,可以自行下载学习。

2302_80180551的博客 3999

AutoSAR MemMap模块详解及使用注意事项

在本文中,我们将深入探讨MemMap模块的实例解析,并介绍在使用该模块时需要注意的一些事项。在上述代码中,我们使用了MEMMAP_START_SEC_VAR_32BIT和MEMMAP_START_SEC_CODE来定义MemMap区域的起始位置,使用MEMMAP_STOP_SEC_VAR_32BIT和MEMMAP_STOP_SEC_CODE来定义MemMap区域的结束位置。1.合理划分MemMap区域:根据实际需求,合理划分MemMap区域,将不同类型的变量放置在不同的区域中,以便于管理和访问。

weixin_50547796的博客 2534

面试官:可重复读+间隙锁+记录锁能保证MySQL一定不发生幻读吗

之前有位小伙伴美团三面,一直被追求「幻读是否被 MySQL 可重复度隔离级别彻底解决了?」

wdj_yyds的博客 607

从你的全世界路过---陌陌X-SIGN还原

我们上期介绍了hook 接口方式,今天我分析so 层。学习是为了复杂的事情简单化。不用如此繁琐。跟进native_sdbyecbu37x这个函数,我们f5看伪代码 v13 = sign(v9, v10, v11, a6); 跟进这个函数 代码如下(示例): 2.代码贴入 SHA消息摘要java代码如下: java 代码如下(示例): 总结 算法,结合动态调试!

任雪飘的博客 2303

17 面试官:MySQL 间隙锁到底是什么?三张图说清楚它什么时候加、怎么加

本文深入剖析了 MySQL InnoDB 引擎中用于防止幻读的关键机制——间隙锁(Gap Lock)与临键锁(Next-Key Lock)。文章从锁的类型划分讲起,澄清了记录锁、间隙锁和临键锁的区别与联系。重点通过源码层面解读了临键锁的加锁规则,并结合唯一索引等值查询、普通索引等值查询和范围查询三大经典场景,详细拆解了不同条件下的加锁范围。最后总结了“无索引导致全表锁”、“插入意向锁与间隙锁冲突”和“RC级别无间隙锁”三个生产环境中最易踩中的坑,并提供了面试官视角的标准回答模板,帮助读者彻底掌握这一高频面试

weixin_42023666的博客 737

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

IDEA的使用指南

IDEA基于java的使用手册,可以最快速的上手,在使用中不断的丰富自己的使用技巧.

美团二面:MySQL记录锁+间隙锁可以防止删除操作而导致的幻读吗?

昨天有位读者在美团二面的时候,被问到关于幻读的问题:

Javatutouhouduan的博客 841

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗?

首先来看看 MySQL 文档是怎么定义幻读(Phantom Read)的:翻译:当同一个查询在不同的时间产生不同的结果集时,事务中就会出现所谓的幻象问题。例如,如果 SELECT 执行了两次,但第二次返回了第一次没有返回的行,则该行是“幻像”行。T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 6 条行记录,那就发生了幻读的问题。T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 4 条行记录,也是发生了幻读的问题。MySQL 是怎么解决幻读的?

百炼之志的博客 194

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

【170期】面试官:你能分别谈谈innodb下的记录锁,间隙锁,next-key锁吗?

程序员的成长之路互联网/程序员/技术/资料共享关注阅读本文大概需要 6.5 分钟。来自:https://www.jianshu.com/p/bf862c37c4c9你需要知道的之前我们介...

良月柒 580

面试官:MySQL 是怎么加锁的?

MySQL 是怎么加锁的? 大家好,我是小林。 在前一篇文章我讲了下 MySQL 的全局锁、表记锁和行级别锁,其中行级锁只提了概念,并没有具体说。 因为行级锁加锁规则比较复杂,不同的场景,加锁的形式还不同,所以这次就来好好介绍下行级锁。 对记录加锁时,加锁的基本单位是 next-key lock,它是由记录锁和间隙锁组合而成的,next-key lock 是前开后闭区间,而间隙锁是前开后开区间。 但是,next-key lock 在一些场景下会退化成记录锁或间隙锁。 那到底是什么场景呢?今天,我们就以下面这

小林coding 5782

MySQL记录锁+间隙锁可以防止删除操作而导致的幻读吗?

昨天有位读者在美团二面的时候,被问到关于幻读的问题:

Javatutouhouduan的博客 408

MySQL记录锁+间隙锁可不可以防止删除操作而导致的幻读?

昨天有位读者在美团二面的时候,被问到关于幻读的问题:

Javatutouhouduan的博客 367
上一篇: 大型分布式系统下缓存应该怎么玩,才能用得高效
下一篇: 阿里出品,SpringBoot应用自动化部署神器,IDEA版Jenkins?
Java佳佳
博客等级 码龄4年 216粉丝 286原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值