BETWEEN SQL - why is there a gap lock?

109 Views Asked by At
CREATE TABLE justpk
(
    A INT,
    B INT,
    PRIMARY KEY (A)
);

INSERT INTO justpk (A, B) VALUES (1, 1), (4, 1), (5, 1);
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 4 |    1 |
| 5 |    1 |
+---+------+

Session1:

begin;
SELECT * FROM justpk WHERE A BETWEEN 1 AND 5 FOR UPDATE;

In another random session:

INSERT INTO lockingreads.justpk (A, B) VALUES (0, 1); # allowed
UPDATE lockingreads.justpk SET B = 2 WHERE A = 1; # blocked
INSERT INTO lockingreads.justpk (A, B) VALUES (2, 1); # blocked
INSERT INTO lockingreads.justpk (A, B) VALUES (3, 1); # blocked
UPDATE lockingreads.justpk SET B = 2 WHERE A = 4; # blocked
UPDATE lockingreads.justpk SET B = 2 WHERE A = 5; # blocked
INSERT INTO lockingreads.justpk (A, B) VALUES (6, 1); # blocked // what?

why is there a gap lock after 5 if a = 6 would not be included in the "between" condition?

0

There are 0 best solutions below