MySQL中死锁案例分析与解决方案-优快云博客

问题描述

今天线上流水的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，不会存在幻读。