Bug #119632 Inconsistent Auto-increment Primary Key Allocation Under Concurrent Operations Violates Transaction Consistency

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

[6 Jan 12:48] Kaiming Zhang

Description:
Under the REPEATABLE-READ isolation level, a specific sequence of concurrent operations causes a gap in the auto-increment primary key allocation. This violates transaction consistency because a rolled-back transaction should not have a permanent effect on the auto-increment sequence, leading to discontinuity in the primary key values.

How to repeat:
CREATE TABLE mtest(c0 DOUBLE, c1 DOUBLE, c2 INT PRIMARY KEY AUTO_INCREMENT) ;
INSERT INTO mtest(c0, c2) VALUES (0.6, NULL);
INSERT INTO mtest(c1, c2) VALUES (0.3, NULL);
/* s1 /BEGIN;
/* s2 /BEGIN;
/* s2 /INSERT IGNORE INTO mtest(c1, c2) VALUES (0.63, NULL);
/* s2 /DELETE FROM mtest WHERE TRUE;
/* s2 /ROLLBACK;
/* s1 /INSERT IGNORE INTO mtest(c0, c1, c2) VALUES (0.11, 0.09, NULL);
/* s1 /COMMIT;
mysql> select * from mtest;          
+------+------+----+
| c0 | c1 | c2 |
+------+------+----+
| 0.6 | NULL | 1 |
| NULL | 0.3 | 2 |
| 0.11 | 0.09 | 4 |
+------+------+----+
3 rows in set (0.00 sec)
--------- Expected result:---------
mysql> select * from mtest;
+------+------+----+
| c0 | c1 | c2 |
+------+------+----+
| 0.6 | NULL | 1 |
| NULL | 0.3 | 2 |
| 0.11 | 0.09 | 3 |
+------+------+----+
3 rows in set (0.00 sec)