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?