0

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".

2
  • "they say, InnoDB will acquire a gap locks only on the preceding gap". Not sure where you see that. On the linked page I read: "a lock on a gap between index records, or a lock on the gap before the first or after the last index record".
    – mustaccio
    Commented Sep 23, 2022 at 14:29
  • Maybe it is finding the row after "7" and locking the gap before that? Please provide SHOW ENGINE=INNODB STATUS;
    – Rick James
    Commented Sep 23, 2022 at 19:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.