Bug #119707 SERIALIZABLE Isolation Level: Concurrent Transactions with AUTO_INCREMENT Produce Non-Serializable Result

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

[16 Jan 11:27] Kaiming Zhang

Description:
When using the SERIALIZABLE isolation level, it is expected that the final result of concurrent transactions should be equivalent to a certain serial execution order. However, in the following test scenario, the result is inconsistent with any serial execution order, and the reason is related to the handling of AUTO_INCREMENT columns.

How to repeat:
CREATE TABLE mtest(x INT DEFAULT 0,c0 CHAR(5), c1 VARCHAR(5), c2 FLOAT UNIQUE AUTO_INCREMENT); 
    CREATE UNIQUE INDEX i0 ON mtest (c0(5));
    INSERT IGNORE INTO mtest(c0, c1, c2, x) VALUES ("", "K", NULL, 61);
    INSERT INTO mtest(c0, c2) VALUES ("d", NULL);
    INSERT INTO mtest(c0, c1, c2) VALUES ("y", "", NULL);
 -- Initial Table: 
View{
    1:[61, , K, 1.0]
     2:[0, d, null, 2.0]
    3:[0, y, , 3.0]
}

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
/*S1*/ BEGIN;
/*S1*/ UPDATE mtest SET c1="-WX<B" WHERE +(0.5274882784425959);
/*S2*/ BEGIN;
/*S2*/ INSERT IGNORE INTO mtest(c0, c1, c2) VALUES ("?|J*", "-0.0", NULL);(BLCOK)
/*S1*/ INSERT INTO mtest(c0, c2, x) VALUES ("4", NULL, 63);
/*S1*/ COMMIT;
/*S2*/ INSERT IGNORE INTO mtest(c1, c2) VALUES ( "N", NULL);
/*S2*/ COMMIT;  
> select * from mtest;
+------+------+-------+----+
| x    | c0   | c1    | c2 |
+------+------+-------+----+
|   61 |      | -WX<B |  1 |
|    0 | d    | -WX<B |  2 |
|    0 | y    | -WX<B |  3 |
|    0 | ?|J* | -0.0  |  4 |
|   63 | 4    | NULL  |  5 |
|    0 | NULL | N     |  6 |
+------+------+-------+----
--------order:   tx1—>tx2----------:
> select * from mtest;
+------+------+-------+----+
| x    | c0   | c1    | c2 |
+------+------+-------+----+
|   61 |      | -WX<B |  1 |
|    0 | d    | -WX<B |  2 |
|    0 | y    | -WX<B |  3 |
|   63 | 4    | NULL  |  4 |
|    0 | ?|J* | -0.0  |  5 |
|    0 | NULL | N     |  6 |
+------+------+-------+----+
-----------order:   tx2—>tx1---------:
> select * from mtest;
+------+------+-------+----+
| x    | c0   | c1    | c2 |
+------+------+-------+----+
|   61 |      | -WX<B |  1 |
|    0 | d    | -WX<B |  2 |
|    0 | y    | -WX<B |  3 |
|    0 | ?|J* | -WX<B |  4 |
|    0 | NULL | -WX<B |  5 |
|   63 | 4    | NULL  |  6 |
+------+------+-------+----+
{code}