死锁场景（删除&插入不存在的记录）

背景

MySQL 5.7
隔离级别：RR

CREATE TABLE `t4` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kdt_id` INT(11) UNSIGNED NOT NULL,
  `admin_id` INT(11) UNSIGNED NOT NULL,
  `biz` VARCHAR(20) NOT NULL DEFAULT '1',
  `role_id` INT(11) UNSIGNED NOT NULL,
  `shop_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `operator` VARCHAR(20) NOT NULL DEFAULT '0',
  `operator_id` INT(11) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
 
INSERT INTO `t4` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES
 (1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
 (2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
 (3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),
 (4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
 (5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');
 
mysql> SELECT * FROM t4;
+----+--------+----------+--------+---------+---------+----------+-------------+---------------------+---------------------+
| id | kdt_id | admin_id | biz    | role_id | shop_id | operator | operator_id | create_time         | update_time         |
+----+--------+----------+--------+---------+---------+----------+-------------+---------------------+---------------------+
|  1 |     10 |        1 | retail |       1 |       0 | 0        |           0 | 2017-05-09 15:55:26 | 2017-05-09 15:55:26 |
|  2 |     20 |        1 | retail |       1 |       0 | 0        |           0 | 2017-05-09 15:55:40 | 2017-05-09 15:55:40 |
|  3 |     30 |        1 | retail |       1 |       0 | 0        |           0 | 2017-05-09 15:55:55 | 2017-05-09 15:55:55 |
|  4 |     40 |        1 | retail |       1 |       0 | 0        |           0 | 2017-05-09 15:56:06 | 2017-05-09 15:56:06 |
|  5 |     50 |        1 | retail |       1 |       0 | 0        |           0 | 2017-05-09 15:56:16 | 2017-05-09 15:56:16 |
+----+--------+----------+--------+---------+---------+----------+-------------+---------------------+---------------------+
5 ROWS IN SET (0.00 sec)

session 1	session 2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1'; Query OK, 0 rows affected (0.00 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1'; Query OK, 0 rows affected (0.00 sec) mysql> insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
mysql> INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	Query OK, 1 row affected (24.79 sec)

session 1

session 2

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

mysql> delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1';
Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) 
VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

mysql> INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) 
VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Query OK, 1 row affected (24.79 sec)

查询死锁日志

*************************** 1. ROW ***************************
  TYPE: InnoDB
  Name: 
STATUS: 
=====================================
2022-04-21 20:14:58 0x7f021ce44700 INNODB MONITOR OUTPUT
=====================================
Per SECOND averages calculated FROM the LAST 5 seconds
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 20:14:41 0x7f021ce44700
*** (1) TRANSACTION:
TRANSACTION 559396809, ACTIVE 51 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1765549133 localhost root UPDATE
INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000014; ASC     ;;
 1: len 4; hex 00000001; ASC     ;;
 2: len 4; hex 00000001; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000002; ASC         ;;
 
*** (2) TRANSACTION:
TRANSACTION 559396670, ACTIVE 61 sec inserting
mysql TABLES IN USE 1, locked 1
3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1765551572 localhost root UPDATE
INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000014; ASC     ;;
 1: len 4; hex 00000001; ASC     ;;
 2: len 4; hex 00000001; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000002; ASC         ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000014; ASC     ;;
 1: len 4; hex 00000001; ASC     ;;
 2: len 4; hex 00000001; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000002; ASC         ;;
 
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
 
---TRANSACTION 559396809, ACTIVE 68 sec
3 LOCK struct(s), heap SIZE 1136, 3 ROW LOCK(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1765549133 localhost root cleaning up
TABLE LOCK TABLE `okami`.`t4` trx id 559396809 LOCK mode IX
RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000014; ASC     ;;
 1: len 4; hex 00000001; ASC     ;;
 2: len 4; hex 00000001; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000002; ASC         ;;
 
Record LOCK, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000012; ASC     ;;
 1: len 4; hex 00000002; ASC     ;;
 2: len 4; hex 00000002; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000006; ASC         ;;
 
RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000014; ASC     ;;
 1: len 4; hex 00000001; ASC     ;;
 2: len 4; hex 00000001; ASC     ;;
 3: len 6; hex 72657461696c; ASC retail;;
 4: len 8; hex 0000000000000002; ASC         ;;

分析死锁日志

事务1执行的SQL： INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
事务1持有的锁：  （间隙锁） RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec
事务1等待获取的锁： （插入意向锁） RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention waiting

事务2执行的SQL： INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
事务2持有的锁： （间隙锁） RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec
事务2等待获取的锁： （插入意向锁） RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec INSERT intention waiting

事务1要获取插入意向锁，需要等待事务2释放间隙锁
事务2要获取插入意向锁，需要等待事务1释放间隙锁
造成死锁

还原死锁过程

1、session1 删除一条不存在的记录，需要加间隙锁。锁的区间为 （10， 20）
2、session2 删除被锁定区间不存在的记录，由于间隙锁之间不冲突，随意加间隙锁成功
3、session2 在锁定的区间插入一条记录，需要获取插入意向锁。由于插入意向锁和间隙锁冲突。故而需要等待sesison1间隙锁的释放
4、session1 同样的锁定的区间插入一条记录，需要获取插入意向锁。此时session2的间隙锁没有释放
5、session1 等待session2的间隙锁释放，session2 等待session1的间隙锁释放。形成僵局（死锁）

提出解决方案

调整业务逻辑，避免删除不存在的记录
调整MySQL的隔离级别

[mysql, 事务, 死锁]

目录

死锁场景（删除&插入 不存在的记录）

背景

查询死锁日志

分析死锁日志

还原死锁过程

提出解决方案

死锁场景（删除&插入不存在的记录）