Description:
Hello.
When I was testing the database, I found a problem.
As in the example below, with the repeatable read isolation level, the results of two reads of a transaction are not consistent. In my opinion, this is a non-repeatable read exception.
--- I see
--- session 0 (init database)
CREATE TABLE t8pYIteS (ID INT, VAL INT, c0 VARCHAR(100) NULL, c1 BOOLEAN , c2 DECIMAL(45, 18) PRIMARY KEY NOT NULL );
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3511, 3882, "", 1, 5550.5802);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3512, 3883, '434619445', 1, 789.6063);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3513, 3884, 'V 鋦v9&jz⃭', 0, 9538.9657);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3514, 3885, '', 1, 3906.7810);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID;
+------+------+-----------+------+-------------------------+
| ID | VAL | c0 | c1 | c2 |
+------+------+-----------+------+-------------------------+
| 3511 | 3882 | | 1 | 5550.580200000000000000 |
| 3512 | 3883 | 434619445 | 1 | 789.606300000000000000 |
| 3514 | 3885 | | 1 | 3906.781000000000000000 |
+------+------+-----------+------+-------------------------+
3 rows in set (0.00 sec)
--- session 2
UPDATE t8pYIteS SET VAL = 3894, t8pYIteS.c0 = '?', t8pYIteS.c1 = 1 WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
COMMIT;
--- session 1
SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID FOR UPDATE;
+------+------+------+------+-------------------------+
| ID | VAL | c0 | c1 | c2 |
+------+------+------+------+-------------------------+
| 3511 | 3894 | ? | 1 | 5550.580200000000000000 |
| 3512 | 3894 | ? | 1 | 789.606300000000000000 |
| 3514 | 3894 | ? | 1 | 3906.781000000000000000 |
+------+------+------+------+-------------------------+
3 rows in set (0.00 sec)
COMMIT;
--- Expected to See (MariaDB Show)
mysql> SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID;
+------+------+-----------+------+-------------------------+
| ID | VAL | c0 | c1 | c2 |
+------+------+-----------+------+-------------------------+
| 3511 | 3882 | | 1 | 5550.580200000000000000 |
| 3512 | 3883 | 434619445 | 1 | 789.606300000000000000 |
| 3514 | 3885 | | 1 | 3906.781000000000000000 |
+------+------+-----------+------+-------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID FOR UPDATE;
ERROR 1020 (HY000): Record has changed since last read in table 't8pYIteS'
How to repeat:
--- session 0 (init database)
CREATE TABLE t8pYIteS (ID INT, VAL INT, c0 VARCHAR(100) NULL, c1 BOOLEAN , c2 DECIMAL(45, 18) PRIMARY KEY NOT NULL );
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3511, 3882, "", 1, 5550.5802);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3512, 3883, '434619445', 1, 789.6063);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3513, 3884, 'V 鋦v9&jz⃭', 0, 9538.9657);
INSERT INTO t8pYIteS (ID, VAL, c0, c1, c2) VALUES (3514, 3885, '', 1, 3906.7810);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID;
--- session 2
UPDATE t8pYIteS SET VAL = 3894, t8pYIteS.c0 = '?', t8pYIteS.c1 = 1 WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100);
COMMIT;
--- session 1
SELECT * FROM t8pYIteS WHERE ( t8pYIteS.c1 = 1 ) AND (t8pYIteS.c2 >= 100) ORDER BY t8pYIteS.ID FOR UPDATE;
COMMIT;