图文实例解析,InnoDB 存储引擎中行锁的三种算法

前文提到,对于 InnoDB 来说,随时都可以加锁(关于加锁的 SQL 语句这里就不说了,忘记的小伙伴可以翻一下上篇文章),但是并非随时都可以解锁。具体来说,InnoDB 采用的是 两阶段锁定协议(two-phase locking protocol):即在事务执行过程中,随时都可以执行加锁操作,但是 只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁,并且所有的锁是在同一时刻被释放。

并且,行级锁只在存储引擎层实现,而对于 InnoDB 存储引擎来说,行级锁又分三种,或者说有三种行级锁算法:

  • Record Lock:记录锁
  • Gap Lock:间隙锁
  • Next-Key Lock:临键锁

下面,我们来详细解释下这三种行锁算法。

Record Lock 记录锁

顾名思义,记录锁就是为 某行记录加锁,事实上,它封锁的是该行的 索引记录。如果表在建立的时候没有设置任何一个索引,那么这时 InnoDB 存储引擎会使用 ” 隐式的主键” 来进行锁定。

所谓隐式的主键就是指:如果在建表的时候没有指定主键,InnoDB 存储引擎会将第一列非空的列作为主键;如果没有的话会自动生成一列为 6 字节的主键。

那么,既然 Record Lock 是基于索引的,那如果我们的 SQL 语句中的条件导致索引失效(比如使用 or) 或者说条件根本就不涉及索引或者主键,行级锁就将退化为表锁。

Record Lock 示例

先来举个对索引字段进行查询的例子,有数据库如下,id 是主键索引:

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

初始数据是这样的:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

新建两个事务, 先执行事务 T1 的前两行,也就是不要执行 commit

图文实例解析,InnoDB 存储引擎中行锁的三种算法

由于没有执行 commit,所以这个时候事务 T1 没有释放锁,并且锁住了 id = 1 的记录行,此时再来执行事务 2 申请 id = 2 的记录行:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

可以看见,由于锁住的是不同的记录行,所以两个记录锁并没有相互排斥,来看一下现在表中的数据,由于事务 1 还没有 commit,所以应该是只有 id = 2 的 username 被修改了:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

nice,果然。再执行下事务 1 的 commit,id = 1 的 username 也就被修改过来啦。

行锁退化为表锁示例

再来看下没有使用索引的例子:

同样的,新建两个事务, 先执行事务 T1 的前两行,也就是不要执行 commit。我们试图使用 select ... for update 给 username = “user_three” 的记录行加上记录锁,但是由于 username 并非主键也并非索引,所以实际上这里事务 T1 锁住的是整张表:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

由于没有执行 commit,所以这个时候事务 T1 没有释放锁,并且锁住了整张表。此时再来执行事务 2 试图申请 id = 5 的记录锁,你会发现事务 T2 会卡住,最后超时关闭事务:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

两条不同记录拥有相同的索引,会发生锁冲突吗?

这个问题的答案应该很简单吧,上面我们强调过,行锁锁住的是索引,而不是一条记录(只不过我们平常这么说锁住了哪条记录,比较好理解罢了)。所以 如果两个事务分别操作的两条不同记录拥有相同的索引,某个事务会因为行锁被另一个事务占用而发生等待

Gap Lock 间隙锁

这里我先简单提一嘴,下文会详细解释:不同于 Record Lock 是基于唯一索引的,Gap Lock 和 Next-Key Lock 都是基于 非唯一索引的。

并且,不同于 Record Lock 锁定的是某一个索引记录,Gap Lock 和 Next-Key Lock 锁定的都是 一段范围内的索引记录:

select * from test where id between 1 and 10 for update;

对于上述 SQL 语句,所有在 (1,10)区间内( 左开右开)的记录行都会被 Gap Lock 锁住,所有 id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条被操作的索引记录并不会被锁住

注意!这里指的是锁住所有的(1,10)区间内的 id,也就是说即使某个 id 目前并不在我们的表中比如 id = 6 ,如果你想插入一条 id = 6 的新纪录,那对不起,不行。

Next-Key Lock 临键锁

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法, 其主要目的是为了解决幻读问题

例如一个索引有 10,11,13 和 20 这四个值,分别对这个 4 个索引进行加锁操作,那么这四个操作分别对应的 Next-Key Lock 锁住的区间是:

  • (-∞, 10]
  • (10, 11]
  • (11, 13]
  • (13, 20]
  • (20, +∞]

细心的同学应该已经注意到了,和 Gap Lock 的不同之处就在于,Next-Key Lock 锁定的区间是 左开右闭的,也就是说它是 包含当前被操作的索引记录的。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下,行锁默认使用的算法就是 Next-Key Lock。但是, 如果操作的索引是唯一索引或主键,InnoDB 会对 Next-Key Lock 进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。

由于 主键也是一种唯一索引,所以我们可以这么说: Record Lock 是基于唯一索引的,而 Next-Key Lock 是基于非唯一索引的

需要注意的, 当操作的索引为非唯一索引时,InnoDB 会先用 Record Lock 锁住对应的唯一索引,再用 Next-Key Lock 和 Gap Lock 对这个非唯一索引进行处理,而不仅仅是锁住这个非唯一索引。具体地我们举个例子来看下。

Next-Key Lock 示例

假设我们为上面 test 表中新增一个字段,并设置为非唯一索引:

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
  class int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY index_class (class) USING BTREE COMMENT '非唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入一些数据:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

开启一个事务 1 执行如下的操作语句:

select * from test where class = 3 for update;

图文实例解析,InnoDB 存储引擎中行锁的三种算法

在这种情况下,InnoDB 事实上会加上三种行锁( select * ... from update 加的是行级写锁即 X 锁):

1)给主键索引 id = 105 加上 Record Lock

2)对于非唯一索引 class = 3,其加上的是 Next-Key Lock,锁定的范围是 (1,3]

3)另外,特别需要注意的是,InnoDB 存储引擎还会对非唯一索引 class 的 下一个键值加上 Gap Lock(表中 class = 3 的下个键值是 6),所以还有一个 class 索引范围为 (3,6) 的间隙锁

总结下 2)和 3),对于这条 SQL 语句,InnoDB 存储引擎锁定地 class 索引范围是 (1, 6)

下面我们用实践来验证理论,再开启一个事务 2,执行下述的语句:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

不出所料,由于在事务 1 中执行的 SQL 语句已经对主键索引中列 a=105 的记录加上了 X 锁,所以此处再去获取 这个记录的 X 锁会被阻塞住。

再用一个事务来执行下述 SQL 语句:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

主键插入 104 没有任何问题,但是插入的 class 索引值 2 在被锁定的范围 (1,6) 中,因此执行同样会被阻塞住。

经过上面的分析,大家一定能够知道下面的 SQL 语句是可以正常执行的:

图文实例解析,InnoDB 存储引擎中行锁的三种算法

Attention

需要注意的是,Next-Key Lock 降级为 Record Lock 仅存在于操作所有的唯一索引列的情况。 若唯一索引由多个列组成,而操作的仅是多个唯一索引列中的其中一个,那么 InnoDB 存储引擎依然使用 Next-Key Lock 进行锁定

🎉 关注公众号 | 飞天小牛肉,即时获取更新

  • 博主东南大学硕士在读,携程 Java 后台开发暑期实习生,利用课余时间运营一个公众号『 飞天小牛肉 』,2020/12/29 日开通,专注分享计算机基础(数据结构 + 算法 + 计算机网络 + 数据库 + 操作系统 + Linux)、Java 技术栈等相关原创技术好文。本公众号的目的就是 让大家可以快速掌握重点知识,有的放矢。关注公众号第一时间获取文章更新,成长的路上我们一起进步
  • 并推荐个人维护的开源教程类项目:CS-Wiki(Gitee 推荐项目,现已累计 1.8k+ star), 致力打造完善的后端知识体系,在技术的路上少走弯路,欢迎各位小伙伴前来交流学习 ~ 😊
  • 如果各位小伙伴春招秋招没有拿得出手的项目的话,可以参考我写的一个项目「开源社区系统 Echo」Gitee 官方推荐项目,目前已累计 900+ star,基于 SpringBoot + MyBatis + MySQL + Redis + Kafka + Elasticsearch + Spring Security + … 并提供详细的开发文档和配套教程。公众号后台回复 Echo 可以获取配套教程,目前尚在更新中。

Original: https://www.cnblogs.com/cswiki/p/15101808.html
Author: 飞天小牛肉
Title: 图文实例解析,InnoDB 存储引擎中行锁的三种算法

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/575967/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • MySQL实战45讲 14

    14 | count(*)这么慢,我该怎么办? 在开发系统时,您可能经常需要计算表中的行数,例如交易系统中的变动记录总数。 [En] When developing a syste…

    数据库 2023年5月24日
    080
  • English words 930 2022

    low hanging fruit 本文来自博客园,作者:ukyo–BlackJesus,转载请注明原文链接:https://www.cnblogs.com/ukzq/…

    数据库 2023年6月11日
    088
  • 14 在 Java 中,如何跳出当前的多重嵌套循环

    在最外层添加一个标记如A,然后用breakA,即可跳出多重循环 关键字break 使用范围:switch-case,循环结构中 break在循环结构中的作用:结束 当前循环 bre…

    数据库 2023年6月6日
    084
  • MySQL实战45讲 1,2

    01 | 基础架构:一条SQL查询语句是如何执行的? Server 层 所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。其架构模…

    数据库 2023年5月24日
    094
  • @Mapper报错,java.lang.NoClassDefFoundError: org/apache/ibatis/annotations/Mapper

    已解决,可以直接看末尾 @Mapper报错,如图: 查了好多资料,如:修改依赖 把1.2改成1.3后还是未能解决。 换成@MapperScan 之后项目启动报错,如图 找到解决方法…

    数据库 2023年6月11日
    082
  • NO.5 MySQL-笔记

    404. 抱歉,您访问的资源不存在。 可能是URL不正确,或者对应的内容已经被删除,或者处于隐私状态。 [En] It may be that the URL is incorre…

    数据库 2023年5月24日
    076
  • Burpsuite安装SQLmap操作

    Burpsuite安装SQLmap插件步骤: 安装准备: Burpsuite工具、SQLmap工具、python解释器 1.打开burpsuite插件; 2.找到CO2插件; 3….

    数据库 2023年6月9日
    084
  • 如何写出有效的单元测试

    测试不要名不副实避免测试的描述与测试内容不符;测试结果必须精准;测试该失败的时候一定要失败! 测试私有或者受保护的方法解决思路: 将方法变成公共方法; 将方法抽取到新类; 将方法变…

    数据库 2023年6月14日
    091
  • Atlas快速入门

    之前的公司在数据中台的项目上调研决定启用了Atlas作为我们数据血缘管理的工具,让我给大家写了一份Atlas快速入门的文档,所以在这里我将这篇文档以一个纯新手视角的方式再一次优化,…

    数据库 2023年6月11日
    086
  • 垃圾回收算法的原理及应用

    概述 有java开发经历的小伙伴必然对 垃圾回收不陌生。垃圾回收简单来说就是一种自动的内存管…

    数据库 2023年6月11日
    092
  • MDC日志链路设计

    正文 本篇博客主题是MDC(MDC 全称是 Mapped Diagnostic Context,可以粗略的理解成是一个线程安全的存放诊断日志的容器),其具体流程是通过某些标识将整个…

    数据库 2023年6月6日
    086
  • 205. 同构字符串

    给定两个字符串 s 和 t ,判断它们是否是同构的。 如果 s 中的字符可以按某种映射关系替换得到 t ,那么这两个字符串是同构的。 每个出现的字符都应当映射到另一个字符,同时不改…

    数据库 2023年6月16日
    076
  • MySQL扩展

    1、行转列 源数据: 目标数据: 数据准备 — 建表插入数据 drop table if …

    数据库 2023年5月24日
    060
  • HMX-Server-分步式服务器框架(开源+源码)

    (原文地址:http://www.cnblogs.com/hellohuang/p/5492302.html ) 这是一个简单实现有分步式框架,由5个服务进程组成一个服务器,它们分…

    数据库 2023年6月14日
    0104
  • 开源、强大的Linux服务器集群管理工具,比宝塔好用!

    在这之前肯定很多人都接触过Linux管理面板:宝塔,宝塔的确非常方便而且好用,安装也简单,复制粘贴几句命令即可安装完成,且提供免费版。今天呢,民工哥向大家介绍另一个Linux的服务…

    数据库 2023年6月9日
    0149
  • RadonDB MySQL on K8s 2.1.4 发布

    RadonDB MySQL Kubernetes 于 4 月 7 日正式发布新版本 2.1.4。该版本主要对可用性进行了优化,新增中英文文档,并修复一些问题。 致谢 首先感谢 @a…

    数据库 2023年6月11日
    069
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球