Mysql 死锁案例3-间隙锁与意向插入锁冲突

死锁复现 

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `t` */
 
insert  into `t`(`id`,`c`,`d`) values (0,0,0),(5,5,5),(10,10,10),(15,15,15)

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =3
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T3	INSERT INTO t VALUES(3, 3,3);(阻塞)
T4		INSERT INTO t VALUES(4, 4,4);（死锁）

死锁分析

注意where条件的c是普通索引

1. T1事务1加普通索引c间隙锁（0,5）写锁成功，
2. T2事务2加普通索引c临键锁写锁（0,5]和间隙锁写锁（5,10），主键id=5记录锁写锁成功
3. T3事务1加普通索引c意向插入锁写锁（0,5]阻塞
4. T4事务2加主键索引c意向插入锁（0,5）阻塞，进入循环等待，死锁

查看锁信息 SHOW ENGINE INNODB STATUS 

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-10 19:59:31 0x2580
*** (1) TRANSACTION:
TRANSACTION 486605, ACTIVE 26 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 19, OS thread handle 4172, query id 3791 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 4 n bits 80 index c of table `test`.`t` trx id 486605 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 486606, ACTIVE 17 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 20, OS thread handle 9600, query id 3796 localhost ::1 root update
INSERT INTO t VALUES(4, 4,4)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 638 page no 4 n bits 80 index c of table `test`.`t` trx id 486606 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 4 n bits 80 index c of table `test`.`t` trx id 486606 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS

update + insert死锁现象

前提，注意insert是隐式加锁，什么是Mysql隐式锁？ ，也就是insert如果觉得跟其他事务没冲突，它是不加锁的，而有可能冲突，它的加锁规则是根据情况而定的，比如MySQL实战45讲——40insert语句的锁为什么这么多？这篇文章提到的insert唯一键冲突时可能加的是读锁（insert …select ），也可能是写锁（insert into …on duplicate key update），而Mysql 死锁案例2-间隙锁与意向插入锁冲突 加的是意向插入锁，所以不要认为insert就是加意向插入锁。

1. 更新一个间隙中不存在的记录（c=3不存在）和更新一个存在的记录（c=5存在）导致这个间隙插入数据死锁（这个不存在的记录在存在的记录的加锁范围内，插入的数据也在两者的范围内）
2. 更新同一间隙中两个记录都不存在的记录也会死锁，比如上面案例把c=5改成c=4，
3. 更新间隙中相邻的两个都存在的记录可能死锁，如果插入的数据在两个记录加锁的共同间隙内则死锁（就是现象2），否则不死锁（如果两个记录不相邻，加锁范围无重叠的地方，不会死锁）

 现象1上面例子已经复现了，现象2就把c=4操作一遍即可验证，现在分析现象3：

更新两个都存在的记录可能死锁

例子1

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(3, 3,3);
T4		INSERT INTO t VALUES(4, 4,4);（阻塞）

1. T1事务1加普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功
2. T2事务2加普通索引c临键锁（5,10]和间隙锁（10,supremum）写锁成功，，加id=10主键记录锁写锁成功
3. T3事务2在（0,5]没加间隙锁，事务1的insert采用隐式锁，插入成功
4. T4事务2插入数据行(4, 4,4)在范围（0,5]被事务1加了间隙锁，间隙锁与插入意向锁冲突，故阻塞

例子2 

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(11, 11,11);（阻塞）
T4		INSERT INTO t VALUES(12, 12,12);（成功）

1. T1事务1加普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功
2. T2事务2加普通索引c临键锁（5,10]和间隙锁（10,15）写锁成功，，加id=10主键记录锁写锁成功
3. 事务1的insert因为事务2的间隙锁（10,15）而阻塞
4. T4事务2插入数据行(12, 12,12)在范围（10,15）事务1没加间隙锁，insert采用隐式锁，插入成功

例子3

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(6, 6,6);（阻塞）
T4		INSERT INTO t VALUES(7, 7,7);（死锁）

1. T1事务1加普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功
2. T2事务2加普通索引c临键锁（5,10]和间隙锁（10,15）写锁成功，，加id=10主键记录锁写锁成功
3. 事务1的insert因为事务2的间隙锁（5,10）而阻塞
4. T4事务2插入数据行(7, 7,7)在范围（5,10）被事务1加了间隙锁，等待意向插入锁，循环等待而死锁

例子4 

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(3, 3,3);
T4		INSERT INTO t VALUES(7, 7,7);（阻塞）

1. T1事务1加普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功
2. T2事务2加普通索引c临键锁（5,10]和间隙锁（10,15）写锁成功，，加id=10主键记录锁写锁成功
3. 事务1的insert因为事务2没加间隙锁（0,5）采用隐式锁插入成功
4. T4事务2插入数据行(7, 7,7)在范围（5,10）被事务1加了间隙锁，故阻塞

例子5

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(7, 7,7);（阻塞）
T4		INSERT INTO t VALUES(11, 11,11);（成功）

1. T1事务1加普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功
2. T2事务2加普通索引c临键锁（5,10]和间隙锁（10,15）写锁成功，，加id=10主键记录锁写锁成功
3. 事务1的insert因为事务2加间隙锁（5,10）而阻塞
4. T4事务2插入数据行(11, 11,11)在范围10,15）事务1没加间隙锁，故采用隐式锁插入成功

 其他情况都差不多，比如两个事务插入（11,11,11），（12,12,12）也不会死锁

这里归根结底就是同一个间隙内的间隙锁和意向插入搜的冲突。

 锁分裂

通过日志发现，上面例子中每个insert插入时，都把会update加的间隙锁分裂成两段。

原理请参考：innodb 锁分裂、继承与迁移 (文章里提到的LOCK_ORDINARY就是临键锁)和MySQL 从一个死锁案例到锁分裂 。insert加锁的两个结论：

1. 如果插入数据行的下一条记录上没有锁或有锁但是与插入意向锁不冲突，均使用隐式锁；（说简单点就是当前插入的间隙内，如果没有其他事务加锁，那insert就是隐式锁）

2. 同一个事务持有 gap lock 的前提下插入数据，会发生锁分裂，如果是其他事务持有 gap lock，会发生插入意向锁等待。（如果在间隙外插入，不会锁分裂）

结论1

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2		START TRANSACTION ; UPDATE t SET d =0 WHERE c =10
T3	INSERT INTO t VALUES(16, 16,16);

行数据(16, 16,16)或者（11,11,11）下一个记录是上限值和15，没加锁，采用隐式锁；(16, 16,16)换成（3,3,3）即插入数据行的下一条记录有锁，但c=5这个记录锁是事务1加的，与事务1的意向插入锁不冲突，也是隐式锁。（说简单点就是当前插入的间隙内，如果没有其他事务加锁，那insert就是隐式锁）

结论2

	事务1
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =5
T2	INSERT INTO t VALUES(3, 3,3);

同一个事务持有 gap lock （临键锁也一样）的前提下插入数据，会发生锁分裂：执行T1，加锁情况是普通索引c临键锁（0,5]和间隙锁（5,10）写锁成功，加id=5主键记录锁写锁成功，共3把锁

执行T2，锁分裂，加锁情况成了（0,3）（3,5]和（5,10）以及主键id=5的记录锁。（0，5]分裂成了（0,3）（3,5]。

如果是其他事务持有 gap lock，会发生插入意向锁等待。

	事务1	事务2
T1	START TRANSACTION ; UPDATE t SET d =0 WHERE c =4
T2		START TRANSACTION ; INSERT INTO t VALUES(4, 4,4);（阻塞）

事务1持有（0,5）间隙锁写锁，事务申请（0,5）意向插入锁阻塞。