手把手教你分析解决MySQL死锁问题
在生产环境中如果出现MySQL死锁问题该如何排查和解决呢，本文将模拟真实死锁场景进行排查，最后总结下实际开发中如何尽量避免死锁发生。
一、准备好相关数据和环境
当前自己的数据版本是8.0.22
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec)
数据库隔离级别(默认隔离级别)
mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
自动提交关闭
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
表结构
这个age为 非唯一索引，这点对下面整个案例非常重要。
-- id是自增主键，age是非唯一索引，name普通字段 CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `age` int DEFAULT NULL COMMENT '年龄', `name` varchar(255) DEFAULT NULL COMMENT '姓名', PRIMARY KEY (`id`), KEY `idx_age` (`age`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
表中暂时先插入两条数据
二、模拟出真实死锁案例
开启两个终端模拟事务并发情况，执行顺序以及实验现象如下：
1）事务A执行更新操作，更新成功
mysql> update user set name = 'wangwu' where age= 20; Query OK, 1 row affected (0.00 sec)
- 事务B执行更新操作，更新成功
mysql> update user set name = 'zhaoliu' where age= 10; Query OK, 1 row affected (0.00 sec)
3）事务A执行插入操作，陷入阻塞~
mysql> insert into user values (null, 15, "tianqi");
4）事务B执行插入操作，插入成功，同时事务A的插入由阻塞变为死锁error。
insert into user values (null, 30, "wangba"); Query OK, 1 row affected (0.00 sec)
事务A的插入操作变成报错。
上面四步操作后，我们分别对事务A和事务B进行commit操作。
mysql> commit; Query OK, 0 rows affected (0.00 sec)
我们再来看数据库中表的数据。
我们发现，事务B的所有操作最终都成功了，而事务A的操作因为报错都回滚了。所以事务A的操作都失败。
那既然是死锁，为什么回滚事务A,而不是事务B,是随机的还是有机制在里面？
我们可以理解死锁是数据库对事务的保护机制,一旦发生死锁,MySQL会选择相对小的事务(undo较少的)进行回滚。
三、查看分析死锁日志
可以用 show engine innodb status，查看最近一次死锁日志哈，执行后，死锁日志如下(只展示部分日志)：
LATEST DETECTED DEADLOCK ------------------------ 2021-12-24 06:02:52 0x7ff7074f8700 *** (1) TRANSACTION: TRANSACTION 2554368, ACTIVE 22 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 INSERT INTO user VALUES (NULL, 15, "tianqi") *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 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;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 2554369, ACTIVE 14 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 INSERT INTO user VALUES (NULL, 30, "wangba") *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 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 (1)
1、事务A相关日志
1）找到关键词TRANSACTION，事务2554368
2）查看事务1正在执行的sql
insert into user values (null, 15, "tianqi")
- 查看当前事务已占有的锁和等待其它事务释放的锁
2、事务B相关日志
1）找到关键词TRANSACTION，事务2554369
2）查看事务2正在执行的sql
insert into user values (null, 30, "wangba")
- 查看当前事务已占有的锁和等待其它事务释放的锁
3、总结
这里把一些关键的日志截图了下
我们把这张图换一种方式画下
1）从图中可以很明显的看出,事务1和事务2都在等对方的锁释放，所以导致了死锁问题。而且最终是事务1进行了回滚。
2）这个日志提供比较重要的信息就是我们可以看出的是哪两条sql在互相一直等待其它事务的锁释放而产生了死锁，也知道是哪个索引导致产生的死锁，同时也知道最终哪个事务
被回滚了。
3）如果上面的信息还不能帮你定位解决问题，那可以问数据库DB要详细的binlog日志来分析这段时间这两个事务具体执行的所有sql。
四、总结分析案例中产生死锁的原因
这个分析就需要对MySQL中的各种锁机制有所了解，还不清楚的话可以看我之前写的两篇文章，看完你就清楚我下面所写的了。
1、事务A的SQL产生了哪些锁
1) 事务A的update语句产生哪些锁
我们先来看
update user set name = 'wangwu' where age= 20;
记录锁
因为是等值查询，所以这里会在满足age=20的所有数据请求一个记录锁。
间隙锁
因为这里是非唯一索引的等值查询,所以一样会产生间隙锁(如果是唯一索引的等值查询那就不会产生间隙锁，只会有记录锁),因为这里只有2条记录
所以左边为(10,20),右边因为没有记录了，所以请求间隙锁的范围就是(20,+∞),加一起就是(10,20) +（20,+∞)。
Next-Key锁
Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（10,+∞)的 Next-Key锁
事务A的install语句产生哪些锁
INSERT INTO user VALUES (NULL, 15, "tianqi");
间隙锁
因为age 15(在10和20之间)，所以需要请求加(10,20)的间隙锁
插入意向锁（Insert Intention）
插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(10,20),这个插入锁的作用就是提高插入效率的，在分析
死锁的时候我们可以不用关心它，只关心上面间隙锁就好了。
2、事务B的SQL产生了哪些锁
事务B的update语句产生哪些锁
我们先来看
update user set name = 'zhaoliu' where age= 10
记录锁
因为是等值查询，所以这里会在满足age=10的所有数据请求一个记录锁。
间隙锁
因为左边没有记录,右边有一个age=20的记录，所以间隙锁的范围是(-∞,10),右边为(10,20),一起就是(-∞,10)+(10,20)。
Next-Key锁
Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（-∞,20)的 Next-Key锁
事务A的install语句产生哪些锁
INSERT INTO user VALUES (NULL, 30, "wangba")
间隙锁
- 因为age 30(左边是20，右边没有值)，所以需要请求加(20,+∞)的间隙锁
插入意向锁（Insert Intention）
- (20,+∞)
锁都分析清楚了，接下来再来看下是什么地方导致死锁的呢？
这样以来产生整个死锁的原因也就清楚了，不过这里再补充两点
1）MySQL的间隙锁虽然有左开右闭的原则,但是其实这个并不完全正确,因为它有可能是左闭右开,也可能是左开右开，它会跟你插入主键值位置有关，具体的可以看我之前写的
一篇文章里面有完整示例MySQL记录锁、间隙锁、临键锁小案例演示。所以这里间隙锁写的都是左开右开的范围，可能临界点有点模糊，但不影响分析这个案例的死锁问题。
2）通过事务A和事务B的update语句，我们可以发现其实它们都持有间隙锁(10,20)的这段范围,说明间隙锁范围是可以相互兼容的，意思就是只要你的10不在我（10,+∞)的间隙锁
范围内，那就可以产生部分重合的间隙锁，也就是这里的(10,20)。
五、实际开发中如何尽量避免死锁发生
一般来讲在实际开发中，很少会发生死锁的情况，尤其是在业务并发量不是很大的情况下。在并发很大的情况下可能会存在偶尔产生死锁。
不过呢，在自己实际开发中，有遇到过请求一个接口出现100%概率死锁的情况。
当时的场景其实很简单。一段业务代码中，有去走Dubbo调其它接口服务,这就存在了两个事务,结果各自事务提交的时候，都需要等待对方的锁释放，就导致每次都发生死锁超时。
这其实是一种代码不规范而导致死锁的发生。这里也总结下如何尽量避免死锁发生。
1）不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表。对一个表而言，应尽量以固定的顺序存取表中的行。这点真的很重要,它可以明显的减少死锁的发生。
举例：好比有a,b两张表，如果事务1先a后b,事务2先b后a,那就可能存在相互等待产生死锁。那如果事务1和事务2都先a后b，那事务1先拿到a的锁，事务2再去拿a的锁，如果
锁冲突那就会等待事务1释放锁，那自然事务2就不会拿到b的锁，那就不会堵塞事务1拿到b的锁，这样就避免死锁了。
2）在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新。为什么要这么做呢，因为如果去更新一条数据库不存在的数据，
一样会产生间隙锁。
举例：如果表中只有id=1和id=5的数据，那么如果你更新id=3的sql，因为这条记录表中不存在，那就会产生一个(1,5)的间隙锁，但其实这个锁就是多余的，因为你去更新一个
数据都不存在的数据没有任何意义。
3）尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生行锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，
一样会产生间隙锁。
4）避免长事务，小事务发送锁冲突的几率也小。这点应该很好理解。
5）在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁，因为产生死锁经常都跟间隙锁有关，间隙锁的存在本身也是在RR隔离级别来
解决幻读的一种措施。
感谢
这篇文章给自己提供了很好的思路，这篇文章也基本上按照这个思路往下写的
