Bug #119409 Update fails to update data consistently after locking is released

• Submitted: 17 Nov 9:32
• Reporter: Kaiming Zhang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Any
• CPU Architecture: Any
• Tags: 块

[17 Nov 9:32] Kaiming Zhang

Description:
Simply changing the initial value of c0 (PRIMARY KEY) will ultimately cause updates to fail. From a user perspective, I believe this discrepancy is erroneous.

How to repeat:
DROP TABLE IF EXISTS mtest;
CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT);
INSERT INTO mtest VALUES (0, 1, 1), (0, 2 , 2);
 -- Initial Table: 
View{
    1:[0, 1, 1]
    2:[0, 2, 2]
}
/* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s1 */BEGIN;
/* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s2 */BEGIN;
/* s2 */UPDATE mtest SET c0=37 WHERE c1=2;
/* s1 */UPDATE mtest SET x = x + 10 WHERE True;（block）
/* s2 */COMMIT;
/* s1 */COMMIT;
mysql> SELECT * FROM mtest;
+------+----+------+
| x    | c0 | c1   |
+------+----+------+
|   10 |  1 |    1  |
|   10 | 37 |    2 |
+------+----+------+
 
DROP TABLE IF EXISTS mtest;
CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT);
INSERT INTO mtest VALUES (0, 1, 1), (0, 67, 2);
 -- Initial Table: 
View{
    1:[0, 1, 1]
    2:[0, 67, 2]
}
/* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s1 */BEGIN;
/* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s2 */BEGIN;
/* s2 */UPDATE mtest SET c0=37 WHERE c1=2;
/* s1 */UPDATE mtest SET x = x + 10 WHERE True;（block）
/* s2 */COMMIT;
/* s1 */COMMIT;
mysql> SELECT * FROM mtest;
+------+----+------+
| x    | c0 | c1   |
+------+----+------+
|   10 |  1 |    1 |
|    0 | 37 |    2 |
+------+----+------+

Suggested fix:
The update to x should take effect.