六个案例搞懂mysql间隙锁

2025-06-11 16:50

本文主要是介绍六个案例搞懂mysql间隙锁,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

《六个案例搞懂mysql间隙锁》MySQL中的间隙是指索引中两个索引键之间的空间,间隙锁用于防止范围查询期间的幻读,本文主要介绍了六个案例搞懂mysql间隙锁,具有一定的参考价值,感兴趣的可以了解一下...

mysql中的间隙是指索引中两个索引键之间的空间,间隙锁用于防止范围查询期间的幻读,确保查询结果的一致性和并发安全性。

概念解释

记录锁(Record Lock)

记录锁也被称为行锁,顾名思义,它是针对数据库中的行记录进行的锁定。

比如:

SELECT * FROM `user` WHERE `id`=1 FOR UPDATE;

上面的SQL会在 id=1 的行记录上加上记录锁,以阻止其他事务插入,更新,删除这一行。

间隙锁(Gap Lock)

间隙锁就是对间隙加锁,用于锁定索引范围之间的间隙,以避免其他事务在这个范围内插入新的数据。间隙锁是排它锁,阻止了其他事务在间隙中插入满足条件的值,间隙锁仅在可重复读隔离级别下才有效。

关于间隙锁的详细讲解放在下文,这里只是先做个概念上的介绍。

临键锁(Next-Key Lock)

临键锁由记录锁和间隙锁组合而成,它在索引范围内的记录上加上记录锁,并在索引范围之间的间隙上加上间隙锁。这样可以避免幻读(Phantom Read)的问题,确保事务的隔离性。

切记:间隙锁的区间是左开右开的,临键锁的区间是左开右闭的。

间隙锁详解

间隙锁是保证临键锁正常运作的基础,理解间隙锁的概念对于深入理解这三种锁非常重要。

间隙锁的锁定范围是指在索引范围之间的间隙

举个简单例子来说明:

假设有一个名为products的表,其中有一个整型列product_id作为主键索引。现在有两个并发事务:事务A和事务B。

事务A执行以下语句:

BEGIN;
SELECT * FROM `products` WHERE `product_id` BETWEEN 100 and 200 FOR UPDATE;

事务B执行以下语句:

BEGIN;
INSERT INTO `products` (`pChina编程roduct_id`, `name`) VALUES (150, 'Product 150');

在这种情况下,事务A会在products表中product_id值在 100 和 200 之间的范围上设置间隙锁。因此,在事务A运行期间,其他事务无法在这个范围内插入新的数据,在事务B尝试插入product_id为150的记录时,由于该记录位于事务A锁定的间隙范围内,事务B将被阻塞,直到事务A释放间隙锁为止。

间隙锁触发条件

在可重复读(Repeatable Read)事务隔离级别下,以下情况会产生间隙锁:

  • 使用普通索引锁定:当一个事务使用普通索引进行条件查询时,MySQL会在满足条件的索引范围之间的间隙上生成间隙锁。
  • 使用多列唯一索引:如果一个表存在多列组成的唯一索引,并且事务对这些列进行条件查询时,MySQL会在满足条件的索引范围之间的间隙上生成间隙锁。
  • 使用唯一索引锁定多行记录:当一个事务使用唯一索引来锁定多行记录时,MySQL会在这些记录之间的间隙上生成间隙锁,以确保其他事务无法在这个范围内插入新的数据。

需要注意的是,上述情况仅在可重复读隔离级别下才会产生间隙锁。在其他隔离级别下,如读提交(Read Committed)隔离级别,MySQL可能会使用临时的意向锁来避免并发问题,而不是生成真正的间隙锁。

为什么这里强调的是普通索引呢?因为对唯一索引锁定并不会触发间隙锁,请看下面这个例子:

假设我们有一个名为students的表,其中有两个字段:id js和 name。id是主键,现在有两个事务同时进行操作:

事务A执行以下语句:

SELECT * FROM students WHERE id = 1 FOR UPDATE;

事务B执行以下语句:

INSERT INTO students (id, name) VALUESandroid (2, 'John');

由于事务A使用了唯一索引锁定,它会锁定id为1的记录,不会触发间隙锁。同时,在事务B中插入id为2的记录也不会受到影响。这是因为唯一索引只会锁定匹配条件的具体记录,而不会锁定不存在的记录(如间隙)。

当使用唯一索引锁定一条存在的记录时,会使用记录锁,而不是间隙锁

但是当搜索条件仅涉及到多列唯一索引的一部分列时,可能会产生间隙锁。以下是一个例子:

假设students表,包含三个列:id、name和age。我们在(name, age)上创建了一个唯一索引。

现在有两个事务同时进行操作:

事务A执行以下语句:

SELECT * FROM students WHERE name = 'John' FOR UPDATE;

事务B执行以下语句:

INSERT INTO students (id, name, age) VALUES (2, 'John', 25);

在这种情况下,事务A搜索的条件只涉及到了唯一索引的一部分列(name),而没有涉及到完整的索引列(name, age)。因此,MySQL会对匹配的记录加上行锁,并且还会对与该条件范围相邻的间隙加上间隙锁。

间隙锁加锁规则

间隙锁有以下加锁规则:

  • 规则1:加锁的基本单位是 Next-Key Lock,左开右闭区间。
  • 规则2:查找过程中访问到的对象才会加锁。
  • 规则3:唯一索引上的范围查询会上锁到不满足条件的第一个值为止。
  • 规则4:唯一索引等值查询,并且记录存在,Next-Key Lock 退化为行锁。
  • 规则5:索引上的等值查询,会将距离最近的左边界和右边界作为锁定范围,如果索引不是唯一索引还会继续向右匹配,直到遇见第一个不满足条件的值,如果最后一个值不等于查询条件,Next-Key Lock 退化为间隙锁。

记住上述这些规则,这些规则不太好理解,我们下面通过案例来讲解。

案例演示

环境:MySQL,InnoDB,RR隔离级别。

数据表:

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
   KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据:

idagename
11小明
55小王
77小张
1111小陈

在进行测试之前,我们先来看看 user 表中存在的隐藏间隙:

  • (-∞, 1]
  • (1, 5]
  • (5, 7]
  • (7, 11]
  • (11, +∞]

案例一:唯一索引等值锁定存在的数据

如下是事务A和事务B执行的顺序:

时刻事务A事务B
T1bejavascriptginbegin
T2select * from user where id = 5 for update
T3insert into user value(3,3,“小黑”) —不阻塞
T4insert into user value(6,6,“小蓝”) —不阻塞
T5commitcommit

根据规则4,加的是记录锁,不会使用间隙锁,所以只会锁定 5 这一行记录。

案例二:索引等值锁定

时刻事务A事务B
T1beginbegin
T2select * from user where id = 3 for update — 不存在的数据
T3insert into user value(6,6,“小蓝”) — 不阻塞
T4insert into user value(2,2,“小黄”) — 阻塞
T5commit

这是一个索引等值查询,根据规则1和规则5,加锁范围是( 1,5 ] ,又由于向右遍历时最后一个值 5 不满足查询需求,Next-Key Lock 退化为间隙锁。也就是最终锁定范围区间是 ( 1,5 )。

案例三:唯一索引范围锁定

时刻事务A事务B
T1beginbegin
T2select * from user where id >= 5 and id<6 for update
T3insert into user value(7,7,“小赵”) — 阻塞
T4commit

根据规则3,会上锁到不满足条件的第一个值为止,也就是7,所以最终加锁范围是 [ 5,7 ]。

其实这里可以分为两个步骤,第一次用 id=5 定位记录的时候,其实加上了间隙锁 ( 1,5 ],又因为是唯一索引等值查询,所以退化为了行锁,只锁定 5。

第二次用 id<6 定位记录的时候,其实加上了间隙锁( 5,7 ],所以最终合起来锁定区间是 [ 5,7 ]。

案例四:非唯一索引范围锁定

时刻事务A事务B
T1beginbegin
T2select * from user where age >= 5 and age<6 for update
T3insert into user value(8,8,“小青”) — 不阻塞
T4insert into user value(2,2,“小黄”) — 阻塞
T5commit

参考上面那个例子。

第一次用 age =5 定位记录的时候,python加上了间隙锁 ( 1,5 ],不是唯一索引,所以不会退化为行锁,根据规则5,会继续向右匹配,所以最终合起来锁定区间是 ( 1,7 ]。

案例五:间隙锁死锁

时刻事务A事务B
T1beginbegin
T2select * from user where id = 3 for update
T3select * from user where id = 4 for update
T4insert into user value(2,2,“小黄”) — 阻塞
T5insert into user value(4,4,“小紫”) — 阻塞

间隙锁之间不是互斥的,如果一个事务A获取到了( 1,5 ] 之间的间隙锁,另一个事务B仍然可以获取到( 1,5 ] 之间的间隙锁。这时就可能会发生死锁问题。

在事务A事务提交,间隙锁释放之前,事务B也获取到了间隙锁( 1,5 ] ,这时两个事务就处于死锁状态。

案例六:limit对加锁的影响

时刻事务A事务B
T1beginbegin
T2deletet user where age = 6 limt 1
T3insert into user value(7,7,“小赵”) — 不阻塞
T4
T5commitcommit

根据规则5,锁定区间应该是 ( 5,7 ],但是因为加了 limit 1 的限制,因此在遍历到 age=6 这一行之后,循环就结束了。

根据规则2,查找过程中访问到的对象才会加锁,所以最终锁定区间应该是:( 5,6 ]。

总结

在本文中,我们讨论了间隙锁的加锁规则。间隙锁是MySQL中用于保护范围查询和防止并发问题的重要机制,了解间隙锁的加锁规则对于优化数据库性能、减少数据冲突以及提高并发性能非常重要。

到此这篇关于六个案例搞懂mysql间隙锁的文章就介绍到这了,更多相关mysql间隙锁内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)!

这篇关于六个案例搞懂mysql间隙锁的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1155014

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更