Bug #119649 SERIALIZABLE Isolation Violation with Consistent Snapshot in MySQL

• Submitted: 8 Jan 12:18
• Reporter: Kaiming Zhang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Any
• CPU Architecture: Any

[8 Jan 12:18] Kaiming Zhang

Description:
MySQL's SERIALIZABLE isolation level fails to maintain transaction isolation when using START TRANSACTION WITH CONSISTENT SNAPSHOT. A transaction that starts with a consistent snapshot incorrectly sees modifications committed by other transactions that began after it, violating the serializable isolation guarantee.

How to repeat:
 CREATE TABLE mtest(x INT DEFAULT 0,c0 VARCHAR(20));
 INSERT INTO mtest(c0, x) VALUES ("<X?", 59);
 INSERT IGNORE INTO mtest(c0) VALUES ("[]]");
 -- Initial Table: 
View{
    1:[59, <X?]
    2:[0, []]]
}

/* s1 */SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
/* s1 */START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* s2 */SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
/* s2 */START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* s2 */UPDATE mtest SET c0="p9#|" WHERE TRUE;
/* s2 */COMMIT;
/* s1 */SELECT * FROM mtest WHERE TRUE;
+------+------+
| x    | c0   |
+------+------+
|   59 | p9#| |
|    0 | p9#| |
+------+------+
/* s1 *COMMIT;