这是十三个实验,扫到了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 。

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

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


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
5月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
125 0
|
7月前
|
数据可视化 关系型数据库 MySQL
ELK实现nginx、mysql、http的日志可视化实验
通过本文的步骤,你可以成功配置ELK(Elasticsearch, Logstash, Kibana)来实现nginx、mysql和http日志的可视化。通过Kibana,你可以直观地查看和分析日志数据,从而更好地监控和管理系统。希望这些步骤能帮助你在实际项目中有效地利用ELK来处理日志数据。
538 90
|
12月前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
191 3
|
8月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
332 25
|
9月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
12月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
614 1
|
12月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
403 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
存储 关系型数据库 MySQL
MySQL数据库锁:共享锁和独占锁
本文详细介绍了`InnoDB`存储引擎中的两种行级别锁:共享锁(S锁)与排他锁(X锁)。通过具体示例展示了这两种锁的工作机制及其在`InnoDB`与`MyISAM`引擎中的表现差异。文章还提供了锁的兼容性矩阵,帮助读者更好地理解锁之间的互斥关系。最后总结了两种锁的特点及适用场景。适合希望深入了解`MySQL`并发控制机制的读者阅读。
435 1
|
12月前
|
存储 关系型数据库 MySQL
MySQL锁,锁的到底是什么?
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象,其目的是为了保证数据的一致性、避免冲突,并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
312 0
|
12月前
|
关系型数据库 MySQL 数据库
mysql锁详解
通过理解并合理运用MySQL中的锁机制,开发者可以有效管理数据库并发访问,平衡性能与数据一致性需求。更多关于MySQL锁的深入探讨和最佳实践,请参考专业的数据库管理资源[[深入MySQL锁机制详解
224 0

推荐镜像

更多