一次Mysql死锁排查过程的全纪录
作者:daisy 发布时间:2024-01-16 20:35:53
前言
之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多。虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的。
PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接。
死锁起因
先介绍一下数据库和表情况,因为涉及到公司内部真是的数据,所以以下都做了模拟,不会影响具体的分析。
我们采用的是5.5版本的mysql数据库,事务隔离级别是默认的RR(Repeatable-Read),采用innodb引擎。假设存在test表:
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;
表的结构很简单,一个主键id,另一个唯一索引a。表里的数据如下:
mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 rows in set (0.00 sec)
出现死锁的操作如下:
步骤 | 事务1 | 事务2 |
---|---|---|
1 | begin | |
2 | delete from test where a = 2; | |
3 | begin | |
4 | delete from test where a = 2; (事务1卡住) | |
5 | 提示出现死锁:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | insert into test (id, a) values (10, 2); |
然后我们可以通过SHOW ENGINE INNODB STATUS;
来查看死锁日志:
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)
分析
阅读死锁日志
遇到死锁,第一步就是阅读死锁日志。死锁日志通常分为两部分,上半部分说明了事务1在等待什么锁:
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
从日志里我们可以看到事务1当前正在执行delete from test where a = 2
,该条语句正在申请索引a的X锁,所以提示lock_mode X waiting
。
然后日志的下半部分说明了事务2当前持有的锁以及等待的锁:
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
从日志的HOLDS THE LOCKS(S)
块中我们可以看到事务2持有索引a的X锁,并且是记录锁(Record Lock)。该锁是通过事务2在步骤2执行的delete语句申请的。由于是RR隔离模式下的基于唯一索引的等值查询(Where a = 2),所以会申请一个记录锁,而非next-key锁。
从日志的WAITING FOR THIS LOCK TO BE GRANTED
块中我们可以看到事务2正在申请S锁,也就是共享锁。该锁是insert into test (id,a) values (10,2)语句申请的。insert语句在普通情况下是会申请排他锁,也就是X锁,但是这里出现了S锁。这是因为a字段是一个唯一索引,所以insert语句会在插入前进行一次duplicate key
的检查,为了使这次检查成功,需要申请S锁防止其他事务对a字段进行修改。
那么为什么该S锁会失败呢?这是对同一个字段的锁的申请是需要排队的。S锁前面还有一个未申请成功的X锁,所以S锁必须等待,所以形成了循环等待,死锁出现了。
通过阅读死锁日志,我们可以清楚地知道两个事务形成了怎样的循环等待,再加以分析,就可以逆向推断出循环等待的成因,也就是死锁形成的原因。
死锁形成流程图
为了让大家更好地理解死锁形成的原因,我们再通过表格的形式阐述死锁形成的流程:
步骤 | 事务1 | 事务2 |
---|---|---|
1 | begin | |
2 | delete from test where a = 2; 执行成功,事务2占有a=2下的X锁,类型为记录锁。 | |
3 | begin | |
4 | delete from test where a = 2; 事务1希望申请a=2下的X锁,但是由于事务2已经申请了一把X锁,两把X锁互斥,所以X锁申请进入锁请求队列。 | |
5 | 出现死锁,事务1权重较小,所以被选择回滚(成为牺牲品)。 | insert into test (id, a) values (10, 2); 由于a字段建立了唯一索引,所以需要申请S锁以便检查duplicate key,由于插入的a的值还是2,所以排在X锁后面。但是前面的X锁的申请只有在事务2commit或者rollback之后才能成功,此时形成了循环等待,死锁产生。 |
拓展
在排查死锁的过程中,有个同事还发现了上述场景会产生另一种死锁,该场景无法通过手工复现,只有高并发场景下才有可能复现。
该死锁对应的日志这里就不贴出了,与上一个死锁的核心差别是事务2等待的锁从S锁换成了X锁,也就是lock_mode X locks gap before rec insert intention waiting
。
我们还是通过表格来详细说明该死锁产生的流程:
步骤 | 事务1 | 事务2 |
---|---|---|
1 | begin | |
2 | delete from test where a = 2; 执行成功,事务2占有a=2下的X锁,类型为记录锁。 | |
3 | begin | |
4 | 【insert第1阶段】insert into test (id, a) values (10, 2); 事务2申请S锁进行duplicate key进行检查。检查成功。 | |
5 | delete from test where a = 2; 事务1希望申请a=2下的X锁,但是由于事务2已经申请了一把X锁,两把X锁互斥,所以X锁申请进入锁请求队列。 | |
6 | 出现死锁,事务1权重较小,所以被选择回滚(成为牺牲品)。 | 【insert第2阶段】insert into test (id, a) values (10, 2); 事务2开始插入数据,S锁升级为X锁,类型为insert intention。同理,X锁进入队列排队,形成循环等待,死锁产生。 |
总结
排查死锁时,首先需要根据死锁日志分析循环等待的场景,然后根据当前各个事务执行的SQL分析出加锁类型以及顺序,逆向推断出如何形成循环等待,这样就能找到死锁产生的原因了。
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,上述分析都是基于经验的推断,希望其他小伙伴们能够指出当中的错误以及不足指出,谢谢大家对脚本之家的支持。
来源:http://www.kissyu.org/2017/02/19/记录一次Mysql死锁排查过程/
猜你喜欢
- 一直用pycharm写代码一直用anaconda管理python环境但是今天我居然发现我不会更改pycharm当前的运行环境到我新建的ana
- 导语前段时间不是制作了一款升级版本五子棋的嘛!但是居然有粉丝私信我说:“准备拿到代码玩一下ok过去了!太难了准备放收藏夹落灰q@q~”所噶,
- 十要:第一:要认真规划和分析。这是网页设计灵魂工作。创建站点之前,要明确你的网站主要针对哪些访问者,为哪些用户服务,要把握准主页题材第二:网
- 前言本文主要介绍的是关于Django验证码生成与使用的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍:方法如下:1、基于PIL生
- MySQL5.0版本的安装图解教程是给新手学习的,当前mysql5.0.96是最新的稳定版本。mysql 下载地址 https://www.
- 今天是边复习边创作博客的第三天,我今年大二,我们专业开的有这门课程,因为喜欢所以更加认真学习,本以为没人看呢,看了后台浏览量让我更加认真创作
- 如何使用ADO 2x Command 对象读取数据?具体的读数据代码如下:Cmd = CType(EC.Example1
- 源起我本想删写一小段代码用于删除一串字符串中的连续重复的指定字符,可能也是长时间不写代码,而且有的时候写代码只途快,很多基础知识都忘光了。我
- 1、Git的基本操作流程初始化一个本地版本库,每个版本库仅需要执行一次。将中央版本库内容克隆到本地版本库,每个客户机仅需要执行一次。添加指定
- 1 简介在使用Git管理自己的代码版本时,由于编译生成的中间文件,Git使用SHA-1算法来对文件进行加密,进而得出来一个40位的十六进制加
- 在上篇博客中,我们已经实现了水平投影和垂直投影图的绘制。接下来,我们可以根据获得的投影数据进行图像的分割,该法用于文本分割较多,所以此处依然
- 简介 本次项目登录注册验证是对之前学习知识点的加深学习,这次项目的练习的知识点有函数、判断语句、循环语句、文件操作等。项目流程 运行代码之后
- 这篇文章主要介绍了Python如何计算语句执行时间,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可
- 之一:torchvision 中包含了很多预训练好的模型,这样就使得 fine-tune 非常容易。本文主要介绍如何 fine-tune t
- 前言最近在开发项目时遇到了发现一个问题,gorm虽然可以自动帮你维护 created_at、updated_at、deleted_at这些关
- 前言很多时候开发者需要删除文件。可能是他错误地创建了文件,或者不再需要该文件。无论出于何种原因,都有一些方法可以通过Python来删除文件,
- 插件是干什么用的详细地址1.下载rainbow-fart目前在vscode中还不能搜索到此插件,只能从官网中下载,官方地址:地址2.安装ra
- 还有多少耿直boy和我一样在等待微信官方送上一顶圣诞帽?最后知道真相的我眼泪掉下来……(还蒙在鼓里的同学请在微信最上方的搜索栏自行搜索『圣诞
- 本文实例为大家分享了python绘制温度变化雷达图的具体代码,供大家参考,具体内容如下假设某天某地每三个小时取样的气温为针对温度变化趋势绘制
- 和其他语言不一样,传递参数的时候,python不允许程序员选择采用传值还是传引用。Python参数传递采用的肯定是“传对象引用”的方式。实际