这是十三个实验,扫到了MySQL锁的多个盲区(下)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 这是十三个实验,扫到了MySQL锁的多个盲区(下)

image.png

可以看到,可重复读级别下 update 的加锁与读提交不太一样,加锁的 lock_data 是 1,说明事务 G 扫描的 id 为 1 的记录之后没有释放锁。

如果把事务G、H 的启动顺序反过来,也就是先执行 H 的语句再执行 G 的语句,结果也是一样的,同样加锁的 lock_data 是 1,这说明可重复读的 update 不是先判断条件是否符合再上锁,而是先上锁再判断条件是否符合

update 都会被阻塞,最终结论就是:

可重复读级别下,加锁非索引列导致的全表记录上锁会使得所有插入和修改都会被阻塞。


小结一下:



此时把读者问题列上:



image.png


留言的回答语境是在可重复读级别下,现在我再来总结回答下:

在读提交级别下

如果锁定的列为非索引列,加锁都是加到主键索引上的,select ..for update的加锁的顺序是从前往后全表扫描的顺序,遍历的记录先上锁,上锁之后发现不满足条件,则释放锁,然后继续往后遍历,直到全表扫描结束。

insert 都不会被阻塞。

而 update 其它字段值,其实也是找记录,如果找到的记录已经被上锁了,那么就会阻塞,如果找到的记录没有被锁则不会被阻塞。

在可重复读级别下

如果锁定的列为非索引列,加锁都是加到主键索引上的,select ..for update的加锁的顺序是从前往后全表扫描的顺序,遍历的记录先上锁,上锁之后发现不满足条件,则不会释放锁,然后继续往后遍历,直到全表扫描结束。

所以只要有一个全表扫描的加锁,则 insert 的时候就会被阻塞。

update 加锁和select ..for update一致。

与之相关的还有一个问题:


image.png


图里已经有答案了,包括前面的截图也可以看到所有的 lock_type 都是 RECORD ,也就是行级锁。

实验三:隔离级别为读提交,锁定索引列的实验

此时在 name 列建立索引

CREATE TABLE `yes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

同样准备数据如下:


image.png

可以看到,不会被阻塞,丝滑。

这个结果符合认知,因为此时 name 已经有索引了,在读提交级别下,只会在 name 索引上加相关记录的锁,而不会加全表行锁,因此事务 A、B 之间不会被阻塞。

此时再起一个事务 C,执行如下语句:


image.png


可以看到,锁的索引确实变成了 idx_name,lock_data 显示锁的是 yes 这个记录,id 为 1

从结果看:在可以命中二级索引的情况下,锁的是对应的二级索引

我们继续做实验。

将上面所有事务提交之后

启动事务 C 执行以下语句,且未提交事务:


image.png

执行 name 一样的插入,也不会阻塞。

所以在读提交级别下,对插入都不会产生阻塞。

关于 update 我就不实验了,和实验一的差别就是加锁索引换成了 name 的索引,其他表现一致。

实验四:隔离级别为可重复读,锁定索引列的实验

同样准备数据如下:


image.png

这是预期之内的阻塞,因为按照 name 为索引,yes这条记录是排在最后的(字母序),为了防止幻读,可重读隔离级别下会在对应记录前后加入间隙锁,而新的记录的插入恰巧需要排 yes 这条记录的后面。


image.png

但是从截图结果来看此时lock_mode是记录锁,且 lock_data 是 supremum,这又涉及到我的盲区了,难道是最后的记录插入比较特殊?所以不是因为间隙锁被阻塞,而是被最大记录行锁阻塞?

此时把事务A、B都提交了 ,然后我们再执行事务 C:


image.png

image.png

可以看到,此时被阻塞的锁是记录锁+间隙锁(next-key lock),这符合我们的认知和上面的图,因为要插入的数据在 yes 和公众号:yes的练级攻略之间。


小结


在命中索引列的前提下,只会在索引列上加锁。

如果此时在读已提交级别下:

select..for update和update的所查找的记录本身会被加上记录锁,因此这个位置的插入会被阻塞,其他位置的插入则没有影响。

如果此时在可重复读级别下:

select..for update和update的所查找的记录在索引位置前后会被加间隙锁,记录本身加记录锁,因此这些位置的插入会被阻塞,其他位置的插入则没有影响。


最后



分了四个实验大类,一个做了十三个实验。

还是挺有收获的,惊喜就是发现了细节盲区,之后研究一下再出一篇文章。

从实验来看,这里再做个概念性的总结:

  • 锁是作用在索引上的,因此如果能命中二级索引就在二级索引上加锁,不然就得被迫在聚簇索引上加锁。
  • 被迫在聚簇索引上加锁,会导致全表扫描式的加锁。
  • 在可重复读下,不论命中哪个索引,不论是select..for update还是update,只要被扫描到的记录,都会被加锁,不论是否符合条件,在事务提交之后才会释放。
  • 在读提交下,select..for update表现出来的结果是扫描到的记录先加锁,再判断条件,不符合就立马释放,不需要等到事务提交,而 update 的扫描是先判断是否符合条件,符合了才上锁。

声明:以上实验是基于 MySQL 5.7.26 版本,存储引擎为 InnoDB 。

这些实验我之前花了三个工作日晚上做的,由于时间是零散的,导致中间实验出错,期间设置事务隔离级别语句有问题,导致我在错误的前提下做实验,实验结果不断地冲击我的认知,我整个人都快搞崩溃了....

然后周六花了一天的时间重新理了一下,实验图很多,可能看了后面就忘了前面,建议结合着结论来回看,这样对结论会有更深刻的认识,但是有些实验结论我是根据实验现象来推断的,我没有去找相关的官网说明,如有错误,恳请指正,如有疑惑还请自行实验,可以在评论区交流一番。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
27天前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
48 3
|
17天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
20 1
|
24天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
55 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
1月前
|
关系型数据库 MySQL Java
MySQL数据锁:Record Lock,Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎,深入解析三种行锁机制:记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-key Lock)。记录锁锁定索引记录,确保事务唯一修改;间隙锁锁定索引间的间隙,防止新记录插入;临键锁结合两者,锁定范围并记录自身,有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
77 2
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库锁:共享锁和独占锁
本文详细介绍了`InnoDB`存储引擎中的两种行级别锁:共享锁(S锁)与排他锁(X锁)。通过具体示例展示了这两种锁的工作机制及其在`InnoDB`与`MyISAM`引擎中的表现差异。文章还提供了锁的兼容性矩阵,帮助读者更好地理解锁之间的互斥关系。最后总结了两种锁的特点及适用场景。适合希望深入了解`MySQL`并发控制机制的读者阅读。
23 1
|
17天前
|
存储 关系型数据库 MySQL
MySQL锁,锁的到底是什么?
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象,其目的是为了保证数据的一致性、避免冲突,并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
47 0
|
2月前
|
监控 关系型数据库 MySQL
MySQL锁机制与解决死锁问题
MySQL锁机制与解决死锁问题
232 5
|
20天前
|
关系型数据库 MySQL 数据库
mysql锁详解
通过理解并合理运用MySQL中的锁机制,开发者可以有效管理数据库并发访问,平衡性能与数据一致性需求。更多关于MySQL锁的深入探讨和最佳实践,请参考专业的数据库管理资源[[深入MySQL锁机制详解
29 0
|
2月前
|
关系型数据库 MySQL 数据库
Mysql的锁
本文介绍了MySQL中表级锁和行级锁的区别,其中MyISAM仅支持表级锁,而InnoDB支持表级锁和行级锁,默认为行级锁。表级锁锁定整个表,实现简单,资源消耗少,但并发度低;行级锁仅锁定相关记录,减少冲突,提高并发度,但加锁开销大。此外,还介绍了共享锁和排他锁的概念及意向锁的作用。
|
2月前
|
存储 SQL 关系型数据库
MySQL 的锁机制,那么多的锁,该怎么区分?
MySQL 的锁机制,那么多的锁,该怎么区分?
37 0