关于MySQL死锁问题的深入分析

2023-08-09 23:04:05 69

前言

如果我们的业务处在一个非常初级的阶段，并发程度比较低，那么我们可以几年都遇不到一次死锁问题的发生，反之，我们业务的并发程度非常高，那么时不时爆出的死锁问题肯定让我们非常挠头。不过在死锁问题发生时，很多没有经验的同学的第一反应就是成为一只鸵鸟：这玩意儿很高深，我也看不懂，听天由命吧，又不是一直发生。其实如果大家认真研读了我们之前写的3篇关于MySQL中语句加锁分析的文章，加上本篇关于死锁日志的分析，那么解决死锁问题应该也不是那么摸不着头脑的事情了。

准备工作

为了故事的顺利发展，我们需要建一个表：

CREATETABLEhero(
idINT,
nameVARCHAR(100),
countryvarchar(100),
PRIMARYKEY(id),
KEYidx_name(name)
)Engine=InnoDBCHARSET=utf8;

我们为hero表的id列创建了聚簇索引，为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄，我们向表中插入一些记录：

INSERTINTOheroVALUES
(1,'l刘备','蜀'),
(3,'z诸葛亮','蜀'),
(8,'c曹操','魏'),
(15,'x荀彧','魏'),
(20,'s孙权','吴');

现在表中的数据就是这样的：

mysql>SELECT*FROMhero;
+----+------------+---------+
|id|name|country|
+----+------------+---------+
|1|l刘备|蜀|
|3|z诸葛亮|蜀|
|8|c曹操|魏|
|15|x荀彧|魏|
|20|s孙权|吴|
+----+------------+---------+
5rowsinset(0.00sec)

准备工作就做完了。

创建死锁情景

我们先创建一个发生死锁的情景，在SessionA和SessionB中分别执行两个事务，具体情况如下：

我们分析一下：

从第③步中可以看出，SessionA中的事务先对hero表聚簇索引的id值为1的记录加了一个X型正经记录锁。
从第④步中可以看出，SessionB中的事务对hero表聚簇索引的id值为3的记录加了一个X型正经记录锁。
从第⑤步中可以看出，SessionA中的事务接着想对hero表聚簇索引的id值为3的记录也加了一个X型正经记录锁，但是与第④步中SessionB中的事务加的锁冲突，所以SessionA进入阻塞状态，等待获取锁。
从第⑥步中可以看出，SessionB中的事务想对hero表聚簇索引的id值为1的记录加了一个X型正经记录锁，但是与第③步中SessionA中的事务加的锁冲突，而此时SessionA和SessionB中的事务循环等待对方持有的锁，死锁发生，被MySQL服务器的死锁检测机制检测到了，所以选择了一个事务进行回滚，并向客户端发送一条消息：

ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

以上是我们从语句加了什么锁的角度出发来进行死锁情况分析的，但是实际应用中我们可能压根儿不知道到底是哪几条语句产生了死锁，我们需要根据MySQL在死锁发生时产生的死锁日志来逆向定位一下到底是什么语句产生了死锁，从而再优化我们的业务。

查看死锁日志

设计InnoDB的大叔给我们提供了SHOWENGINEINNODBSTATUS命令来查看关于InnoDB存储引擎的一些状态信息，其中就包括了系统最近一次发生死锁时的加锁情况。在上边例子中的死锁发生时，我们运行一下这个命令：

mysql>SHOWENGINEINNODBSTATUS\G
...省略了好多其他信息
------------------------
LATESTDETECTEDDEADLOCK
------------------------
2019-06-2013:39:190x70000697e000
***(1)TRANSACTION:
TRANSACTION30477,ACTIVE10secstartingindexread
mysqltablesinuse1,locked1
LOCKWAIT3lockstruct(s),heapsize1160,2rowlock(s)
MySQLthreadid2,OSthreadhandle123145412648960,queryid46localhost127.0.0.1rootstatistics
select*fromherowhereid=3forupdate
***(1)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30477lock_modeXlocksrecbutnotgapwaiting
Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0
0:len4;hex80000003;asc;;
1:len6;hex000000007517;ascu;;
2:len7;hex80000001d0011d;asc;;
3:len10;hex7ae8afb8e8919be4baae;ascz;;
4:len3;hexe89c80;asc;;

***(2)TRANSACTION:
TRANSACTION30478,ACTIVE8secstartingindexread
mysqltablesinuse1,locked1
3lockstruct(s),heapsize1160,2rowlock(s)
MySQLthreadid3,OSthreadhandle123145412927488,queryid47localhost127.0.0.1rootstatistics
select*fromherowhereid=1forupdate
***(2)HOLDSTHELOCK(S):
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30478lock_modeXlocksrecbutnotgap
Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0
0:len4;hex80000003;asc;;
1:len6;hex000000007517;ascu;;
2:len7;hex80000001d0011d;asc;;
3:len10;hex7ae8afb8e8919be4baae;ascz;;
4:len3;hexe89c80;asc;;

***(2)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30478lock_modeXlocksrecbutnotgapwaiting
Recordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits0
0:len4;hex80000001;asc;;
1:len6;hex000000007517;ascu;;
2:len7;hex80000001d00110;asc;;
3:len7;hex6ce58898e5a487;ascl;;
4:len3;hexe89c80;asc;;

***WEROLLBACKTRANSACTION(2)
------------
...省略了好多其他信息

我们只关心最近发生的死锁信息，所以就把以LATESTDETECTEDDEADLOCK这一部分给单独提出来分析一下。下边我们就逐行看一下这个输出的死锁日志都是什么意思：

首先看第一句：

2019-06-2013:39:190x70000697e000

这句话的意思就是死锁发生的时间是：2019-06-2013:39:19，后边的一串十六进制0x70000697e000表示的操作系统为当前session分配的线程的线程id。

然后是关于死锁发生时第一个事务的有关信息：

***(1)TRANSACTION:

#为事务分配的id为30477，事务处于ACTIVE状态已经10秒了，事务现在正在做的操作就是：“startingindexread”
TRANSACTION30477,ACTIVE10secstartingindexread

#此事务使用了1个表，为1个表上了锁（此处不是说为该表加了表锁，只要不是进行一致性读的表，都需要加锁，具体怎么加锁请看加锁语句分析或者小册章节）
mysqltablesinuse1,locked1

#此事务处于LOCKWAIT状态，拥有3个锁结构（2个行锁结构，1个表级别X型意向锁结构，锁结构在小册中重点介绍过），heapsize是为了存储锁结构而申请的内存大小（我们可以忽略），其中有2个行锁的结构
LOCKWAIT3lockstruct(s),heapsize1160,2rowlock(s)

#本事务所在线程的id是2（MySQL自己命名的线程id），该线程在操作系统级别的id就是那一长串数字，当前查询的id为46（MySQL内部使用，可以忽略），还有用户名主机信息
MySQLthreadid2,OSthreadhandle123145412648960,queryid46localhost127.0.0.1rootstatistics

#本事务发生阻塞的语句
select*fromherowhereid=3forupdate

#本事务当前在等待获取的锁：
***(1)WAITINGFORTHISLOCKTOBEGRANTED:

#等待获取的表空间ID为151，页号为3，也就是表hero的PRIMAY索引中的某条记录的锁（n_bits是为了存储本页面的锁信息而分配的一串内存空间，小册中有详细介绍），该锁的类型是X型正经记录锁（recbutnotgap）
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30477lock_modeXlocksrecbutnotgapwaiting

#该记录在页面中的heap_no为2，具体的记录信息如下：
Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

#这是主键值
0:len4;hex80000003;asc;;

#这是trx_id隐藏列
1:len6;hex000000007517;ascu;;

#这是roll_pointer隐藏列
2:len7;hex80000001d0011d;asc;;

#这是name列
3:len10;hex7ae8afb8e8919be4baae;ascz;;

#这是country列
4:len3;hexe89c80;asc;;

从这个信息中可以看出，SessionA中的事务为2条记录生成了锁结构，但是其中有一条记录上的X型正经记录锁（recbutnotgap）并没有获取到，没有获取到锁的这条记录的位置是：表空间ID为151，页号为3，heap_no为2。当然，设计InnoDB的大叔还贴心的给出了这条记录的详细情况，它的主键值为80000003，这其实是InnoDB内部存储使用的格式，其实就代表数字3，也就是该事务在等待获取hero表聚簇索引主键值为3的那条记录的X型正经记录锁。

然后是关于死锁发生时第二个事务的有关信息：

其中的大部分信息我们都已经介绍过了，我们就挑重要的说：

***(2)TRANSACTION:
TRANSACTION30478,ACTIVE8secstartingindexread
mysqltablesinuse1,locked1
3lockstruct(s),heapsize1160,2rowlock(s)
MySQLthreadid3,OSthreadhandle123145412927488,queryid47localhost127.0.0.1rootstatistics
select*fromherowhereid=1forupdate

#表示该事务获取到的锁信息
***(2)HOLDSTHELOCK(S):
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30478lock_modeXlocksrecbutnotgap
Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

#主键值为3
0:len4;hex80000003;asc;;
1:len6;hex000000007517;ascu;;
2:len7;hex80000001d0011d;asc;;
3:len10;hex7ae8afb8e8919be4baae;ascz;;
4:len3;hexe89c80;asc;;

#表示该事务等待获取的锁信息
***(2)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid171pageno3nbits72indexPRIMARYoftable`dahaizi`.`hero`trxid30478lock_modeXlocksrecbutnotgapwaiting
Recordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits0

#主键值为1
0:len4;hex80000001;asc;;
1:len6;hex000000007517;ascu;;
2:len7;hex80000001d00110;asc;;
3:len7;hex6ce58898e5a487;ascl;;
4:len3;hexe89c80;asc;;

从上边的输出可以看出来，SessionB中的事务获取了hero表聚簇索引主键值为3的记录的X型正经记录锁，等待获取hero表聚簇索引主键值为1的记录的X型正经记录锁（隐含的意思就是这个hero表聚簇索引主键值为1的记录的X型正经记录锁已经被SESSIONA中的事务获取到了）。

看最后一部分：

***WEROLLBACKTRANSACTION(2)

最终InnoDB存储引擎决定回滚第2个事务，也就是SessionB中的那个事务。

死锁分析的思路

1、查看死锁日志时，首先看一下发生死锁的事务等待获取锁的语句都是啥。

本例中，发现SESSIONA发生阻塞的语句是：

select*fromherowhereid=3forupdate

SESSIONB发生阻塞的语句是：

select*fromherowhereid=1forupdate

然后切记：到自己的业务代码中找出这两条语句所在事务的其他语句。

2、找到发生死锁的事务中所有的语句之后，对照着事务获取到的锁和正在等待的锁的信息来分析死锁发生过程。

从死锁日志中可以看出来，SESSIONA获取了hero表聚簇索引id值为1的记录的X型正经记录锁（这其实是从SESSIONB正在等待的锁中获取的），查看SESSIONA中的语句，发现是下边这个语句造成的（对照着语句加锁分析那三篇文章）:

select*fromherowhereid=1forupdate;

还有SESSIONB获取了hero表聚簇索引id值为3的记录的X型正经记录锁，查看SESSIONB中的语句，发现是下边这个语句造成的（对照着语句加锁分析那三篇文章）:

select*fromherowhereid=3forupdate;

然后看SESSIONA正在等待hero表聚簇索引id值为3的记录的X型正经记录锁，这个是由于下边这个语句造成的：

select*fromherowhereid=3forupdate;

然后看SESSIONB正在等待hero表聚簇索引id值为1的记录的X型正经记录锁，这个是由于下边这个语句造成的：

select*fromherowhereid=1forupdate;

然后整个死锁形成过程就根据死锁日志给还原出来了。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

关于MySQL死锁问题的深入分析

热门推荐

随机推荐