mysql死锁场景汇总整理

左手的ㄟ右手 2022-11-10 10:53 185阅读 0赞

**目录**

简述

行锁导致死锁

gap lock/next keys lock导致死锁

index merge导致死锁

唯一索引冲突导致死锁

总结

--------------------

## 简述 ##

本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：

*  行锁导致死锁
 *  gap lock/next keys lock导致死锁
 *  index merge 导致死锁
 *  唯一索引冲突导致死锁

**注**：以下场景隔离级别均为默认的Repeatable Read；

## 行锁导致死锁 ##

**前提**：表 t\_user 的 uid 字段创建了唯一索引，并拥有可更新字段age。  
**场景复现**：

![7aa5ce8c9038cdb82d7b83c135c6f755.png][]

行锁导致死锁

**死锁原因详解**：

1.  两个事务执行过程时间上有交集，并且过程发生在两者提交之前
2.  事务1更新uid=1的记录，事务2更新uid=2的记录，在RR级别，由于uid是唯一索引，因此两个事务将分别持有uid=1和2所在行的独占锁
3.  事务1执行到第二条更新语句时，发现uid=2的行被锁住，进入阻塞等待锁释放；
4.  事务2执行到第二条语句时发现uid=1的行被锁，同样进入阻塞
5.  两个事务互相等待，死锁产生。

**相应业务案例和解决方案**：  
该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示`lock_mode X locks rec but not gap waiting`（即行锁而非间隙锁），解决方案：

1.  避免循环更新，优化为一条where锁定要更新的记录批量更新
2.  如果非要循环更新，尝试取消事务（能接受的话），即每一条更新为一个独立的事务

## gap lock/next keys lock导致死锁 ##

**表结构**:

CREATE TABLE `t_user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `age` int(3) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `udx_age` (`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

**场景复现**：  
首先查询表中目前存在的记录：

![1834ea6ac49ab9c77c785876dd607261.png][]

执行两个事务的操作：

![c4b5a100d1a7319184603278360dd786.png][]

**死锁原因分析**：

1.  事务1执行delete age = 27，务2执行delete age = 31，在RR级别，操作条件不是唯一索引时，行锁会升级为next keys lock（可以理解为间隙锁），因此事务1锁住了25到27和27到29的区间，事务2锁住了29到31的区间
2.  事务1执行insert age = 30，等待事务2释放锁
3.  事务2执行insert age = 28，等待事务1释放锁
4.  死锁产生，死锁日志显示`lock_mode X locks gap before rec insert intention waiting`

**解决方案**：

1.  降低事务隔离级别到Read Committed，该隔离级别下间隙锁降级为行锁，可以减少死锁发生的概率
2.  避免这种场景- -

## index merge导致死锁 ##

t\_user结构改造为：

CREATE TABLE `t_user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `age` int(11) DEFAULT NULL,
      `zone_id` bigint(20) DEFAULT NULL,
      `username` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_age` (`age`),
      KEY `idx_zone_id` (`zone_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

**场景复现操作（几率不高）**：

![cb3a6a0b63d08f5dd393568453afad1d.png][]

**假设存在以下数据**：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>zone_id</th> 
   <th>uid</th> 
   <th>username</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
   <td>""</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>2</td> 
   <td>""</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>1</td> 
   <td>""</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2</td> 
   <td>2</td> 
   <td>""</td> 
  </tr> 
 </tbody> 
</table>

**死锁分析**：

1.  在符合场景前提的情况下（即表数据量较大，index\_merge未关闭），通过explain分析`update t_user where zone_id = 1 and uid = 1`可以发现type是index\_merge，即会用到zone\_id和uid两个索引
2.  上锁的过程为：

**事务1**：  
① 锁住zone\_id=1对应的间隙锁: zoneId in (1,2)  
② 锁住索引zone\_id=1对应的主键索引行锁id = \[1,2\]  
③ 锁住uid=1对应的间隙锁: uid in (1, 2)  
④ 锁住uid=1对应的主键索引行锁: id = \[1, 3\]  
**事务2**：  
① 锁住zone\_id=2对应的间隙锁: zoneId in (1,2)  
② 锁住索引zone\_id=2对应的主键索引行锁id = \[3,4\]  
③ 锁住uid=2对应的间隙锁: uid in (1, 2)  
④ 锁住uid=2对应的主键索引行锁: id = \[2, 4\]

1.  如果两个事务上锁的顺序相反，则有一定的概率出现死锁。另外，index\_merge的形式锁住了很多不符合条件的行，浪费了资源。一般死锁日志打印的信息为：`lock_mode X locks rec but not gap waiting Record lock`

**解决方案**：创建联合索引，使执行计划只会用到一个索引。

## 唯一索引冲突导致死锁 ##

**测试表结构**:

CREATE TABLE `t_sample` (
      `id` bigint(29) NOT NULL AUTO_INCREMENT,
      `uid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_uid` (`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

**场景复现操作**：

image.png

**死锁分析**：

1.  三个事务分别尝试插入uid=1的数据，其中事务1先于后两个事务
2.  由于是唯一索引，所以后两个事务会出现唯一键冲突，但是事务1并未立即提交，因此不会报错，而是将事务一insert的隐式锁升级为显式锁
3.  事务二和事务三为了判断是否出现唯一键冲突，必须进行一次当前读（`select...lock in share mode`)，加的锁是GAP S锁，所以进入阻塞，等待事务一释放锁
4.  事务一回滚，此时事务二和事务三成功获取记录上的GAP S锁，并继续执行插入操作
5.  插入则需要依次请求插入意向锁，而插入意向锁和GAP S锁冲突，因此两个事务相互等待，形成死锁

解决办法：尽量避免这种插入又回滚的场景。

## 总结 ##

避免死锁的原则：

*  建立合适的索引，减小锁的粒度
 *  选择合适的事务隔离级别
 *  大事务拆成小事务，一个事务中的锁尽量少

[7aa5ce8c9038cdb82d7b83c135c6f755.png]: /images/20221022/cf1aee774159426980c4ad89a66f5f65.png
[1834ea6ac49ab9c77c785876dd607261.png]: https://img-blog.csdnimg.cn/img_convert/1834ea6ac49ab9c77c785876dd607261.png
[c4b5a100d1a7319184603278360dd786.png]: https://img-blog.csdnimg.cn/img_convert/c4b5a100d1a7319184603278360dd786.png
[cb3a6a0b63d08f5dd393568453afad1d.png]: https://img-blog.csdnimg.cn/img_convert/cb3a6a0b63d08f5dd393568453afad1d.png