When I tested serializable isolation levels on Mariadb, I found a write skew exception.
The test cases and results are as follows.
The execution demonstrates a write skew exception (A5B: r1[x]... .r2[y]... .w1[y]... .w2[x]... (c1 and c2 occur), ANSI).
Also, the result of this execution contains a circle of read-write dependencies, which is against the serializable isolation level.
In other words, the two concurrent transactions are not equivalent to the result of the serialized execution of the transaction. This is a contradiction to the definition of serializability.
Therefore, it seems to me that this is a logical bug for the isolation level.
--- session 0 ---
|
CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 INT NULL UNIQUE KEY);
|
INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748);
|
INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822);
|
INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076);
|
INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931);
|
--- session 1 ---
|
BEGIN;
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
--- session 2 ---
|
BEGIN;
|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
--- session 1 ---
|
BEGIN;
|
--- session 2 ---
|
BEGIN;
|
--- session 1 ---
|
SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );
|
+----+-------+-------------+
|
| ID | VAL | c0 |
|
+----+-------+-------------+
|
| 2 | 58093 | -1404643822 |
|
| 3 | 67578 | -335483076 |
|
+----+-------+-------------+
|
2 rows in set (0.00 sec)
|
--- session 2 ---
|
SELECT * FROM tYv10enE WHERE ((1) | (0)) IS UNKNOWN OR ( c0 >= 889101988 );
|
+----+-------+-----------+
|
| ID | VAL | c0 |
|
+----+-------+-----------+
|
| 4 | 63609 | 942162931 |
|
+----+-------+-----------+
|
1 row in set (0.00 sec)
|
--- session 1 ---
|
DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 );
|
Query OK, 1 row affected (0.00 sec)
|
--- session 2 ---
|
DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 );
|
Query OK, 2 rows affected (0.00 sec)
|
--- session 1 ---
|
COMMIT;
|
--- session 2 ---
|
COMMIT;
|
Critical MDEV-6905 START TRANSACTION IF NOT EXISTS
