手把手教你分析解决MySQL死锁问题

手把手教你分析解决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)

2. 事务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")

3. 查看当前事务已占有的锁和等待其它事务释放的锁

2、事务B相关日志

1）找到关键词TRANSACTION，事务2554369

2）查看事务2正在执行的sql

insert into user values (null, 30, "wangba")

3. 查看当前事务已占有的锁和等待其它事务释放的锁

3、总结

这里把一些关键的日志截图了下

我们把这张图换一种方式画下

1）从图中可以很明显的看出,事务1和事务2都在等对方的锁释放，所以导致了死锁问题。而且最终是事务1进行了回滚。

2）这个日志提供比较重要的信息就是我们可以看出的是哪两条sql在互相一直等待其它事务的锁释放而产生了死锁，也知道是哪个索引导致产生的死锁，同时也知道最终哪个事务被回滚了。

3）如果上面的信息还不能帮你定位解决问题，那可以问数据库DB要详细的binlog日志来分析这段时间这两个事务具体执行的所有sql。

四、总结分析案例中产生死锁的原因

这个分析就需要对MySQL中的各种锁机制有所了解，还不清楚的话可以看我之前写的两篇文章，看完你就清楚我下面所写的了。

• 一文详解MySQL的锁机制
• 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隔离级别来解决幻读的一种措施。

感谢

这篇文章给自己提供了很好的思路，这篇文章也基本上按照这个思路往下写的

手把手教你分析MySQL死锁问题

声明: 公众号如需转载该篇文章,发表文章的头部一定要 告知是转至公众号: 后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载！