Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0.2
-
None
-
None
-
Ubuntu 20.04
Description
In MariaDB, 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.
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 | |
+------+------+-------+----+ |