復現方法：
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)