I am currently learning about Gap Lock
in the InnoDB engine, In MySQL doc they say,
InnoDB will acquire a gap locks only on the preceding gap, but on this Percona article as I understood from their example, InnoDB will acquire gap locks on the pre and post gaps!
I tried to do my tests
CREATE TABLE T ( INT ID PRIMARY KEY);
INSERT INTO T
VALUES (1), (2), (4), (7), (9);
//session-1
BEGIN;
//session-2
BEGIN;
//session-1
SELECT ID FROM T WHERE ID BETWEEN 4 and 7 FOR UPDATE;
//session-2
INSERT INTO T VALUES(3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
INSERT INTO T VALUES(8);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
//session-1
commit;
//session-2
INSERT INTO T VALUES(8);
Query OK, 1 row affected (0.000 sec)
commit;
So the result is the same as the result described in Percona article! Is MySQL doc wrong or i just missed something? I really can't understand.
P.S: the Isolation-level for both sessions is the default one "REPATABLE READ".
SHOW ENGINE=INNODB STATUS;