MySQL中死锁的场景【带索引的表一边删除一遍插入】

案例背景

MySQL版本：5.7
事务隔离级别： RR

CREATE TABLE `ty` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
INSERT INTO ty(a,b) VALUES(2,3),(5,4),(6,7);

	session 1	session 2
step-1	mysql> begin; Query OK, 0 rows affected (0.00 sec)
step-2	mysql> delete from ty where a=5; Query OK, 0 rows affected (0.00 sec)
step-3		mysql> begin; Query OK, 0 rows affected (0.00 sec)
step-4		mysql> delete from ty where a=5;
step-5	mysql> insert into ty(a,b) values(2,10); Query OK, 1 row affected (0.11 sec)
step-6		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看死锁日志

mysql> SHOW engine innodb STATUS\G
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 21:17:45 0x7f021ce44700
*** (1) TRANSACTION:
TRANSACTION 559477071, ACTIVE 7 sec starting INDEX READ
mysql TABLES IN USE 1, locked 1
LOCK WAIT 2 LOCK struct(s), heap SIZE 1136, 1 ROW LOCK(s)
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1765819361 localhost root updating
DELETE FROM  ty WHERE  a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559477071 lock_mode X waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** (2) TRANSACTION:
TRANSACTION 559476990, ACTIVE 19 sec inserting
mysql TABLES IN USE 1, locked 1
5 LOCK struct(s), heap SIZE 1136, 4 ROW LOCK(s), undo log entries 2
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1765820237 localhost root UPDATE
INSERT INTO ty(a,b) VALUES(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559476990 lock_mode X
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559476990 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** WE ROLL BACK TRANSACTION (1)
 
------------
TRANSACTIONS
------------
---TRANSACTION 559476990, ACTIVE 29 sec
5 LOCK struct(s), heap SIZE 1136, 5 ROW LOCK(s), undo log entries 2
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1765820237 localhost root cleaning up
TABLE LOCK TABLE `okami`.`ty` trx id 559476990 LOCK mode IX
RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559476990 lock_mode X
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
RECORD LOCKS SPACE id 5470 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`ty` trx id 559476990 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000002; ASC     ;;
 1: len 6; hex 00002158f0fe; ASC   !X  ;;
 2: len 7; hex 3c00005fcb277a; ASC <  _ 'z;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000004; asc     ;;
 
RECORD LOCKS space id 5470 page no 4 n bits 72 index idxa of table `okami`.`ty` trx id 559476990 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000003; asc     ;;
 
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 
RECORD LOCKS space id 5470 page no 4 n bits 72 index idxa of table `okami`.`ty` trx id 559476990 lock_mode X locks gap before rec insert intention
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000002; asc     ;;

事务1执行的SQL： DELETE FROM  ty WHERE  a=5
事务1等待的锁： (Next-Key锁) RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559477071 lock_mode X waiting


事务2执行的SQL： INSERT INTO ty(a,b) VALUES(2,10)
事务2持有的锁： 
    （记录锁）RECORD LOCKS SPACE id 5470 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`ty` trx id 559476990 lock_mode X locks rec but NOT gap
    （间隙锁）RECORD LOCKS space id 5470 page no 4 n bits 72 index idxa of table `okami`.`ty` trx id 559476990 lock_mode X locks gap before rec
    （插入意向锁）RECORD LOCKS space id 5470 page no 4 n bits 72 index idxa of table `okami`.`ty` trx id 559476990 lock_mode X locks gap before rec insert intention
事务2等待的锁： （意向插入锁）RECORD LOCKS SPACE id 5470 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty` trx id 559476990 lock_mode X locks gap BEFORE rec INSERT intention waiting

事务1等待获取Next-Key锁。事务2等待获取插入意向锁
Next-Key锁和插入意向锁、Next-Key锁同时冲突。所以，事务1需要等待事务2释放Next-Key锁以后才能获得锁。
事务2因为已经有了Next-Key锁，所以无法获得插入意向锁
由此造成了死锁问题

1、session1 执行 delete操作， 因为idxa索引是普通索引，所以需要获取gap锁或者Next-Key锁. 因为要删除的记录在索引上存在，所以选择Next-Key锁
2、session2 执行delete操作，同样要获取Next-Key锁。由于Next-Key锁之间是相互冲突的，所以进入了等待状态
3、session1 执行insert操作，需要获取插入意向锁。插入意向锁和Next-Key锁之间是相互冲突的，所以形成了死锁

官网对于delete操作的加锁逻辑描述如下

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend 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, and for non-unique indexes, 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.

对于delete操作，加锁逻辑如下
- 如果走唯一索引并且命中索引，InnoDB只加记录锁，没有间隙锁
- 如果走普通索引或者没有命中索引，InnoDB加Gap锁或者Next-key锁

CREATE TABLE `ty2` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idxa` (`a`) -- 唯一索引
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
 
INSERT INTO ty2(a,b) VALUES(2,3),(5,4),(6,7)

	session 1	session 2
step-1	mysql> begin; Query OK, 0 rows affected (0.00 sec)
step-2	mysql> delete from ty2 where a=5; Query OK, 0 rows affected (0.00 sec)
step-3		mysql> begin; Query OK, 0 rows affected (0.00 sec)
step-4		mysql> delete from ty2 where a=5;
step-5	mysql> insert into ty2 (a,b) values(3,10); Query OK, 1 row affected (0.11 sec)
step-6		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 22:07:53 0x7f021ce44700
*** (1) TRANSACTION:
TRANSACTION 559514360, ACTIVE 7 sec starting INDEX READ
mysql TABLES IN USE 1, locked 1
LOCK WAIT 2 LOCK struct(s), heap SIZE 1136, 1 ROW LOCK(s)
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1766025948 localhost root updating
DELETE FROM  ty2 WHERE  a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514360 lock_mode X waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** (2) TRANSACTION:
TRANSACTION 559514239, ACTIVE 15 sec inserting
mysql TABLES IN USE 1, locked 1
4 LOCK struct(s), heap SIZE 1136, 3 ROW LOCK(s), undo log entries 2
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1766026263 localhost root UPDATE
INSERT INTO ty2 (a,b) VALUES(3,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
*** WE ROLL BACK TRANSACTION (1)
 
------------
TRANSACTIONS
------------
---TRANSACTION 559514239, ACTIVE 53 sec
4 LOCK struct(s), heap SIZE 1136, 3 ROW LOCK(s), undo log entries 2
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1766026263 localhost root cleaning up
TABLE LOCK TABLE `okami`.`ty2` trx id 559514239 LOCK mode IX
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
RECORD LOCKS SPACE id 5471 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000002; ASC     ;;
 1: len 6; hex 00002159827f; ASC   !Y  ;;
 2: len 7; hex 3d00005ca00450; ASC =  \  P;;
 3: len 4; hex 80000005; ASC     ;;
 4: len 4; hex 80000004; ASC     ;;
 
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks gap BEFORE rec INSERT intention
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;

于案例1相比，索引的类型发生了变化。案例1的索引是非唯一索引，案例2的索引是唯一索引
4 lock struct(s)：分别为IX、idxa的记录锁、PRIMARY的记录锁、idxa的插入意向锁
3 row lock(s)：该事务持有3个行锁。除去IX的都是算在row lock里面

事务1执行的SQL：DELETE FROM  ty2 WHERE  a=5
事务1等待的锁：（Next-Key锁） RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514360 lock_mode X waiting

事务2执行的SQL：INSERT INTO ty2 (a,b) VALUES(3,10)
事务2持有的锁： （记录锁）RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks rec but NOT gap
事务2等待的锁：（插入意向锁） RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559514239 lock_mode X locks gap BEFORE rec INSERT intention waiting

事务1等待获取Next-Key锁。事务2等待获取插入意向锁
已有记录锁，无法获取Next-Key锁。事务1进入等待状态
事务2需要等待事务1获得Next-Key锁并释放才能继续
由此造成了死锁问题

session1 执行sql delete from ty2 where a=5;, 查看锁情况如下

---TRANSACTION 559572391, ACTIVE 4 sec
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 1766179965 localhost root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`ty2` trx id 559572391 LOCK mode IX
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559572391 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
RECORD LOCKS SPACE id 5471 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`ty2` trx id 559572391 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000002; ASC     ;;
 1: len 6; hex 0000215a65a7; ASC   !Ze ;;
 2: len 7; hex 3600004e410110; ASC 6  NA  ;;
 3: len 4; hex 80000005; ASC     ;;
 4: len 4; hex 80000004; ASC     ;;

session1 持有主键的记录锁和索引idxa的记录锁

session2 执行sql delete from ty2 where a=5;, 查看锁情况如下

---TRANSACTION 559573679, ACTIVE 3 sec starting index read
mysql TABLES IN USE 1, locked 1
LOCK WAIT 2 LOCK struct(s), heap SIZE 1136, 1 ROW LOCK(s)
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1766182773 localhost root updating
DELETE FROM  ty2 WHERE  a=5
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559573679 lock_mode X waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
------------------
TABLE LOCK TABLE `okami`.`ty2` trx id 559573679 LOCK mode IX
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559573679 lock_mode X waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
---TRANSACTION 559572391, ACTIVE 40 sec
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 1766182849 localhost root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`ty2` trx id 559572391 LOCK mode IX
RECORD LOCKS SPACE id 5471 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`ty2` trx id 559572391 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; ASC     ;;
 1: len 4; hex 80000002; ASC     ;;
 
RECORD LOCKS SPACE id 5471 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`ty2` trx id 559572391 lock_mode X locks rec but NOT gap
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000002; ASC     ;;
 1: len 6; hex 0000215a65a7; ASC   !Ze ;;
 2: len 7; hex 3600004e410110; ASC 6  NA  ;;
 3: len 4; hex 80000005; ASC     ;;
 4: len 4; hex 80000004; ASC     ;;

session2 等待获取锁主键的Next-Key锁和索引idxa的Next-Key锁

sessioon1 执行sql insert into ty2 (a,b) values(3,10);，应该要获取意向插入锁。

session1 等待获取意向插入锁， 持有记录锁。
session2 等待获取Next-Key锁。 session1需要等待session2获得锁并释放锁
由此造成了死锁

1、session1 执行 delete操作， 因为idxa索引是唯一索引且要删除的记录存在，获取到Record锁
2、session2 执行delete操作，由于别的事务已经删除了这一行。所以需要获取Next-Key锁。Next-Key锁和Record锁之间是相互冲突的，所以进入了等待状态
3、session1 执行insert操作，需要获取插入意向锁。Next-Key锁插入意向锁和之间是相互冲突的，所以形成了死锁

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.