MySQL Bugs: #119648: Non-repeatable Reads in REPEATABLE READ Isolation Level

Bug #119648	Non-repeatable Reads in REPEATABLE READ Isolation Level
Submitted:	8 Jan 11:37
Reporter:	Kaiming Zhang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.39	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Within a single REPEATABLE READ transaction, two consecutive SELECT statements on the same table return different results, even though no data was modified within the transaction. This violates the isolation level's guarantee that all reads within a transaction should see the same consistent snapshot.

How to repeat:
CREATE TABLE mtest(c0 TEXT, c1 DOUBLE, c2 CHAR(18)) STATS_PERSISTENT = 0;
INSERT INTO mtest(c2) VALUES ("*A");
INSERT IGNORE INTO mtest(c0, c2) VALUES ("{i{Yd{8My", "X");
   
 -- Initial Table: 
View{
    1:[null, null, *A]
    2:[{i{Yd{8My, null, X]
}
/* s1 */BEGIN;
/* s1 */SELECT * FROM mtest WHERE TRUE;
+-----------+------+------+
| c0        | c1   | c2   |
+-----------+------+------+
| NULL      | NULL | *A   |
| {i{Yd{8My | NULL | X    |
+-----------+------+------+
/* s2 */BEGIN;
/* s2 */UPDATE mtest SET c0="", c1=0.408, c2="1G5z" WHERE TRUE;
/* s2 */COMMIT;
/* s1 */SELECT * FROM mtest WHERE TRUE FOR UPDATE;
+------+-------+------+
| c0   | c1    | c2   |
+------+-------+------+
|      | 0.408 | 1G5z |
|      | 0.408 | 1G5z |
+------+-------+------+
/* s1 */COMMIT;

I do not think this is a bug.

FOR UPDATE bypasses the read-view and returns the actual data in the table (READ-COMMITTED).

I think this is documented behavior (no time to find a link).

(Note: I am writing this comment as a community contribution, MySQL Verification Team might have something to add to what I write)

https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html

"A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows. "