【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁

这篇具有很好参考价值的文章主要介绍了【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在「读未提交」和「读已提交」隔离级别下,都只会使用记录锁;而对于「可重复读」隔离级别来说,会使用记录锁、间隙锁和 Next-Key 锁

那么 MySQL 啥时候会用记录锁,啥时候会用间隙锁,啥时候又会用 Next-Key 锁呢?

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

1. 影响因素

影响其使用哪种行级锁的因素有:

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

测试数据:

CREATE TABLE `price_test`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `price` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ind_price`(`price`) USING BTREE,
  INDEX `ind_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `price_test` VALUES (1, 'apple', 10);
INSERT INTO `price_test` VALUES (2, 'orange', 30);
INSERT INTO `price_test` VALUES (50, 'perl', 60);

2. 8 种情况

在看死锁日志的时候,我们一般先把这个变量 innodb_status_output_locks 打开哈,它是MySQL 5.6.16 引入的:

set global innodb_status_output_locks =on;

2.1 读可提交隔离级别

2.1.1 查询条件是主键

加一个记录锁

2.1.2 查询条件是唯一索引

加两个记录锁:主键索引上的一条记录;唯一索引上的一条记录

2.1.3 查询条件是普通索引

对应的所有满足 SQL 查询条件的记录,都会加上锁。同时,这些记录对应主键索引,也会上锁

2.1.4 查询条件列无索引

MySQL 会走聚簇索引进行全表扫描过滤。每条记录都会加上 X 锁。但是,为了效率考虑,MySQL 在这方面进行了改进,在扫描过程中,若记录不满足过滤条件,会进行解锁操作。同时优化违背了2PL原则

2.2 可重复读隔离级别

2.2.1 聚簇索引 + 精确匹配

  1. 事务 A 执行下面命令:
begin;
select * from price_test where id = 2 for update;
  1. 事务 B 执行下面命令:
begin;
update price_test set price = 25 where id = 2;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

可以看到,其是对 id 为 2 的索引加了一个记录锁

那如果聚簇索引的值找不到对应的记录呢,将会是一个什么样的结果呢?

  1. 事务 A 执行下面命令,其中 id 为 5 的记录是不存在的
begin;
select * from price_test where id = 5 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

加了一个间隙锁,该间隙锁是 (2, 50) 这个范围

  1. 事务 B 执行如下命令来测试下间隙锁的范围
begin;
// 执行下面任何一个命令,可以通过
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);

聚簇索引 + 精确匹配: 如果能够定位到唯一一条存在的记录,那么其会使用记录锁。如果该记录不存在,那么则会使用间隙锁

2.2.2 聚簇索引 + 范围匹配

  1. 事务 A 执行下面命令:
begin;
select * from price_test where id >= 2 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

事务 A 一共加了 3 个锁,其中 1 个记录锁,2 个 Next-Key 锁。其中 1 个记录锁是对 id 为 2 的索引加的锁,Next-Key 锁是对 (2, 50] 和 (50, 正无穷) 这两个区间加的锁

  1. 在事务 B 执行下面命令可以验证间隙锁的加锁区间:
begin;
// 执行下面任意一条语句,都会阻塞
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);

如果范围匹配的值并不存在,那么会是什么情况呢?

  1. 事务 A 执行如下语句,其中 id 为 50 的记录是不存在的。
begin;
select * from price_test where id > 50 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

加了 1 个 Next-Key 锁,锁的范围应该是(50, + 无穷)

聚簇索引 + 范围匹配:存在匹配的值,会使用记录锁 + Next-Key 锁;不存在匹配的值,只会使用 Next-Key 锁

2.2.3 唯一二级索引 + 精确匹配

  1. 事务 A 执行下面命令
begin;
select * from price_test where price = 10 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

加的行级锁是 2 个记录锁,应该是 price = 10 这条索引记录的锁

  1. 此时,如果在事务 B 执行下面命令:
begin;
// 执行下面任意一条语句,都会阻塞
update price_test set name = 'test-name' where price = 10;

那如果唯一二级索引的值找不到对应的记录呢,将会是一个什么样的结果呢?

  1. 事务 A 执行下面命令,其中 price 为 11 的记录是不存在的
begin;
select * from price_test where price = 11 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

加了一个间隙锁,该间隙锁是 (10, 30) 这个范围

唯一二级索引 + 精确匹配:唯一二级索引与聚簇索引非常类似,如果能够定位到唯一一条存在的记录,那么其会使用记录锁。如果该记录不存在,那么则会使用间隙锁

2.2.4 唯一二级索引 + 范围匹配

  1. 事务 A 执行下面命令:
begin;
select * from price_test where price >= 30 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

一共加了 5 个行锁,2 个记录锁(price 为 30、60 的记录),3 个 Next-Key 锁, 。3 个 Next-Key 锁则是 (10, 30]、(30,60]、(60, 正无穷)三个范围

  1. 在事务 B 执行下面命令,每条 SQL 都会阻塞住:
begin;
// 执行下面任意一条语句,都会阻塞
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);

如果范围匹配的值并不存在,那么会是什么情况呢?

  1. 事务 A 执行下面命令:
begin;
select * from price_test where price >= 70 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

加了一个 Next-key 锁 (60, 正无穷)

聚簇索引 + 范围匹配:存在匹配的值,会使用记录锁 + Next-Key 锁;不存在匹配的值,只会使用 Next-Key 锁

2.2.5 普通二级索引 + 精确匹配

  1. 事务 A 执行下面命令:
begin;
select * from price_test where name = 'apple' for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

一个记录锁 (name = ‘apple’)、间隙锁(范围: (负无穷,orange))、Next-key 锁(二级索引的记录锁 + 间隙锁)

  1. 事务 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 锁;否则,只使用间隙锁

2.2.6 普通二级索引 + 范围匹配

  1. 事务 A 执行下面命令:
begin;
select * from price_test where name >= 'orange' for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

一共有 2 个记录锁,3 个 Next-Key 锁。其中 2 个记录锁应该是 orange 和 perl 两个记录,3 个 Next-Key 锁,应该是 (apple, orange]、[orange, perl)、[perl, 正无穷)

  1. 事务 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 锁;若不存在,则使用 Next-Key 锁

2.2.7 无索引

如果查询条件列没有索引,主键索引的所有记录,都将加上 X 锁,每条记录间也都加上间隙 Gap 锁。大家可以想象一下,任何加锁并发的 SQL,都是不能执行的,全表都是锁死的状态。如果表的数据量大,那效率就更低

【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁,MYSQL,mysql,学习,adb

在这种情况下,MySQL 做了一些优化,即 semi-consistent read,对于不满足条件的记录,MySQL 提前释放锁,同时 Gap 锁也会释放。而 semi-consistent read 是如何触发的呢:要么在 Read Committed 隔离级别下;要么在 Repeatable Read 隔离级别下,设置了 innodb_locks_unsafe_for_binlog 参数。但是 semi-consistent read 本身也会带来其他的问题,不建议使用。

2.3 Serializable 串行化

在 Serializable 串行化的隔离级别下,对于写的语句,比如 update account set balance= balance-10 where name=‘Jay’;,跟RC和RR隔离级别是一样的。不一样的地方是,在查询语句,如 select balance from account where name = ‘Jay’;,在 RC 和 RR 是不会加锁的,但是在 Serializable 串行化的隔离级别,即会加锁文章来源地址https://www.toymoban.com/news/detail-782257.html

到了这里,关于【MYSQL】MYSQL 的学习教程(十二)之 MySQL 啥时候用记录锁,啥时候用间隙锁的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 详细学习教程【万字长文, 建议收藏】

    Mysql文档地址👉 建议在用户变量下配置 在mysql-5.7.19-winx64目录下创建my.ini文件 1.3.1 Navicat 1.3.2 SQLyog 改变字体显示 所有安装Mysql数据库,就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system) 一个数据库中可以创建多个表,以保存

    2024年02月14日
    浏览(54)
  • 【教程】MySQL数据库学习笔记(三)——数据定义语言DDL(持续更新)

    写在前面: 如果文章对你有帮助,记得点赞关注加收藏一波,利于以后需要的时候复习,多谢支持! 第一章 《认识与环境搭建》 第二章 《数据类型》 第三章 《数据定义语言DDL》 DDL(Data Definition Language,数据定义语言) 是用于定义和管理数据库对象(如表、视图、索引等

    2024年02月20日
    浏览(43)
  • 【Redis】Redis 的学习教程(十二)之在 Redis使用 lua 脚本

    lua 菜鸟教程:https://www.runoob.com/lua/lua-tutorial.html 在 Redis 使用 lua 脚本的好处: 减少网络开销 。可以将多个请求通过脚本的形式一次发送,减少网络时延及开销 原子性操作 。Redis会将整个脚本作为一个整体执行,中间不会被其他请求插入。因此在脚本运行过程中无需担心会出

    2024年02月07日
    浏览(47)
  • Docker的使用教程、学习笔记,附实战:部署chatgpt网页版ui,部署mysql8数据库

    作者:ChenZhen 博客地址:https://www.chenzhen.space/ 版权:来自b站视频 【SpringCloud+RabbitMQ+Docker+Redis+搜索+分布式,系统详解springcloud微服务技术栈课程|黑马程序员Java微服务】 因为黑马的课件ppt做的实在是太好了,基本就是原样copy过来整理一下。 如果对你有帮助,请给一个小小的

    2024年02月04日
    浏览(56)
  • 【深度学习】Pytorch 系列教程(十二):PyTorch数据结构:4、数据集(Dataset)

             目录 一、前言 二、实验环境 三、PyTorch数据结构 0、分类 1、张量(Tensor) 2、张量操作(Tensor Operations) 3、变量(Variable) 4、数据集(Dataset) 随机洗牌           ChatGPT:         PyTorch是一个开源的机器学习框架,广泛应用于深度学习领域。它提供了丰富

    2024年02月07日
    浏览(43)
  • 【深度学习】 Python 和 NumPy 系列教程(十二):NumPy详解:4、数组广播;5、排序操作

    目录 一、前言 二、实验环境 三、NumPy 0、多维数组对象(ndarray) 多维数组的属性 1、创建数组 2、数组操作 3、数组数学 4、数组广播 5、排序操作 1. np.sort() 函数 2. np.argsort() 函数 3. ndarray.sort() 方法 4. 按列或行排序 5. np.lexsort() 函数 6. np.partition() 函数 7. np.argpartition() 函

    2024年02月08日
    浏览(62)
  • SAP UI5 官方教程学习记录

    最近有闲跟着官方的Get Started教程学习了UI5,记录一下自己学习中遇到的几个问题。 本文链接:https://www.cnblogs.com/hhelibeb/p/17835722.html 注意文档可能不是最新的,和实际示例代码有出入,比如本文写作时,Data Binding Tutorial里面的Step 1: No Data Binding 教程里写的代码是, 示例的实

    2024年02月05日
    浏览(36)
  • gtest教程(记录小白从0学习gtest的过程)

    1 简介 之前对gtest一无所知,最近,找了些相关的资料,学习了下.这里主要记录了学习过程和相关知识点. 什么是gtest: gtest测试框架是在不同平台上(Linux,Mac OS X,Windows,Cygwin,Windows CE和Symbian)为编写C++测试而生成的。它是基于xUnit架构的测试框架,支持自动发现测试,丰富的

    2023年04月23日
    浏览(31)
  • Python+Django+Mysql开发简单在线课程推荐系统简单教程 基于用户的协同过滤推荐算法 个性化课程推荐系统 爬虫 可视化数据分析 机器学习 人工智能 个性化推荐 相关推荐

    1、开发工具和使用技术 Python3及以上版本,Django3.6及以上版本,mysql8,navicat数据库管理工具或者sqlyog数据库管理工具,html页面,javascript脚本,jquery脚本,echarts.js可视化图表工具,自定义星星评分功能(使用font-awesome星星图标字体)等。 2、实现功能 前台首页地址:http://1

    2024年02月05日
    浏览(95)
  • 恒源云 & PyCharm!!!深度学习在恒源云上租服务器并进行远程连接超级详细教程!!!(个人学习记录)

    本人因为实验需要,经常在网上租服务。之前用过Autodl平台,也出了相关教程Autodl使用教程。因为Autodl上面的卡空闲的比较少,而且都比较贵,最近发现了恒源云这个新平台。感觉上面服务器的普遍比Autodl上的便宜(没有严格对比),但是恒源云也存在一个问题,就是租一个

    2024年04月22日
    浏览(54)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包