Bug #119801 If two transactions concurrently execute updates on the same row, the transaction that performs the update later will fa

• Submitted: 28 Jan 13:20
• Reporter: nikki Zhang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Any
• CPU Architecture: Any

[28 Jan 13:20] nikki Zhang

Description:
Under the Read Committed isolation level, if two transactions concurrently execute updates on the same row, the transaction that performs the update later will fail in its attempt to modify that specific row.

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, 99, 2);
 -- Initial Table: 
View{
    1:[0, 1, 1]
    2:[0, 99, 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 */SELECT * FROM mtest;   -- Actual result:    [(10, 1, 1), (0, 37, 2)]
-- Expected result:  [(10, 1, 1), (10, 37, 2)]  -- Both rows should have x increased by 10
/* s1 */COMMIT;
mysql> SELECT * FROM mtest;
+------+----+------+
| x    | c0 | c1   |
+------+----+------+
|   10 |  1 |    1 |
|    0 | 37 |    2 |
+------+----+------+

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, 99, 2);
 -- Initial Table: 
View{
    1:[0, 1, 1]
    2:[0, 99, 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 */SELECT * FROM mtest;   -- Actual result:    [(10, 1, 1), (0, 37, 2)]
-- Expected result:  [(10, 1, 1), (10, 37, 2)]  -- Both rows should have x increased by 10
/* s1 */COMMIT;
mysql> SELECT * FROM mtest;
+------+----+------+
| x    | c0 | c1   |
+------+----+------+
|   10 |  1 |    1 |
|    0 | 37 |    2 |
+------+----+------+