文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL死锁系列-常见加锁场景分析

2024-12-11 18:46

关注

如下图所示,数据库的隔离等级,SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式,锁类型和锁数量

下面,我们会首先讲解一下隔离等级、不同 SQL 语句 和 当前数据库数据对生成锁影响的基本规则,然后再依次具体 SQL 的加锁场景。

隔离等级对加锁的影响

MySQL 的隔离等级对加锁有影响,所以在分析具体加锁场景时,首先要确定当前的隔离等级

这里说明一下,RC 总是读取记录的最新版本,而 RR 是读取该记录事务开始时的那个版本,虽然这两种读取的版本不同,但是都是快照数据,并不会被写操作阻塞,所以这种读操作称为 快照读(Snapshot Read)

MySQL 还提供了另一种读取方式叫当前读(Current Read),它读的不再是数据的快照版本,而是数据的最新版本,并会对数据加锁,根据语句和加锁的不同,又分成三种情况:

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的:RC 只加记录锁,RR 除了加记录锁,还会加间隙锁,用于解决幻读问题

不同 SQL 语句对加锁的影响

不同的 SQL 语句当然会加不同的锁,总结起来主要分为五种情况:

其中,当前读的 SQL 语句的 where 从句的不同也会影响加锁,包括是否使用索引,索引是否是唯一索引等等。

当前数据对加锁的影响

SQL 语句执行时数据库中的数据也会对加锁产生影响。

比如一条最简单的根据主键进行更新的 SQL 语句,如果主键存在,则只需要对其加记录锁,如果不存在,则需要在加间隙锁。

至于其他非唯一性索引更新或者插入时的加锁也都不同程度的受到现存数据的影响,后续我们会一一说明。

具体场景分析

具体 SQL 场景分析主要借鉴何登成前辈的《MySQL 加锁处理分析》文章和 aneasystone 的系列文章,在他们的基础上进行了总结和整理。

我们使用下面这张 book 表作为实例,其中 id 为主键,ISBN(书号)为二级唯一索引,Author(作者)为二级非唯一索引,score(评分)无索引。

UPDATE 语句加锁分析

下面,我们先来分析 UPDATE 相关 SQL 在使用较为简单 where 从句情况下加锁情况。其中的分析原则也适用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等当前读的语句。

聚簇索引,查询命中

聚簇索引就是 InnoDB 存储引擎下的主键索引,具体可参考《MySQL索引》。

下图展示了使用 UPDATE book SET score = 9.2 WHERE ID = 10 语句命中的情况下在 RC 和 RR 隔离等级下的加锁,两种隔离等级下没有任何区别,都是对 ID = 10 这个索引加排他记录锁。

聚簇索引,查询未命中

下图展示了 UPDATE book SET score = 9.2 WHERE ID = 16 语句未命中时 RR 隔离级别下的加锁情况。

在 RC 隔离等级下,不需要加锁;而在 RR 隔离级别会在 ID = 16 前后两个索引之间加上间隙锁。

值得注意的是,间隙锁和间隙锁之间是互不冲突的,间隙锁唯一的作用就是为了防止其他事务的插入新行,导致幻读,所以加间隙 S 锁和加间隙 X 锁没有任何区别。

二级唯一索引,查询命中

下图展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔离等级下命中时的加锁情况。

在 InnoDB 存储引擎中,二级索引的叶子节点保存着主键索引的值,然后再拿主键索引去获取真正的数据行,所以在这种情况下,二级索引和主键索引都会加排他记录锁。

二级唯一索引,查询未命中

下图展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 语句在 RR 隔离等级下未命中时的加锁情况,RC 隔离等级下该语句未命中不会加锁。

因为 N0008 大于 N0007,所以要锁住 (N0007,正无穷)这段区间,而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 来分别表示记录的上下边界。Infimum 是比该页中任何记录都要小的值,而 Supremum 比该页中最大的记录值还要大,这两条记录在创建页的时候就有了,并且不会删除。

所以,在 N0007 和 Suprenum Record 之间加了间隙锁。

为什么不在主键上也加 GAP 锁呢?欢迎留言说出你的想法。

二级非唯一索引,查询命中

下图展示了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 语句在 RC 隔离等级下命中时的加锁情况。

我们可以看到,在 RC 等级下,二级唯一索引和二级非唯一索引的加锁情况是一致的,都是在涉及的二级索引和对应的主键索引上加上排他记录锁。

但是在 RR 隔离等级下,加锁的情况产生了变化,它不仅对涉及的二级索引和主键索引加了排他记录锁,还在非唯一二级索引上加了三个间隙锁,锁住了两个 Tom 索引值相关的三个范围。

那为什么唯一索引不需要加间隙锁呢?间隙锁的作用是为了解决幻读,防止其他事务插入相同索引值的记录,而唯一索引和主键约束都已经保证了该索引值肯定只有一条记录,所以无需加间隙锁。

需要注意的是,上图虽然画着 4 个记录锁,三个间隙锁,但是实际上间隙锁和它右侧的记录锁会合并成 Next-Key 锁。

所以实际情况有两个 Next-Key 锁,一个间隙锁(Tom60,正无穷)和两个记录锁。

二级非唯一索引,查询未命中

下图展示了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔离等级下未命中的加锁情况,它会在二级索引 Rose 和 Tom 之间加间隙锁。而 RC 隔离等级下不需要加锁。

无索引

当 Where 从句的条件并不使用索引时,则会对全表进行扫描,在 RC 隔离等级下对所有的数据加排他记录锁。在RR 隔离等级下,除了给记录加锁,还会对记录和记录之间加间隙锁。和上边一样,间隙锁会和左侧的记录锁合并成 Next-Key 锁。

下图就是 UPDATE book SET score = 9.2 WHERE score = 22 语句在两种隔离等级下的加锁情况。

聚簇索引,范围查询

上面介绍的场景都是 where 从句的等值查询,而范围查询的加锁又是怎么样的呢?我们慢慢来看。

下图是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔离等级下的加锁情况。

RC 场景下与等值查询类似,只会在涉及的 ID = 10,ID = 18 和 ID = 25 索引上加排他记录锁。

而在 RR 隔离等级下则有所不同,它会加上间隙锁,和对应的记录锁合并称为 Next-Key 锁。除此之外,它还会在(25, 30] 上分别加 Next-Key 锁。这一点是十分特殊的,具体原因还需要再探究。

二级索引,范围查询

下图展示了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 级别下的加锁情况。

修改索引值

UPDATE 语句修改索引值的情况可以分开分析,首先 Where 从句的加锁分析如上文所述,多了一步 Set 部分的加锁。

下图展示了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔离等级下的加锁情况。除了在主键 ID 上进行加锁,还会对二级索引上的 Bob(就值) 和 John(新值) 上进行加锁。

DELETE 语句加锁分析

一般来说,DELETE 的加锁和 SELECT FOR UPDATE 或 UPDATE 并没有太大的差异。

因为,在 MySQL 数据库中,执行 DELETE 语句其实并没有直接删除记录,而是在记录上打上一个删除标记,然后通过后台的一个叫做 purge 的线程来清理。从这一点来看,DELETE 和 UPDATE 确实是非常相像。事实上,DELETE 和 UPDATE 的加锁也几乎是一样的。

INSERT 语句加锁分析

接下来,我们来看一下 Insert 语句的加锁情况。

Insert 语句在两种情况下会加锁:

除了上述情况,Insert 语句的锁都是隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁的机制来减少加锁的数量。

隐式锁的特点是只有在可能发生冲突时才加锁,减少了锁的数量。另外,隐式锁是针对被修改的 B+Tree 记录,因此都是记录类型的锁,不可能是间隙锁或 Next-Key 类型。

具体 Insert 语句的加锁流程如下:

后记

本文中讲解的 SQL 语句都是十分简单的,当 SQL 语句包含多个查询条件时,加锁的分析过程就往往更加复杂。我们需要使用 MySQL 相关的工具进行分析,并且有时甚至需要查询 MySQL 相关的日志信息来了解到底语句加了什么锁或者为什么产生死锁。

 

来源:今日头条内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯