Description:
When I tested serialisable isolation levels, I found a problem.
As the example below shows, the transaction reads an insert from another transaction. However, if the snapshot is created at the beginning of the transaction, it seems to me that the transaction should not read the insert of another transaction, which is guaranteed by the weaker repeatable read isolation level.
--- I see
--- session 0
CREATE TABLE tas5ohA3 (ID INT, VAL INT, c0 DOUBLE UNIQUE , c1 INT UNIQUE, c2 DOUBLE );
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5946, 6599, 3026.2602, 9648, 8132.5963);
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5947, 6600, 6757.5569, 3514, 2931.5352);
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5948, 6601, 943.9966, 4070, 3592.9069);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES ( 5949, 6602, 4431.4091, 134, 5572.8797);
COMMIT;
--- session 2
SELECT * FROM tas5ohA3 WHERE ( tas5ohA3.c0 <= 5635.9980 AND tas5ohA3.c0 > 1073.6968 ) AND (tas5ohA3.c0 >= 100) AND (tas5ohA3.c1 >= 100) ORDER BY tas5ohA3.ID;
+------+------+-----------+------+-----------+
| ID | VAL | c0 | c1 | c2 |
+------+------+-----------+------+-----------+
| 5946 | 6599 | 3026.2602 | 9648 | 8132.5963 |
| 5949 | 6602 | 4431.4091 | 134 | 5572.8797 |
+------+------+-----------+------+-----------+
2 rows in set (0.00 sec)
COMMIT;
How to repeat:
--- session 0
CREATE TABLE tas5ohA3 (ID INT, VAL INT, c0 DOUBLE UNIQUE , c1 INT UNIQUE, c2 DOUBLE );
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5946, 6599, 3026.2602, 9648, 8132.5963);
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5947, 6600, 6757.5569, 3514, 2931.5352);
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES (5948, 6601, 943.9966, 4070, 3592.9069);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
INSERT INTO tas5ohA3 (ID, VAL, c0, c1, c2) VALUES ( 5949, 6602, 4431.4091, 134, 5572.8797);
COMMIT;
--- session 2
SELECT * FROM tas5ohA3 WHERE ( tas5ohA3.c0 <= 5635.9980 AND tas5ohA3.c0 > 1073.6968 ) AND (tas5ohA3.c0 >= 100) AND (tas5ohA3.c1 >= 100) ORDER BY tas5ohA3.ID;
COMMIT;
