手把手教你分析解决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 2INSERT 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 00: 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 00: 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 00: 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 00: 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中的各种锁机制有所了解。

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锁

「2 事务A的install语句产生哪些锁」

INSERT INTO user VALUES (NULL, 15, "tianqi");

间隙锁

因为age 15(在10和20之间)，所以需要请求加(10,20)的间隙锁

插入意向锁（Insert Intention）

插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(10,20),这个插入锁的作用就是提高插入效率的，在分析死锁的时候我们可以不用关心它，只关心上面间隙锁就好了。

2、事务B的SQL产生了哪些锁

「1 事务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锁

「2 事务A的install语句产生哪些锁」

INSERT INTO user VALUES (NULL, 30, "wangba")

间隙锁

因为age 30(左边是20，右边没有值)，所以需要请求加(20,+∞)的间隙锁

插入意向锁（Insert Intention）

(20,+∞)

锁都分析清楚了，接下来再来看下是什么地方导致死锁的呢？

这样一来产生整个死锁的原因也就清楚了，不过这里再补充两点：

1、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死锁问题

相关问题

公告