记录一次MySQL死锁的分析与解决过程

以你之姓@ 2023-01-07 14:30 213阅读 0赞

[原文][Link 1]  
一、问题描述  
上周开发过程中，线下环境遇到一个死锁问题，借此机会正好分析下MySQL死锁的原因和解决方案，本篇文章会带你去如何查看死锁日志和分析、解决。  
这里写图片描述  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70]

二、MySQL死锁介绍  
1、MySQL 锁种类  
MySQL InnoDB存储引擎提供了如下几种锁：

（1）共享/排他锁（S/X锁）

共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行）；  
排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（即写写/写读互斥）；  
（2）意向锁（Intention Lock）  
意向锁是一种表级别的锁，意味着事务在更细的粒度上进行加锁。

意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；  
意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；  
举个例子，事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。

（3）插入意向锁（Insert Intention Lock）  
插入意向锁是间隙锁的一种，专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此；  
举个例子：在可重复读隔离级别下，对PK ID为10-20的数据进行操作：  
事务1在10-20的记录中插入了一行：  
insert into table value(11, xx)  
事务2在10-20的记录中插入了一行：  
insert into table value(12, xx)  
由于两条插入的记录不冲突，所以会使用插入意向锁，且事务2不会被阻塞。

（4）自增锁（Auto-inc Locks）  
自增锁是一种特殊的表级别锁，专门针对事务插入AUTO-INCREMENT类型的列。  
即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。  
举个例子：在可重复读隔离级别下，PK ID为自增主键  
表中已有主键ID为1、2、3的3条记录。  
事务1插入了一行：  
insert into table value(‘aa’)  
得到一条（4,’aa’）的记录，未提交；

此时  
事务2中插入了一行：  
insert into table value(‘bb’)  
这时会被阻塞，即用到了插入意向锁的概念。

（5）记录锁（Record Locks）- locks rec but not gap  
记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；

（6）间隙锁(Gap Lock)  
间隙锁锁定记录的一个间隔，但不包含记录本身。  
举个例子：  
假如数据库已有ID为1、6两条记录，  
现在想要在ID in （4，10）之间更新数据的时候，会加上间隙锁，锁住\[4,5\] \[7,10\] ,(不包含已有记录ID=5本身)  
那么在更新ID=5的记录（只有一条记录）符合条件；  
如果不加间隙锁，事务2有可能会在4、10之间插入一条数据，这个时候事务1再去更新，发现在(4,10)这个区间内多出了一条“幻影”数据。  
间隙锁就是防止其他事务在间隔中插入数据，以导致“不可重复读”。

（7）临键锁（Next-Key Lock）= Gap Lock + Record Lock  
临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

2、MySQL死锁  
死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相等待的现象。

发生死锁的3个条件

> = 2个事务  
> 不同方向  
> 相同锁资源  
> 解决死锁的方法

超时等待：即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。（缺点：如果回滚的事务更新了很多行，占用了较多的undo log，那么在回滚的时候花费的时间比另外一个正常执行的事务花费的时间可能还要多，就不太合适）；  
wait-for graph（等待图）：死锁碰撞检测，是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，通过这两个部分信息构造出一张图，在每个事务请求锁并发生等待时都会判断是否存在回路，如果在图中检测到回路，就表明有死锁产生，这时候InnoDB存储引擎会选择回滚undo量最小的事务。  
下面举个例子简单介绍下对wait-for graph的理解。  
如下图所示，有4个事务T1、T2、T3、T4，由图2可以得出T2对row1占用x锁；由图3得出T1对row2占用s锁（遵循FIFO原则，链表最上层先拿到锁资源，故T2、T1分别占有row1、row2的锁资源）。

现在：  
（1）T1想要占用row1的s锁，T1需要等待T2的x锁释放（图1、2）  
（2）T2想要占用row2的x锁，T2需要等待T1、T4的s锁释放（图1、3）  
（3）T3想要占用row2的x锁，T3需要等待T1、T4的s锁和T2的x锁释放（图1、3）

这里写图片描述

根据上面的信息，可以绘制wait-for graph：  
有4个事务，所以对应4个节点T1、T2、T3、T4；  
由（1）得出：节点T1指向节点T2；  
由（2）得出：节点T2分别指向节点T1、T4；  
由（3）得出：节点T3指向节点T1、T2、T4；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70 1]

这里写图片描述  
由上图可知，T1、T2之间存在回路，所以存在死锁。

三、分析过程  
1、如何查看MySQL死锁日志  
MySQL出现死锁的日志如下（这里的数据库名、表名都以虚拟库、表名代替）：

/\*\* 检测到死锁 \*\*/  
2018-08-17T10:25:54.892361+08:00 2421473 \[Note\] InnoDB: Transactions deadlock detected:dumping detailedinformation.

/\*\*

*  第1个事务  
    \*/  
    2018-08-17T10:25:54.892387+08:00 2421473 \[Note\] InnoDB:  
    0 \*\*\* (1) TRANSACTION:

/\*\* 持有1个锁(locked 1 LOCK),该事务持有540812个行锁 \*\*/  
TRANSACTION 36870850707:ACTIVE 0 sec fetching rows  
mysql tables in use 1:locked 1  
LOCK WAIT 2640 lock struct(s:heap size 303312:540812 row lock(s):undo log entries 1  
MySQL thread id 2378184:OS thread handle 47191491700480:query id 1488833748 10.73.237.32 Database\_1 updating

/\*\*

*  事务1在执行UPDATE Table\_1
 *  SQL语句的时候发生死锁，等待数据库Database\_1中表Table\_1
 *  中的主键PRIMARY上的X锁的释放  
    \*/  
    /*id:c5b1daf1*/  
    UPDATE  
    Table\_1  
    SET  
    LockStatus = 1  
    WHERE  
    OrderId = 5340691540570919  
    AND BizId = 5340691540570919  
    AND ActionType = 503  
    AND LockStatus = 0  
    2018-08-17T10:25:54.892429+08:00 2421473 \[Note\] InnoDB: \*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:

/\*\*

*  lock type : RECORD LOCKS（锁的类型）
 *  space id : 锁对象的space id
 *  page no ：事务锁定页的数量，若是表锁，该值为null
 *  index ：锁住的索引
 *  table：要加锁的表
 *  trx id：事务id
 *  lock\_mode : 锁的模式  
    \*/  
    RECORD LOCKS space id 140316 page no 6956 n bits 160 index PRIMARY of table `Database_1`.`Table_1` trx id 36870850707 lock\_mode X waiting

/\*\*

*  检测到第2个事务  
    \*/  
    2018-08-17T10:25:54.892456+08:00 2421473 \[Note\] InnoDB: \*\*\* (2) TRANSACTION:

/\*\* 第2个事务持有5个行锁，在执行update Table\_1 SQL语句的时候出现死锁 \*\*/  
TRANSACTION 36870851126:ACTIVE 0 sec starting index read  
mysql tables in use 1:locked 1  
11 lock struct(s):heap size 1136:5 row lock(s):undo log entries 5  
MySQL thread id 2421473:OS thread handle 47191510509312:query id 1488846769 10.73.237.32 Database\_1  
updating  
/*id:c7549267*/  
UPDATE  
Table\_1  
SET  
Status = 4  
WHERE  
Id = 543090

/\*\*

*  事务2 持有主键PRIMARY索引（index PRIMARY ）上的锁资源
 *  locks rec but not gap代表锁住的是一个索引，不是一个范围  
    \*/  
    2018-08-17T10:25:54.892495+08:00 2421473 \[Note\] InnoDB: \*\*\* (2) HOLDS THE LOCK(S):  
    RECORD LOCKS space id 140316 page no 6956 n bits 160 index PRIMARY of table `Database_1`.`Table_1` trx id 36870851126 lock\_mode X locks rec but not gap

/\*\* 事务2在等待主键PRIMARY索引上的 X locks rec but not gap 锁释放 \*\*/  
2018-08-17T10:25:54.892518+08:00 2421473 \[Note\] InnoDB: \*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 140316 page no 6956 n bits 160 index PRIMARY of table `Database_1`.`Table_1` trx id 36870851126 lock\_mode X locks rec but not gap waiting

/\*\* 事务2 回滚 \*\*/  
2018-08-17T10:25:54.892542+08:00 2421473 \[Note\] InnoDB: \*\*\* WE ROLL BACK TRANSACTION (2)

2、SQL语句分析  
首先介绍下表Table\_1的索引结构:

PRIMARY KEY (`ID`),  
KEY `IX_UpdateTime` (`UpdateTime`),  
KEY `IX_LockStatus` (`LockStatus`),  
KEY `IX_Status` (`Status`),  
KEY `IX_DeadLine_ActionType` (`DeadLine`,`ActionType`)

主键索引为ID，唯一键索引OrderId，索引IX\_UpdateTime、IX\_LockStatus、IX\_Status，组合索引IX\_DeadLine\_ActionType（LockStatus取值0和1）

下面我们来分析下SQL语句。  
事务1中的SQL 1语句：

UPDATE  
Table\_1  
SET  
LockStatus = 1  
WHERE  
OrderId = 2233445566  
AND BizId = 2233445566  
AND ActionType = 503  
AND LockStatus = 0

根据LockStatus索引更新LockStatus值，对LockStatus索引加上X锁。

事务2中的SQL 2语句：

UPDATE  
Table\_1  
SET  
Status = 4  
WHERE  
Id = 543090

根据主键ID去更新表Table\_1中的Status状态，这个时候会对主键索引ID加上X锁。

第一条sql用到了索引LockStatus，执行sql 1更新数据的时候，首先根据索引lockStatus查询，获取所有lockStatus = 0 数据的行锁，在此期间事务1在执行sql 1 的时候锁住了sql 2的行锁，即lockStatus = 0的数据中由一条数据是sql 2对应的记录；  
为验证猜想，  
查看第二条SQL语句中对应的LockStatus为0，猜想验证成功。  
这里写图片描述

由死锁日志分析可得事务2拥有5个行锁，因此猜测得出sql 1对应的数据的行锁被事务2所拥有，这需要通过代码分析事务2在执行的时候，有没有操作sql 1那一行的数据。

由上述分析可得本次死锁的过程：  
事务1在执行sql 1的时候发生死锁，说明sql 1对应的行记录的锁被事务2占有；  
事务2在执行sql 2的时候发生死锁，说明sql 2对应的行记录的锁被事务1占有；

用T1、T2分别表示事务1、事务2，sql1、sql2对应的行记录分别用row1、row2表示，  
T1在执行sql1的时候，先去尝试获取sql1所在记录的行锁，发现sql1的行锁被T2占有；  
T2在执行sql2的时候，先去尝试获取sql2所在记录的行锁，发现sql2的行锁被T1占有；  
用图表示  
这里写图片描述  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70 2]

3、解决方案  
产生死锁的原因是第一条SQL语句用到的索引区分度不高，只用到了lockstatsu索引，导致5万条行记录被锁，因此解决方案就是要用区分度更高的索引，根据第一条SQL建立唯一键引：

UNIQUE KEY `UK_OrderId_BizId_ActionType` (`OrderId`,`BizId`,`ActionType`)  
1  
参考：《MySQL技术内幕》  
微信公众号：架构师之路  
疑问：  
1.根据上图索引分布情况，为什么只用到了lockstatsu索引，为什么不是唯一键索引OrderId，是没有创建吗，只是提到这个唯一索引  
2.不同事物持有锁，或者咱有同一把锁？只有一把锁怎么不同事物都占有。

[Link 1]: https://blog.csdn.net/noaman_wgs/article/details/82529908
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70]: /images/20221119/3f335d92817e4b3cb2fd768aacc1881c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210118112950792.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210118112800254.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0MxODI5ODE4MjU3NQ==,size_16,color_FFFFFF,t_70