MySQL实战45讲——30答疑文章(二):用动态的观点看加锁

news/2024/4/13 11:14:21/文章来源:https://blog.csdn.net/meser88/article/details/136457913

目录

不等号条件里的等值查询

等值查询的过程

怎么看死锁?

怎么看锁等待?

update 的例子

小结

上期问题时间

提示


文章摘自林晓斌老师《MySQL实战45讲》,作为笔记而用,故有加一些自己的理解。在第[20]和[21]篇文章中,我和你介绍了 InnoDB 的间隙锁、next-key lock,以及加锁规则。在这两篇文章的评论区,出现了很多高质量的留言。我觉得通过分析这些问题,可以帮助你加深对加锁规则的理解。

所以,我就从中挑选了几个有代表性的问题,构成了今天这篇答疑文章的主题,即:用动态的观点看加锁。 为了方便你理解,我们再一起复习一下加锁规则。这个规则中,包含了两个"原则”、两个"优化"和一个"bug”:* 原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。 原则 2:查找过程中访问到的对象才会加锁。 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

接下来,我们的讨论还是基于下面这个表 t:

CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `c` (`c`)
) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

标题不等号指>和<,不是!=

有同学对"等值查询"提出了疑问:等值查询和"遍历"有什么区别?为什么我们文章的例子里面,where 条件是不等号,这个过程里也有等值查询?

我们一起来看下这个例子,分析一下这条查询语句的加锁范围:

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则,我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10] 和 (10, 15)。也就是说,id=15 这一行,并没有被加上行锁。为什么呢?

我们说加锁单位是 next-key lock,都是前开后闭区间,但是这里用到了优化 2,即索引上的等值查询,向右遍历的时候 id=15 不满足条件,所以 next-key lock 退化为了间隙锁 (10, 15)。(等值查询无匹配则退化成间隙锁,即id=12不存在,所以在存在的两个索引10和15之间是间隙锁)

这里说的不好理解,我重新整理下,默认加锁是next-key lock,符合条件的索引记录都加上next-key lock,所以这里无论降序还是升序,因为c=10匹配到了,所以一定会加(5,10]

先说降序,我们把数据理解为左大右小,然后向右遍历,id<12,向右遍历,这里id=12无匹配记录,根据等值不匹配原则,这里加(10, 15)间隙锁,id>9,向右遍历到第一个不满足的索引即id=5,加next-key lock (0,5],故加锁范围是 (0,5]、(5,10] 和 (10, 15)

再说升序(默认) select * from t where id>9 and id<12 for update;数据右小左大,向右遍历,id>9,9属于等值不匹配,故加(5,10)间隙锁,因为10满足条件,加锁(5,10],遍历到 id<12,向右遍历第一个不满足条件的值即id=15,加(10, 15],故加锁范围是 (5,10] 和 (10, 15]

重新整理下规则:

  1. 规则1,等值不匹配(就是数据库没这个值),在这个不匹配记录所在的相邻两个记录之间加间隙锁,如id=9,不存在即不匹配,9在5和10之间,故加了(5,10)间隙锁,规则对主键/唯一/普通索引都适用
  2. 规则2,向右遍历到第一个不满足的索引加next-key lock

根据这个原则更好理解为啥降序没锁id=15,而升序又锁了,降序锁了(0,5],而升序又没锁。所谓向右遍历,准确说是按索引的顺序遍历,降序是从大到小遍历,所以要看小的那端的第一个不满足的记录,升序是从小到大遍历,所以看的是大的那头的第一个不匹配记录。

但是,我们的查询语句中 where 条件是大于号和小于号,这里的"等值查询"又是从哪里来的呢?

要知道,加锁动作是发生在语句执行过程中的,所以你在分析加锁行为的时候,要从索引上的数据结构开始。这里,我再把这个过程拆解一下。

如图 1 所示,是这个表的索引 id 的示意图。

图 1 索引 id 示意图

  1. 首先这个查询语句的语义是 order by id desc,要拿到满足条件的所有行,优化器必须先找到"第一个 id<12 的值”。
  2. 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找到 id=12 的这个值,只是最终没找到,但找到了 (10,15) 这个间隙。
  3. 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,所以会加一个 next-key lock (0,5]。

也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是"等值查询"的方法

等值查询的过程

与上面这个例子对应的,是 @发条橙子同学提出的问题:下面这个语句的加锁范围是什么?

begin;
select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是 in,我们先来看这条语句的 explain 结果。 

img

图 2 in 语句的 explain 结果

可以看到,这条 in 语句使用了索引 c 并且 rows=3,说明这三个值都是通过 B+ 树搜索定位的。

在查找 c=5 的时候,先锁住了 (0,5]。但是因为 c 不是唯一索引,为了确认还有没有别的记录 c=5,就要向右遍历,找到 c=10 才确认没有了,这个过程满足优化 2,所以加了间隙锁 (5,10)。

同样的,执行 c=10 这个逻辑的时候,加锁的范围是 (5,10] 和 (10,15);执行 c=20 这个逻辑的时候,加锁的范围是 (15,20] 和 (20,25)。

通过这个分析,我们可以知道,这条语句在索引 c 上加的三个记录锁的顺序是:先加 c=5 的记录锁,再加 c=10 的记录锁,最后加 c=20 的记录锁。

你可能会说,这个加锁范围,不就是从 (5,25) 中去掉 c=15 的行锁吗?为什么这么麻烦地分段说呢?

因为我要跟你强调这个过程:这些锁是"在执行过程中一个一个加的”,而不是一次性加上去的。

理解了这个加锁过程之后,我们就可以来分析下面例子中的死锁问题了。

如果同时有另外一个语句,是这么写的:

select id from t where c in(5,20,10) order by c desc for update;

此时的加锁范围,又是什么呢?

我们现在都知道间隙锁是不互锁的,但是这两条语句都会在索引 c 上的 c=5、10、20 这三行记录上加记录锁。

这里你需要注意一下,由于语句里面是 order by c desc, 这三个记录锁的加锁顺序,是先锁 c=20,然后 c=10,最后是 c=5。

也就是说,这两条语句要加锁相同的资源,但是加锁顺序相反。当这两条语句并发执行的时候,就可能出现死锁。

关于死锁的信息,MySQL 只保留了最后一个死锁的现场,但这个现场还是不完备的。

有同学在评论区留言到,希望我能展开一下怎么看死锁。现在,我就来简单分析一下上面这个例子的死锁现场。

怎么看死锁?

图 3 是在出现死锁后,执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息,有一节 LATESTDETECTED DEADLOCK,就是记录的最后一次死锁信息。 

img

图 3 死锁现场

我们来看看这图中的几个关键信息。

  1. 这个结果分成三部分: (1) TRANSACTION,是第一个事务的信息; (2) TRANSACTION,是第二个事务的信息; WE ROLL BACK TRANSACTION (1),是最终的处理结果,表示回滚了第一个事务。
  2. 第一个事务的信息中: WAITING FOR THIS LOCK TO BE GRANTED,表示的是这个事务在等待的锁信息; index c of table test.t,说明在等的是表 t 的索引 c 上面的锁; lock mode S waiting 表示这个语句要自己加一个读锁,当前的状态是等待中; Record lock 说明这是一个记录锁; n_fields 2 表示这个记录是两列,也就是字段 c 和主键字段 id; 0: len 4; hex 0000000a; asc ;; 是第一个字段,也就是 c。值是十六进制 a,也就是 10; 1: len 4; hex 0000000a; asc ;; 是第二个字段,也就是主键 id,值也是 10; 这两行里面的 asc 表示的是,接下来要打印出值里面的"可打印字符”,但 10 不是可打印字符,因此就显示空格。 第一个事务信息就只显示出了等锁的状态,在等待 (c=10,id=10) 这一行的锁。 当然你是知道的,既然出现死锁了,就表示这个事务也占有别的锁,但是没有显示出来。别着急,我们从第二个事务的信息中推导出来。
  3. 第二个事务显示的信息要多一些: " HOLDS THE LOCK(S)“用来显示这个事务持有哪些锁; index c of table test.t 表示锁是在表 t 的索引 c 上; hex 0000000a 和 hex 00000014 表示这个事务持有 c=10 和 c=20 这两个记录锁; WAITING FOR THIS LOCK TO BE GRANTED,表示在等 (c=5,id=5) 这个记录锁。

从上面这些信息中,我们就知道:

  1. “lock in share mode"的这条语句,持有 c=5 的记录锁,在等 c=10 的锁;
  2. “for update"这个语句,持有 c=20 和 c=10 的记录锁,在等 c=5 的记录锁。

因此导致了死锁。这里,我们可以得到两个结论:

  1. 由于锁是一个个加的,要避免死锁,对同一组资源,要按照尽量相同的顺序访问;
  2. 在发生死锁的时刻,for update 这条语句占有的资源更多,回滚成本更大,所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句,来回滚。

怎么看锁等待?

看完死锁,我们再来看一个锁等待的例子。

在第 21 篇文章的评论区,@Geek_9ca34e 同学做了一个有趣验证,我把复现步骤列出来:

img

图 4 delete 导致间隙变化

可以看到,由于 session A 并没有锁住 c=10 这个记录,所以 session B 删除 id=10 这一行是可以的。但是之后,session B 再想 insert id=10 这一行回去就不行了。

现在我们一起看一下此时 show engine innodb status 的结果,看看能不能给我们一些提示。锁信息是在这个命令输出结果的 TRANSACTIONS 这一节。你可以在文稿中看到这张图片 

img

图 5 锁等待信息

我们来看几个关键信息。

  1. index PRIMARY of table test.t ,表示这个语句被锁住是因为表 t 主键上的某个锁。
  2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息: insert intention 表示当前线程准备插入一个记录,这是一个插入意向锁。为了便于理解,你可以认为它就是这个插入动作本身。 gap before rec 表示这是一个间隙锁,而不是记录锁。
  3. 那么这个 gap 是在哪个记录之前的呢?接下来的 0~4 这 5 行的内容就是这个记录的信息。
  4. n_fields 5 也表示了,这一个记录有 5 列: 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段,十六进制 f 就是 id=15。所以,这时我们就知道了,这个间隙就是 id=15 之前的,因为 id=10 已经不存在了,它表示的就是 (5,15)。 1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id,表示最后修改这一行的是 trx id 为 1299 的事务。 2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到,这里的 acs 后面有显示内容 (% 和 4),这是因为刚好这个字节是可打印字符。 后面两列是 c 和 d 的值,都是 15。

因此,我们就知道了,由于 delete 操作把 id=10 这一行删掉了,原来的两个间隙 (5,10)、(10,15)变成了一个 (5,15)。

说到这里,你可以联合起来再思考一下这两个现象之间的关联:

  1. session A 执行完 select 语句后,什么都没做,但它加锁的范围突然"变大"了;
  2. 第 21 篇文章的课后思考题,当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候,要把 (5, 10] 锁起来。

也就是说,所谓"间隙”,其实根本就是由"这个间隙右边的那个记录"定义的。(因为锁是加在索引上,而索引是基于一条一条记录的,注意这里的"这个间隙右边的那个记录"和“向左扫描到c=10”,统一按根据索引顺序扫描到边界时的第一个不满足条件的记录来理解,不然一会向右一会又是向左扫描很容易让人晕)

update 的例子

看过了 insert 和 delete 的加锁例子,我们再来看一个 update 语句的案例。在留言区中 @信信 同学做了这个试验:

img

图 6 update 的例子

你可以自己分析一下,session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25] 和 (25,supremum]。

注意:根据 c>5 查到的第一个记录是 c=10,因此不会加 (0,5] 这个 next-key lock。

之后 session B 的第一个 update 语句,要把 c=5 改成 c=1,你可以理解为两步:

  1. 插入 (c=1, id=5) 这个记录;
  2. 删除 (c=5, id=5) 这个记录。

按照我们上一节说的,索引 c 上 间隙是由这个间隙右边的记录,也就是 c=10 定义的。所以通过这个操作,session A 的加锁范围变成了图 7 所示的样子:

图 7 session B 修改后, session A 的加锁范围

好,接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了,一样地可以拆成两步:

  1. 插入 (c=5, id=5) 这个记录;
  2. 删除 (c=1, id=5) 这个记录。

第一步试图在已经加了间隙锁的 (1,10) 中插入数据,所以就被堵住了。

小结

今天这篇文章,我用前面[第 20]和[第 21 篇]文章评论区的几个问题,再次跟你复习了加锁规则。并且,我和你重点说明了,分析加锁范围时,一定要配合语句执行逻辑来进行。

在我看来,每个想认真了解 MySQL 原理的同学,应该都要能够做到:通过 explain 的结果,就能够脑补出一个 SQL 语句的执行流程。达到这样的程度,才算是对索引组织表、索引、锁的概念有了比较清晰的认识。你同样也可以用这个方法,来验证自己对这些知识点的掌握程度。

在分析这些加锁规则的过程中,我也顺便跟你介绍了怎么看 show engine innodb status 输出结果中的事务信息和死锁信息,希望这些内容对你以后分析现场能有所帮助。

老规矩,即便是答疑文章,我也还是要留一个课后问题给你的。

上面我们提到一个很重要的点:所谓"间隙”,其实根本就是由"这个间隙右边的那个记录"定义的。

那么,一个空表有间隙吗?这个间隙是由谁定义的?你怎么验证这个结论呢?

你可以把你关于分析和验证方法写在留言区,我会在下一篇文章的末尾和你讨论这个问题。感谢你的收听,也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间

我在上一篇文章最后留给的问题,是分享一下你关于业务监控的处理经验。

在这篇文章的评论区,很多同学都分享了不错的经验。这里,我就选择几个比较典型的留言,和你分享吧: @老杨同志 回答得很详细。他的主要思路就是关于服务状态和服务质量的监控。其中,服务状态的监控,一般都可以用外部系统来实现;而服务的质量的监控,就要通过接口的响应时间来统计。 @Ryoma 同学,提到服务中使用了 healthCheck 来检测,其实跟我们文中提到的 select 1 的模式类似。 @强哥 同学,按照监控的对象,将监控分成了基础监控、服务监控和业务监控,并分享了每种监控需要关注的对象。

提示

分析文章提到的案例,请先看懂:MySQL行锁加锁规则之等值查询

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_996998.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

链路聚合+VRRP

链路聚合---作用&#xff1a; 将多条链路聚合为一条逻辑链路&#xff0c;起到叠加带宽的作用 1.通道对端必须是一个设备 2.聚合的链路带宽必须一致 3.聚合的链路配置必须一致---华为设备为了保证聚合的链路配置一致&#xff0c;限制如果聚合的链路存在配 置&#xff0c;则不…

使用 Docker 部署 MrDoc 在线文档管理系统

1&#xff09;MrDoc 介绍 MrDoc 简介 MrDoc 觅思文档&#xff1a;https://mrdoc.pro/ MrDoc 使用手册&#xff1a;https://doc.mrdoc.pro/p/user-guide/ MrDoc 可以创建各类私有化部署的文档应用。你可以使用它进行知识管理、构建团队文库、制作产品手册以及在线教程等。 Mr…

存储架构 NAS 与 SAN:有什么区别?

SAN&#xff08;Storage Area Network&#xff09;和NAS&#xff08;Network Attached Storage&#xff09;是两种存储架构&#xff0c;它们在数据存储和管理方面有着不同的设计理念和应用场景。SAN通常将存储设备连接到一个独立的高速网络&#xff0c;而NAS则通过普通的网络协…

Java:JVM基础

文章目录 参考JVM内存区域程序计数器虚拟机栈本地方法栈堆方法区符号引用与直接引用运行时常量池字符串常量池直接内存 Hotspot虚拟机对象创建过程虚拟机对象的内存布局对象访问 class文件结构 类加载过程加载验证准备解析初始化使用卸载 参考 JavaGuide JVM内存区域 程序计数…

小白在VMware Workstation Pro上安装部署SinoDB V16.8

一、安装环境说明 CPU&#xff1a;2核或以上&#xff0c;内存&#xff1a;2G或以上&#xff1b;磁盘10G或以上&#xff1b;网卡&#xff1a;千兆 1.1检查服务器内存大小 命令&#xff1a;free -m 1.2检查服务器磁盘空间大小 命令&#xff1a;df -h 1.3检查服务器网络配置信息 命…

antvX6 - Vue自定义节点,并实现多种画布操作,拖拽、缩放、连线、双击、检索等等

一、 首先 antv x6 分为两个版本 低版本和高版本 我这里是使用的2.0版本 并且搭配了相关插件 例如&#xff1a;画布的图形变换、地图等 个人推荐 2.0版本&#xff0c;高版本配置多&#xff0c;可使用相关插件多&#xff0c;但是文档描述小&#xff0c;仍在更新&#xff0c; 低…

(正规api接口代发布权限)短视频账号矩阵系统实现开发--技术全自动化saas营销链路生态

短视频账号矩阵系统实现开发--技术全自动化saas营销链路生态源头开发&#xff08;本篇禁止抄袭复刻&#xff09; 一、短视频矩阵系统开发者架构 云罗短视频矩阵系统saas化系统&#xff0c;开发层将在CAP原则基础上使用分布式架构,对此网站的整体架构采用了基于B/S三层架构模式…

R语言数据可视化之美专业图表绘制指南(增强版):第1章 R语言编程与绘图基础

第1章 R语言编程与绘图基础 目录 第1章 R语言编程与绘图基础前言1.1 学术图表的基本概念1.1.1 学术图表的基本作用1.1.2基本类别1.1.3 学术图表的绘制原则 1.2 你为什么要选择R1.3 安装 前言 这是我第一次在博客里展示学习中国作者的教材的笔记。我选择这本书的依据是作者同时…

input输入框的23中类型

HTML 的 <input> 元素支持多种类型&#xff0c;这些类型决定了用户如何与表单控件进行交互。以下是 HTML5 中 <input> 元素的 23 种类型&#xff0c;以及每种类型的代码示例和效果图的描述&#xff08;请注意&#xff0c;由于文本的限制&#xff0c;我无法直接在这…

STM32day2

1.思维导图 个人暂时的学后感&#xff0c;不一定对&#xff0c;没什么东西&#xff0c;为做项目奔波中。。。1.使用ADC采样光敏电阻数值&#xff0c;如何根据这个数值调节LED灯亮度。 while (1){/* USER CODE END WHILE *//* USER CODE BEGIN 3 */adc_val HAL_ADC_GetValue(&a…

Hello C++ (c++是什么/c++怎么学/c++推荐书籍)

引言 其实C基础语法基本上已经学完&#xff0c;早就想开始写C的博客了&#xff0c;却因为其他各种事情一直没开始。原计划是想讲Linux系统虚拟机安装的&#xff0c;后来考虑了一下还是算了&#xff0c;等Linux学到一定程度再开始相关博客的写作和发表吧。今天写博客想给C开个头…

JS函数

目录 1.Function声明 2.匿名函数 3.函数表达式 4.箭头函数 5.构造函数 个人版JS函数使用&#xff1a; 函数的声明&#xff1a;函数如果有return则返回的是 return 后面的值&#xff0c;如果函数没有有return 声明方式一&#xff1a; 声明方式二&#xff1a;变量名声明…

Java ElasticSearch面试题

Java ES-ElasticSearch面试题 前言1、ElasticSearch是什么&#xff1f;2. 说说你们公司ES的集群架构&#xff0c;索引数据大小&#xff0c;分片有多少 &#xff1f;3. ES的倒排索引是什么&#xff1f;4. ES是如何实现 master 选举的?5. 描述一下 ES索引文档的过程&#xff1a;…

STM32CubeMX学习笔记15---CAN总线

1、CAN简介 CAN总线网络的结构有闭环和开环两种形式 闭环结构的CAN总线网络&#xff0c;总线两端各连接一个1202的电阻。这种CAN总线网络由ISO11898标准定义&#xff0c;是高速、短距离的CAN网络&#xff0c;通信速率为125kbit/s到1Mbit/s。在1Mbit/s通信速率时&#x…

基于springboot的作业管理系统论文

摘 要 使用旧方法对作业管理信息进行系统化管理已经不再让人们信赖了&#xff0c;把现在的网络信息技术运用在作业管理信息的管理上面可以解决许多信息管理上面的难题&#xff0c;比如处理数据时间很长&#xff0c;数据存在错误不能及时纠正等问题。 这次开发的作业管理系统有…

记录一次自己的服务器迁移过程

记录一次自己的服务器迁移过程 记录一次自己的服务器迁移过程 前言目前项目的部署方式开始迁移 提前准备设置安全组开始初始化安装 docker尝试部署数据库迁移 结尾一些问题 为什么中间没有配置 https?关于数据库备份 前言 最近阿里云发动了史上最大力度价格战&#xff0c…

Git小册-笔记迁移

Git简介 Git是目前世界上最先进的分布式版本控制系统&#xff08;没有之一&#xff09;。 所有的版本控制系统&#xff0c;其实只能跟踪文本文件的改动&#xff0c;比如TXT文件&#xff0c;网页&#xff0c;所有的程序代码等等&#xff0c;Git也不例外。版本控制系统可以告诉…

GB 2312字符集:中文编码的基石

title: GB 2312字符集&#xff1a;中文编码的基石 date: 2024/3/7 19:26:00 updated: 2024/3/7 19:26:00 tags: GB2312编码中文字符集双字节编码区位码规则兼容性问题存储空间优化文档处理应用 一、GB 2312字符集的背景 GB 2312字符集是中国国家标准委员会于1980年发布的一种…

【JavaEE初阶 -- 计算机核心工作机制】

这里写目录标题 1.冯诺依曼体系2.CPU是怎么构成的3.指令表4.CPU执行代码的方式5.CPU小结&#xff1a;6.编程语言和操作系统7. 进程/任务&#xff08;Process/Task&#xff09;8.进程在系统中是如何管理的9. CPU分配 -- 进程调度10.内存分配 -- 内存管理11.进程间通信 1.冯诺依曼…

SpringBoot【问题 05】PostgreSQL数据库启用SSL后使用默认配置进行数据库连接(Navicat工具与Java程序)

官网SSL说明&#xff1a;https://www.postgresql.org/docs/9.1/libpq-ssl.html 1.配置 1.1 文件 使用SSL需要的4个文件&#xff0c;名称要一致&#xff1a; 客户端密钥&#xff1a;postgresql.keyJava客户端密钥&#xff1a;postgresql.pk8客户端证书&#xff1a;postgresq…