MySQL RC隔离级别下罕见的gap lock

复现方法：

 CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `id1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id1` (`id1`);

insert into test.test1 values(1,'a',1),(3,'a',3),(5,'a',5);

session1:

begin;
delete from test.test1 where id=3;
insert into test.test1 values(4,'b','3');

session2:

begin;
insert into test1 values(6,'a',6);
insert into test1 values(2,'a',2);  /* 此时进入gap锁等待

session1:

insert into test.test1 values(6,'b','6');   /* sesion2报错死锁

死锁如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-08 17:41:24 0x30a808000
*** (1) TRANSACTION:
TRANSACTION 19769, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 2
MySQL thread id 14, OS thread handle 13060816896, query id 450 localhost 127.0.0.1 root update
insert into test1 values(2,'a',2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 91 page no 4 n bits 72 index id1 of table `test`.`test1` trx id 19769 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 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 19763, ACTIVE 32 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1160, 7 row lock(s), undo log entries 2
MySQL thread id 13, OS thread handle 13061095424, query id 451 localhost 127.0.0.1 root update
insert into test.test1 values(6,'b','6')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 91 page no 4 n bits 72 index id1 of table `test`.`test1` trx id 19763 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 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 91 page no 3 n bits 80 index PRIMARY of table `test`.`test1` trx id 19763 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 000000004d39; asc     M9;;
 2: len 7; hex cb000001930110; asc        ;;
 3: len 1; hex 61; asc a;;
 4: len 4; hex 80000006; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

原因分析

mysql 的unique索引在写入数据时，分为2个阶段

1、判断当前的物理记录上是否有冲突的record(delete-marked 是不冲突)
2、如果没有冲突, 那么可以执行插入操作

我们 session1 delete的unique（即标为delete-marked）在insert后得到复用。

如果gap 上存在至少一个相同的record, 大概率是delete-marked record, 那么需要给整个range 都加上gap X lock, 加了gap X lock 以后就可以禁止其他事务在这个gap 区间插入数据, 也就是通过lock 来保证阶段1和阶段2的原子性。

如果gap 上没有相同的record, 那么就不需要进任何gap lock，可以直接变为record lock。