Bug #117835 Non Repeatable Read WITH CONSISTENT SNAPSHOT

• Submitted: 31 Mar 10:44
• Modified: 1 Apr 4:44
• Reporter: Huicong Xu
• Status: Analyzing
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 9.2.0
• OS: Any (Ubuntu 22.04)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[31 Mar 10:44] Huicong Xu

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;