Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.7.2, 10.11
-
Ubuntu 22.04
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.
--- session 0 (init database)
|
DROP TABLE tKRijnTy; |
CREATE TABLE tKRijnTy (ID INT, VAL INT, c0 DOUBLE , c1 BIGINT UNSIGNED , c2 TEXT ); |
INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (19, 21, 2036.4166, 7408, 'fUt'); |
INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (20, 22, 7537.9400, 9201, '1459352324'); |
INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (21, 23, 4034.0071, 9788, 'V'); |
|
|
--- session 1
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
--- session 2
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
--- session 1
|
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
--- session 2
|
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
--- session 1
|
INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 25, 27, 3783.9088, 9258, "1459352324"); |
--- session 2
|
INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 26, 28, 3740.4026, 3161, ''); |
COMMIT; |
--- session 1
|
SELECT ID, VAL, tKRijnTy.c0, tKRijnTy.c1, tKRijnTy.c2 FROM tKRijnTy WHERE ( tKRijnTy.c0 > 4034.0071 OR tKRijnTy.c0 <= 7537.9400) ORDER BY tKRijnTy.ID; |
+------+------+-----------+------+------------+ |
| ID | VAL | c0 | c1 | c2 |
|
+------+------+-----------+------+------------+ |
| 19 | 21 | 2036.4166 | 7408 | fUt |
|
| 20 | 22 | 7537.94 | 9201 | 1459352324 |
|
| 21 | 23 | 4034.0071 | 9788 | V |
|
| 25 | 27 | 3783.9088 | 9258 | 1459352324 |
|
| 26 | 28 | 3740.4026 | 3161 | |
|
+------+------+-----------+------+------------+ |
5 rows in set (0.01 sec) |
COMMIT; |