一个死锁的case

551 阅读4分钟

问题描述

今天线上流水的consumer出现了一个insert导致的死锁问题,这里通过一个DEMO复现一下case的整个过程,并进行详细的分析。

表结构如下:

mysql> show create table test_table;
| Table      | Create Table                                                                                
| test_table | CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_table_a_uindex` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1 row in set (0.00 sec)

表数据:

mysql> select * from test_table;
+----+----+------+
| id | a  | b    |
+----+----+------+
|  1 |  1 |    1 |
| 20 | 20 |   20 |
| 50 | 50 |   50 |
+----+----+------+
3 rows in set (0.02 sec)

事务中的代码是先update,如果记录不存在再去insert。

事务1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_table set b = 1 where a = 30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

事务1开启事务,并update一条不存在的记录(此时会对a,[20,50]加间隙锁)。

事务2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_table set b = 1 where a = 31;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

事务2开启事务,并update一条不存在的记录(此时同样会对a,[20,50加间隙锁])。

事务1:

mysql> insert into test_table values(30,30,1);
mysql> waiting...

事务1插入一条记录,此时会被阻塞...

事务2:

ysql> insert into test_table values(31,31,1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

事务2同样插入一条记录,此时会报错,死锁,并回滚事务。

事务1:

mysql> insert into test_table values(30,30,1);
Query OK, 1 row affected (12.58 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务2回滚后,事务1插入成功,事务提交成功。

问题分析

1.事务1、事务2中分别进行了一次update操作,并且操作的记录都不存在,此时,事务1、2分别会对a,[20,50]范围加一个间隙锁。

注:间隙锁与间隙锁之间可以兼容(共享锁)

2.事务1中进行了一次insert操作,此时由于事务2对a,[20,50]范围加了一个间隙锁,所以事务1的insert操作处于阻塞状态。

3.事务2中也进行了一次insert操作,同样被事务1的间隙锁阻塞。此时事务1在等事务2释放间隙锁,而事务2也在等事务1释放间隙锁,构成死锁,所以事务2报错“死锁”并进行事务回滚。

4.事务2回滚后,事务2的间隙锁被释放,事务1的insert操作执行成功,事务1提交成功。

解决方案

考虑了两种解决方案:

方案一

将事务中的插入操作提到事务之前执行,每次事务开始前先select一下,如果记录不存在插入一条空记录进去,在事务中只需要执行update操作。

缺点:多进行了一次select操作,可能对接口性能造成影响,需要重新进行压测判断。

方案二

降低mysql事务隔离级别,从RR下调到RC。

缺点:存在幻读问题

综合考虑,由于这个集群只用于流水和统计数据的存储,所以采用了 方案二:调低事务隔离级别

知识扩展

InnoDB中RR隔离级别是否存在幻读问题?

回答这个问题前,我先假设你知道数据库隔离级别的定义针对的都是“当前读”

首先我们来看一段InnoDB官方文档的话:

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.

大致意思就是,在 RR 级别下,如果查询条件能使用上唯一索引,或者是一个唯一的查询条件,那么仅加行锁,如果是一个范围查询,那么就会给这个范围加上 gap 锁或者 next-key锁 (行锁+gap锁)。

InnoDB 的 RR 隔离界别对范围会加上 GAP,不会存在幻读。

小结

  • 事务最好不要太长,否则容易出现锁等待、死锁等问题
  • insert操作最好不要放到事务里,否则容易引发死锁问题(相互等待)。