您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

时间:2021-12-14 09:19:27  来源:  作者:python数据分析

生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。

有关MySQL记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章 一文详解MySQL的锁机制

这篇主要通过小案例来对记录锁间隙(gap)锁临键(next-key)锁做一个更好的理解。

这里先给出结论,再来用实际例子证明

1、当使用唯一索引来等值查询的语句时, 如果这行数据存在,不产生间隙锁,而是记录锁。

2、当使用唯一索引来等值查询的语句时, 如果这行数据不存在,会产生间隙锁。

3、当使用唯一索引来范围查询的语句时,对于满足查询条件但不存在的数据产生间隙(gap)锁,如果查询存在的记录就会产生记录锁,加在一起就是临键锁(next-key)锁。

4、当使用普通索引不管是锁住单条,还是多条记录,都会产生间隙锁;

5、在没有索引上不管是锁住单条,还是多条记录,都会产生表锁;

间隙锁会封锁该条记录相邻两个键之间的空白区域,防止其它事务在这个区域内插入、修改、删除数据,这是为了防止出现 幻读 现象;

间隙的范围?

根据检索条件向下寻找最靠近检索条件的记录值A作为左区间,向上寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B] 左开右闭。

接下来我们开始来验证以上结论

一、数据和环境准备

1、创建表和数据

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int NOT NULL COMMENT '年龄',
  `mobile` int DEFAULT NULL COMMENT '手机号',
  `name` varchar(8)  DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

id为主键(唯一索引)、age是普通索引、mobile没有加索引

同时插入数据如下。

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

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

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

(-∞, 1]
(1, 4]
(4, 7]
(7, +supernum]

(其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。)

2、关闭事务默认提交

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

结果显示,autocommit 的值是 ON,表示系统开启自动提交模式。

在 MySQL 中,可以使用 SET autocommit 语句设置事务的自动提交模式,语法格式如下:

SET autocommit = 0|1|ON|OFF;

对取值的说明:

值为 0 和值为 OFF:关闭事务自动提交。如果关闭自动提交,用户将会一直处于某个事务中,只有提交或回滚后才会结束当前事务,重新开始一个新事务。

值为 1 和值为 ON:开启事务自动提交。如果开启自动提交,则每执行一条 SQL 语句,事务都会提交一次。


 

二、唯一索引示例

1、等值查询且数据存在示例

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务A 等值查询id=4,因为id是主键,同时是等值查询存在该记录,所以只会在id=4这条记录上加记录锁,不会加间隙锁。

事务B 等值查询id=5,没有锁冲突,所以查询正常,不会堵塞。(如果事务B 等值查询id=4,因为事务A加了记录锁,所以会堵塞)

2、等值查询且数据不存在示例

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务A 等值查询id=5,因为查询记录不存在,所以无法加记录锁,但这里会存在一个(5,7]的间隙锁。

事务B 插入一条id=6的数据,因为上面存在了(5,7]的间隙锁,所以会堵塞。

3、范围查询示例

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务B 插入一个id=6、age=6的数据,因为age值在上面临键锁,范围内,所以也会堵塞。

事务B 插入一条id=6的数据,因为上面存在了(4,+supernum]的临键(next-key)锁,所以会堵塞。

如果 事务B 是更新 id=7 的记录,同样会堵塞。


 

三、普通索引示例

1、等值查询值

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务A 等值查询age=4,因为age是普通索引,所以会产生临键(next-key)锁(1,4]和(4,7],左开右闭原则

事务B 插入一个id=6、age=6的数据,因为age值在上面临键锁,范围内,所以也会堵塞。

2、左开右闭原则

按照上面的例子,如果事务B插入一条 id=6,age=1 的数据会不会堵塞呢,因为按照左开右闭原则,上面的age=1是开的,所以正常应该是可以插入的。

但实际上你真是实践之后,你发现同样也会堵塞。

通过实践之后,会发现,所谓的左开右闭原则,跟主键id有关系。

上面的事务A 等值查询age=4,它的当前主键id=4,上一条记录主键id=1,下条记录主键id=7。

如果插入 id<1, age 在(1,7)范围内,是 左闭右开原则。即age=1能插入,age=7会堵塞。

如果插入 1<id<7,age 在(1,7)范围内,是 左闭右闭原则。即age=1会堵塞,age=7也会堵塞。

如果插入 id>7,age 在(1,7)范围内,是 左开右闭原则。即age=1会堵塞,age=7能插入。

有关等值查询值不存在、普通索引范围的示例这里就不举了,跟上面的差不多,都会产生间隙锁。


 

四、无索引示例

1、等值查询值

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务A 等值查询 mobile = 8888884,因为mobile是无索引的,所以这个for update,变成表级排他(X)锁。

事务B 因为事务A已经加了表级的排他锁,所以其它事务无法进行任何的增删改操作。

2、范围查询

分享MySQL记录锁、间隙锁、临键锁小案例演示,你学废了吗

 

事务A 等值查询 mobile > 8888884,因为mobile是无索引的,所以这个for update,变成表级排他(X)锁。

事务B 因为事务A已经加了表级的排他锁,所以其它事务无法进行任何的增删改操作。



Tags:MySQL记录锁   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL记录锁  点击:(17)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条