一个奇怪的加锁案例--Execute “ if(not exists (select ..))” locked next record

一个奇怪的加锁案例–Execute “ if(not exists (select ..))” locked next record

https://bugs.mysql.com/bug.php?id=85431

1. 表结构：
   create table t(id1 int,id2 int,id3 int, unique key(id2,id3));
2. 数据：
   insert into t values(1,1,1);
   insert into t values(20,20,20);
3. 存储过程：

create a procedure:
   delimiter //
create procedure p() 
begin 
if(not exists (select 1 from t where id2=6 and id3=6)) then 
select "not exist"; 
end if; 
end//

1. 调用
   start transaction;
   call p;

2. then excute “show engine innodb status\G” in another session：
   …

   ---

   TRANSACTIONS

   ---

   …
   TABLE LOCK table yzs.t trx id 275844 lock mode IS
   RECORD LOCKS space id 127 page no 4 n bits 72 index id2 of table yzs.t trx
   id 275844 lock mode S locks gap before rec
   Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
   0: len 4; hex 80000014; asc ;;
   1: len 4; hex 80000014; asc ;;
   2: len 6; hex 000000000886; asc ;;
   …