MySQL死锁如何处理

红太狼 2022-12-19 13:26 139阅读 0赞

转载自  [MySQL死锁如何处理][MySQL]

![8d3b9161a0b8d98920d4d05431b75cb1.png][]

## 前提 ##

笔者负责的一个系统最近有新功能上线后突然在预警模块不定时报出MySQL死锁导致事务回滚。幸亏，上游系统采用了异步推送和同步查询结合的方式，感知到推送失败及时进行了补偿。于是，笔者争取了一点时间详细分析了导致死锁的多个事务的执行时序，分析并且得出解决方案。

## 死锁场景复现 ##

首先，MySQL的服务端版本是5.7(小版本可以基本忽略)，使用了`InnoDB`。有一张用户数据表的schema设计如下（无关字段已经屏蔽掉）：

CREATE TABLE `t_user_data`
    (
        id      BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
        data_id VARCHAR(50)     NOT NULL COMMENT '数据ID',
        INDEX idx_user_id (user_id),
        INDEX idx_data_id (data_id)
    ) COMMENT '用户数据表';

业务代码中发生死锁的伪代码如下：

process_method(dataId,userDataDtoList){
        start transaction:
        userDataDao.deleteByDataId(dataId);
        for dto in userDataDtoList:
            UserData userData = convert(dto);
            userDataDao.insert(dto);
        commit;
    }

这里的逻辑是，如果已经存在对应dataId的数据要先进行删除，然后写入新的用户数据。

尝试用两个Session提交两个事务重现死锁问题：

<table> 
 <thead> 
  <tr> 
   <th>时间序列</th> 
   <th>Tx-Session-1</th> 
   <th>Tx-Session-2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td>START TRANSACTION;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td>&nbsp;</td> 
   <td>START TRANSACTION;</td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>DELETE FROM t_user_data WHERE data_id = ‘xxxxx’;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td>&nbsp;</td> 
   <td>DELETE FROM t_user_data WHERE data_id = ‘yyyyy’;</td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td>INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (1, ‘xxxxx’);</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td>&nbsp;</td> 
   <td>INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (2, ‘yyyyy’);</td> 
  </tr> 
  <tr> 
   <td>T7</td> 
   <td>&nbsp;</td> 
   <td>Deadlock found when trying to get lock; try restarting transaction(<strong>Rollback</strong>)</td> 
  </tr> 
  <tr> 
   <td>T8</td> 
   <td>COMMIT;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

这里会出现两个现象：

1.  Tx-Session-2会话T4执行完毕之后，Tx-Session-1会话T5执行的时候，Tx-Session-1会话客户端会处于阻塞状态。
2.  Tx-Session-2会话T6执行完毕之后，MySQL提示死锁事务被回滚，此时，Tx-Session-1会话客户端会解除阻塞。

## 导致死锁的原因 ##

后面会写一篇专门的文章学习和理解MySQL的InnoDB数据引擎的锁相关知识，这里直接排查InnoDB的死锁日志。

mysql> show engine innodb status;

输出的死锁日志如下：

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-05-11 19:16:04 0x5804
    *** (1) TRANSACTION:
    TRANSACTION 3882, ACTIVE 13 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 32, OS thread handle 9876, query id 358 localhost ::1 doge update
    INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (1, 'xxxxx')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3882 lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    *** (2) TRANSACTION:
    TRANSACTION 3883, ACTIVE 9 sec inserting, thread declared inside InnoDB 5000
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 11, OS thread handle 22532, query id 359 localhost ::1 doge update
    INSERT INTO t_user_data(USER_ID, DATA_ID) VALUES (2, 'yyyyy')
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3883 lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 33 page no 6 n bits 72 index idx_data_id of table `test`.`t_user_data` trx id 3883 lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    *** WE ROLL BACK TRANSACTION (2)

这里要参考MySQL关于InnoDB锁的关于next-key锁描述那一节，注意死锁日志关键字`supremum`的意义：

> next-key锁将gap锁定在索引中最大值之上，而`supremum`伪记录的值高于索引中实际的任何值。`supremum`不是真正的索引记录，因此，实际上，此next-key锁仅锁定最大索引值之后的间隙。

两个事务的锁属性可以通过`select * from information_schema.innodb_locks;`进行查询，数据如下表：

<table> 
 <thead> 
  <tr> 
   <th>lock_id</th> 
   <th>lock_tx_id</th> 
   <th>lock_mode</th> 
   <th>lock_type</th> 
   <th>lock_table</th> 
   <th>lock_index</th> 
   <th>lock_space</th> 
   <th>lock_page</th> 
   <th>lock_rec</th> 
   <th>lock_data</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3882:33:6:1</td> 
   <td>3882</td> 
   <td>X</td> 
   <td>RECORD</td> 
   <td><code>test</code>.<code>t_user_data</code></td> 
   <td>idx_data_id</td> 
   <td>33</td> 
   <td>6</td> 
   <td>1</td> 
   <td>supremum pseudo-record</td> 
  </tr> 
  <tr> 
   <td>3883:33:6:1</td> 
   <td>3883</td> 
   <td>X</td> 
   <td>RECORD</td> 
   <td><code>test</code>.<code>t_user_data</code></td> 
   <td>idx_data_id</td> 
   <td>33</td> 
   <td>6</td> 
   <td>1</td> 
   <td>supremum pseudo-record</td> 
  </tr> 
 </tbody> 
</table>

DELETE FROM t_user_data WHERE data_id = '不存在的索引值';

上面的SQL执行时候，如果条件刚好是索引列，并且查询的值是当前表(索引)中不存在的数据，根据next-key锁的描述和死锁日志中的`asc supremum`关键字，执行该`DELETE`语句的时候，会锁定目标值和高于目标值的任何值，如果条件是"xxxxx"，那么相当于锁定区间为(“xxxxx”,最大上界\]。

next-key锁是索引记录上的记录锁(Record Lock)和索引记录之前的间隙上的间隙锁(Gap Lock)定的组合。间隙锁有两个特点：

1.  两个事务即使锁定的区间一致（或者有部分重合），不会影响它们之间获取到锁（可以参考行锁的兼容性矩阵）。
2.  间隙锁G会阻止非持有G的其他事务向锁定的区间中插入数据，以避免产生冲突数据。

分析到这里，就很好解释上面出现死锁的执行时序：

1.  两个事务的`DELETE`语句都可以正确执行，这个时候，两者的间隙锁锁定的区域分别是(‘xxxxx’,最大上界\]和(‘yyyyy’,最大上界\]。
2.  事务1执行`INSERT`语句的时候阻塞，是因为事务2的间隙锁不允许事务1插入索引值’xxxxx’。
3.  事务2执行`INSERT`语句的时候阻塞，是因为事务1的间隙锁不允许事务1插入索引值’yyyyy’，执行到这一步，MySQL的死锁检查模块应该起效了，因为两个事务依赖的锁资源已经成环(或者成有向图)。
4.  事务2的优先级比较低，于是抛出死锁异常并且被回滚了。

之前曾经和DBA同事聊过，发生死锁的事务是怎么衡量优先级或者怎么确定哪个事务需要回滚(释放锁资源让另一个事务可以正常提交)，但是后来没有收到很好的答复，这一点有时间再研究一下。

## 解决方案 ##

参考MySQL的文档，解决方案有两个：

1.  方案一：降低数据库的事务隔离级别，需要降低到`READ COMMITED`，这样子可以关闭间隙锁的扫描。（<== 并不推荐这种做法，修改事务隔离级别有可能出现新的问题）
2.  方案二：针对对应的原因修改业务代码。

这里方案二只需要把伪代码逻辑修改如下：

process_method(dataId,userDataDtoList){
        List<UserData> userDataList = userDataDao.selectByDataId(dataId);
        start transaction:
        if userDataList is not empty: 
           List<Long> ids = collectIdList(userDataList);
           userDataDao.deleteByIds(ids);       
        for dto in userDataDtoList:
            UserData userData = convert(dto);
            userDataDao.insert(dto);
        commit;
    }

就是先根据dataId进行查询，如果存在数据，聚合主键列表，通过主键列表进行删除，然后再进行数据插入。

## 小结 ##

这并非是第一次在生产环境中出现MySQL死锁，只是这次的案例相对简单。InnoDB提供的死锁日志其实并没有提供完整的事务提交的SQL，所以对于复杂的场景需要细致结合代码和死锁日志进行排查，很多时候对应的代码逻辑是多处的。这里列举一下笔者处理死锁问题的一些步骤：

1.  及时止损，如果可以回滚导致死锁的代码，那么最好果敢地回滚；如果重试可以解决问题并且出现死锁问题的规模不大，可以尝试短时间内进行问题排查。
2.  通过业务系统日志迅速定位到发生死锁的代码块，JVM应用一般底层是依赖JDBC，出现死锁的时候会抛出一个`SQLException`的子类，异常栈的信息中带有"Deadlock"字样。
3.  分析InnoDB的死锁日志，一般会列出竞争锁的多个事务的相对详细的信息，这些信息是排查死锁问题的第一手资料。
4.  修复问题上线后注意做好监控和预警，确定问题彻底解决。

参考资料：

*  MySQL5.7官方文档

[MySQL]: https://mp.weixin.qq.com/s?__biz=MzU0MDEwMjgwNA==&mid=2247489011&idx=2&sn=4ba38bcd18da750a38944bd9757cb5e5&chksm=fb3f0c08cc48851e11fe23ba73389e79a266dcfeced39e0bf4e109f95216748a11ff5ef0bee0&mpshare=1&scene=23&srcid=&sharer_sharetime=1593480784080&sharer_shareid=94d57563d95d90af139479e3745fcada#rd
[8d3b9161a0b8d98920d4d05431b75cb1.png]: /images/20221120/860536b0e7be439199d68243456dbc20.png