MySQL Bugs: #116521: Write skew exception at serializable isolation level

Bug #116521	Write skew exception at serializable isolation level
Submitted:	1 Nov 2024 1:37	Modified:	4 Nov 2024 11:32
Reporter:	Huicong Xu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.0.1	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86
Tags:	Isolation level

Description:
Hello.
When I tested serializable isolation levels on MySQL9, I found a write skew exception.I get the following results.
--- session 0 ---
CREATE TABLE tWepKvSE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 BIGINT UNIQUE KEY NULL);
INSERT INTO tWepKvSE (VAL, c0) VALUES (44854, NULL);
......
INSERT INTO tWepKvSE (VAL, c0) VALUES (17350, 1982217668);

--- 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 *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (NOT (0)) OR (c0 >= -1726137651 AND c0 <= -671629208) ORDER BY tWepKvSE.c0;
+----+-------+-------------+---------+------------+
| ID | VAL   | c0          | read_id | read_value |
+----+-------+-------------+---------+------------+
|  1 | 44854 |        NULL |       1 |      44854 |
|  2 | 50744 | -2134275173 |       2 |      50744 |
|  3 | 39591 | -1008914785 |       3 |      39591 |
|  4 | 58114 |  -351361077 |       4 |      58114 |
|  5 | 32424 |  -235958547 |       5 |      32424 |
|  6 | 81150 |  -211170753 |       6 |      81150 |
|  7 | 54994 |    47626112 |       7 |      54994 |
|  8 | 41409 |    69331423 |       8 |      41409 |
|  9 | 15114 |   397883104 |       9 |      15114 |
| 10 | 27305 |   515927826 |      10 |      27305 |
| 11 | 32383 |   689941044 |      11 |      32383 |
| 12 | 83580 |   737030094 |      12 |      83580 |
| 13 | 88740 |   813918762 |      13 |      88740 |
| 14 | 92769 |   964530977 |      14 |      92769 |
| 15 | 19406 |  1144228744 |      15 |      19406 |
| 16 | 67933 |  1369607908 |      16 |      67933 |
| 17 | 93422 |  1794799621 |      17 |      93422 |
| 18 | 72374 |  1938044707 |      18 |      72374 |
| 19 | 17350 |  1982217668 |      19 |      17350 |
+----+-------+-------------+---------+------------+
19 rows in set (0.00 sec)
--- session 2 ---
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (0) AND (0) OR (c0 = 1938044707);
+----+-------+------------+---------+------------+
| ID | VAL   | c0         | read_id | read_value |
+----+-------+------------+---------+------------+
| 18 | 72374 | 1938044707 |      18 |      72374 |
+----+-------+------------+---------+------------+
1 row in set (0.00 sec)
--- session 1 ---
UPDATE tWepKvSE SET VAL = (ROUND(RAND() * 100000)), c0 = LEAST(889401561, 1323017564) WHERE (CAST(-1139874939 AS CHAR)) IN (CAST(0 AS CHAR)) OR (c0 = 1938044707);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
--- session 2 ---
DELETE FROM tWepKvSE WHERE FALSE OR (c0 >= -1726137651 AND c0 <= -671629208);
Query OK, 1 row affected (0.00 sec)
--- session 1 ---
COMMIT;
--- session 2 ---
COMMIT;

The execution above 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.

How to repeat:
--- Init Table ---
--- session 0 --- 
CREATE TABLE tWepKvSE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 BIGINT UNIQUE KEY NULL);
INSERT INTO tWepKvSE (VAL, c0) VALUES (44854, NULL);
INSERT INTO tWepKvSE (VAL, c0) VALUES (50744, -2134275173);
INSERT INTO tWepKvSE (VAL, c0) VALUES (39591, -1008914785);
INSERT INTO tWepKvSE (VAL, c0) VALUES (58114, -351361077);
INSERT INTO tWepKvSE (VAL, c0) VALUES (32424, -235958547);
INSERT INTO tWepKvSE (VAL, c0) VALUES (81150, -211170753);
INSERT INTO tWepKvSE (VAL, c0) VALUES (54994, 47626112);
INSERT INTO tWepKvSE (VAL, c0) VALUES (41409, 69331423);
INSERT INTO tWepKvSE (VAL, c0) VALUES (15114, 397883104);
INSERT INTO tWepKvSE (VAL, c0) VALUES (27305, 515927826);
INSERT INTO tWepKvSE (VAL, c0) VALUES (32383, 689941044);
INSERT INTO tWepKvSE (VAL, c0) VALUES (83580, 737030094);
INSERT INTO tWepKvSE (VAL, c0) VALUES (88740, 813918762);
INSERT INTO tWepKvSE (VAL, c0) VALUES (92769, 964530977);
INSERT INTO tWepKvSE (VAL, c0) VALUES (19406, 1144228744);
INSERT INTO tWepKvSE (VAL, c0) VALUES (67933, 1369607908);
INSERT INTO tWepKvSE (VAL, c0) VALUES (93422, 1794799621);
INSERT INTO tWepKvSE (VAL, c0) VALUES (72374, 1938044707);
INSERT INTO tWepKvSE (VAL, c0) VALUES (17350, 1982217668);
--- 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 *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (NOT (0)) OR (c0 >=-1726137651 AND c0 <=-671629208) ORDER BY tWepKvSE.c0;
--- session 2 ---
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (0) AND (0) OR (c0 =1938044707);
--- session 1 ---
UPDATE tWepKvSE SET VAL = (ROUND(RAND() * 100000)), c0 =LEAST(889401561, 1323017564) WHERE (CAST(-1139874939 AS CHAR)) IN (CAST(0 AS CHAR)) OR (c0 = 1938044707);
--- session 2 ---
DELETE FROM tWepKvSE WHERE FALSE OR (c0 >=-1726137651 AND c0 <=-671629208);
--- session 1 ---
COMMIT;
--- session 2 ---
COMMIT;

Python scripts to reproduce the bug

Attachment: reproduce.py (text/x-python), 5.43 KiB.

Hi Mr. Xu,

Thank you for your bug report.

However, we are not able to repeat it.

We have executed all the commands in the order that you provided us.

After each command we have taken a look at the locks held and those were all 100 % compliant with SERIALIZABLE isolation level.

Nothing was changed until the COMMIT.

Then both a DELETE and UPDATE were applied. We looked at the table after both COMMITs and a proper row is missing and another row was updated.

The same, proper behaviour was noticed in 8.0.40, 8.4.3, 9.0.1 and 9.1.0.

Hence, everything is in order and we can not repeat what you are reporting.

Can't repeat.

Thanks for your reply.
In my opinions,the bug lies in the fact that you are executing this code without encountering an exception.
I mean, by the definition of serializable isolation level, two transactions should circumvent such write skewed exceptions during execution.
In other words, serializability means that the result of two transactions executed concurrently is equivalent to the result of two transactions executed serially.
The result of the serial execution of the two transactions is as follows.
You'll notice that the result of our concurrent execution is not equivalent to the result of any kind of serial execution.
Specifically. For serial order 1 and 2, session2 did not read the data while the concurrent transaction did; for serial order 2 and 1, session1 read 18 pieces of data while the concurrent transaction read 19 pieces of data.

ORDER：1,2
--- session 1 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (NOT (0)) OR (c0 >= -1726137651 AND c0 <= -671629208) ORDER BY tWepKvSE.c0;
+----+-------+-------------+---------+------------+
| ID | VAL   | c0          | read_id | read_value |
+----+-------+-------------+---------+------------+
|  1 | 44854 |        NULL |       1 |      44854 |
|  2 | 50744 | -2134275173 |       2 |      50744 |
|  3 | 39591 | -1008914785 |       3 |      39591 |
|  4 | 58114 |  -351361077 |       4 |      58114 |
|  5 | 32424 |  -235958547 |       5 |      32424 |
|  6 | 81150 |  -211170753 |       6 |      81150 |
|  7 | 54994 |    47626112 |       7 |      54994 |
|  8 | 41409 |    69331423 |       8 |      41409 |
|  9 | 15114 |   397883104 |       9 |      15114 |
| 10 | 27305 |   515927826 |      10 |      27305 |
| 11 | 32383 |   689941044 |      11 |      32383 |
| 12 | 83580 |   737030094 |      12 |      83580 |
| 13 | 88740 |   813918762 |      13 |      88740 |
| 14 | 92769 |   964530977 |      14 |      92769 |
| 15 | 19406 |  1144228744 |      15 |      19406 |
| 16 | 67933 |  1369607908 |      16 |      67933 |
| 17 | 93422 |  1794799621 |      17 |      93422 |
| 18 | 72374 |  1938044707 |      18 |      72374 |
| 19 | 17350 |  1982217668 |      19 |      17350 |
+----+-------+-------------+---------+------------+
UPDATE tWepKvSE SET VAL = (ROUND(RAND() * 100000)), c0 = LEAST(889401561, 1323017564) WHERE (CAST(-1139874939 AS CHAR)) IN (CAST(0 AS CHAR)) OR (c0 = 1938044707);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
COMMIT;
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (0) AND (0) OR (c0 = 1938044707);
Empty set (0.00 sec)
DELETE FROM tWepKvSE WHERE FALSE OR (c0 >= -1726137651 AND c0 <= -671629208);
Query OK, 1 row affected (0.00 sec)
COMMIT;

ORDER：2,1
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (0) AND (0) OR (c0 = 1938044707);
+----+-------+------------+---------+------------+
| ID | VAL   | c0         | read_id | read_value |
+----+-------+------------+---------+------------+
| 18 | 72374 | 1938044707 |      18 |      72374 |
+----+-------+------------+---------+------------+
1 row in set (0.00 sec)
DELETE FROM tWepKvSE WHERE FALSE OR (c0 >= -1726137651 AND c0 <= -671629208);
Query OK, 1 row affected (0.00 sec)
COMMIT;
--- session 1 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT *, ID AS read_id, VAL AS read_value FROM tWepKvSE WHERE (NOT (0)) OR (c0 >= -1726137651 AND c0 <= -671629208) ORDER BY tWepKvSE.c0;
+----+-------+-------------+---------+------------+
| ID | VAL   | c0          | read_id | read_value |
+----+-------+-------------+---------+------------+
|  1 | 44854 |        NULL |       1 |      44854 |
|  2 | 50744 | -2134275173 |       2 |      50744 |
|  4 | 58114 |  -351361077 |       4 |      58114 |
|  5 | 32424 |  -235958547 |       5 |      32424 |
|  6 | 81150 |  -211170753 |       6 |      81150 |
|  7 | 54994 |    47626112 |       7 |      54994 |
|  8 | 41409 |    69331423 |       8 |      41409 |
|  9 | 15114 |   397883104 |       9 |      15114 |
| 10 | 27305 |   515927826 |      10 |      27305 |
| 11 | 32383 |   689941044 |      11 |      32383 |
| 12 | 83580 |   737030094 |      12 |      83580 |
| 13 | 88740 |   813918762 |      13 |      88740 |
| 14 | 92769 |   964530977 |      14 |      92769 |
| 15 | 19406 |  1144228744 |      15 |      19406 |
| 16 | 67933 |  1369607908 |      16 |      67933 |
| 17 | 93422 |  1794799621 |      17 |      93422 |
| 18 | 72374 |  1938044707 |      18 |      72374 |
| 19 | 17350 |  1982217668 |      19 |      17350 |
+----+-------+-------------+---------+------------+
18 rows in set (0.00 sec)
UPDATE tWepKvSE SET VAL = (ROUND(RAND() * 100000)), c0 = LEAST(889401561, 1323017564) WHERE (CAST(-1139874939 AS CHAR)) IN (CAST(0 AS CHAR)) OR (c0 = 1938044707);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
COMMIT;

Hi Mr. Xu,

We have not been able to repeat your test case.

Final result is exactly as it is expected.

If you want to test it with another test case, please send it in the form that we can execute directly. If you have any comments or result sets, also provide them in the comments.

Regarding your starting comments, the final result is exactly as you have pointed out. You should have omitted the fact that in concurrent transactions, each of the transactions has to wait on the COMMIT of the other transaction, before it can change the rows. And that is exactly what happened. After first transaction is committed, a second one has performed the changes that it waited upon.

Hence, we do not see any bug here.

I'm guessing that the reason you can't reproduce my bug might be a problem with the database parameter configuration. 

Therefore, I recreated a MySQL instance. And, I initialized the possibly relevant parameters in the new code.You can execute this code directly including the test case.This code will execute the test case in the order given and output the result or an exception like "Lock wait timeout exceeded".

You will notice that there are no waits you mentioned in the execution. I also provide you with the results of the code execution.

Python scripts to reproduce the bug.

Attachment: reproduce_with_config.py (text/x-python), 5.86 KiB.

The results of the code execution.

Attachment: result_set.txt (text/plain), 2.46 KiB.

Hi Mr. Xu,

We can not run a program in some programming language ...... We are doing it only for the Connectors and this bug is not a Connector / Python bug.

Also, we have sent you results that we have got. We did not get any timeout, any exception or anything else.

If you wish, we can run SQL commands one more time in the order that you send us.

Before that, you should let us know what is wrong with the results that we have got so far ..

We have posted the results, but you did not comment on what is wrong with them.

Can't repeat.