mysql死锁排查案例

行锁的兼容矩阵

/	Gap	Insert Intention	Record	Next-Key
Gap	兼容	兼容	兼容	兼容
Insert Intention	冲突	兼容	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-Key	兼容	兼容	冲突	冲突

问题描述

业务背景:
有一个表需要储存终端上报的硬件信息, 接口处理逻辑是先删除旧数据, 然后开启事务循环插入新数据.(不要问我为什么不用批量插入, 我接的是旧代码).
问题:
出现了死锁.

死锁问题排查

数据准备

CREATE TABLE `testd` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert into testd values(1,1,1),(10,10,10),(20,20,20);

Session 1

mysql> select * from testd;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
| 10 |   10 |   10 |
| 20 |   20 |   20 |
+----+------+------+

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

-- step1
mysql> insert into testd values(7,7,7);
Query OK, 1 row affected (0.00 sec)

-- step3
mysql> insert into testd values(8,7,70);
Query OK, 1 row affected (0.00 sec)

-- step4
mysql> insert into testd values(6,7,70);
Query OK, 1 row affected (0.01 sec)

Session 2

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

-- step2
mysql> delete from testd where a=7;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Session1	Session 2	desc
begin; Query OK, 0 rows affected (0.00 sec)
.	begin; Query OK, 0 rows affected (0.00 sec)
insert into testd values(7,7,7); Query OK, 1 row affected (0.00 sec)		占用 Gap 锁(1,7)和行锁 7
.	delete from testd where a=7;	等待行锁, gap 锁锁定 (1,7)
insert into testd values(8,7,70); Query OK, 1 row affected (0.00 sec)		为什么这行没有错, 因为主键为 8,大于 7,插入位置刚好在 gap 后面;
insert into testd values(6,7,70); Query OK, 1 row affected (0.01 sec)	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	等待 Gap 锁,造成死锁