Under repeatable_read after ...
drop table if exists test;
create table test( id int primary key, i int, key(i) );
insert into test values(1,10),(2,11),(3,13),(4,20);
start transaction;
select * from test where i=17 for update ;
... then in another client, this ...
insert into test set id=5,i=13;
... times out. InnoDB row locks are ...
SELECT * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:2428:1727578871128
ENGINE_TRANSACTION_ID: 121052
THREAD_ID: 222
EVENT_ID: 129
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1727578871128
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:987:5:5:1727578868344
ENGINE_TRANSACTION_ID: 121052
THREAD_ID: 222
EVENT_ID: 129
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 20,
Still under repeatable_read and the same setup, after ...
select * from test where i between 13 and 20 for update ;
on one client, another client trying to execute ...
insert into test set id=6,i=99;
... times out like your id=5,i=13 example. InnoDB locks are ...
SELECT * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:2426:1727578871128
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1727578871128
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:985:5:1:1727578868344
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:985:5:4:1727578868344
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 13, 3
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:985:5:5:1727578868344
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 20, 4
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:985:4:4:1727578868688
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1727578868688
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 1727614845088:985:4:5:1727578868688
ENGINE_TRANSACTION_ID: 120997
THREAD_ID: 222
EVENT_ID: 106
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1727578868688
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 4
The issue appears to be more complicated than just the left border of one gap lock.