Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.36, 12.0.2
-
None
-
Ubuntu 20.04
-
Not for Release Notes
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.
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) |