MySQL Bugs: #117487: Serializable Isolation Violation (Write Skew) under Specific Concurrent Transactions

Bug #117487	Serializable Isolation Violation (Write Skew) under Specific Concurrent Transactions
Submitted:	17 Feb 8:43	Modified:	25 Feb 1:59
Reporter:	Huicong Xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.2.0	OS:	Any (22.04)
Assigned to:		CPU Architecture:	Any

Description:
When I tested serializable isolation levels on MySQL, 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;
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)
--- session 1 ---
BEGIN;
--- session 2 ---
BEGIN;
--- session 1 ---
SELECT * FROM tYv10enE WHERE c0 <= 1;
+----+-------+-------------+
| ID | VAL   | c0          |
+----+-------+-------------+
|  2 | 58093 | -1404643822 |
|  3 | 67578 |  -335483076 |
+----+-------+-------------+
2 rows in set (0.00 sec)
--- session 2 ---
SELECT * FROM tYv10enE WHERE c0 >= 889101988;
+----+-------+-----------+
| ID | VAL   | c0        |
+----+-------+-----------+
|  4 | 63609 | 942162931 |
+----+-------+-----------+
1 row in set (0.00 sec)
--- session 1 ---
DELETE FROM tYv10enE WHERE c0 >= 889101988;
Query OK, 1 row affected (0.00 sec)
--- session 2 ---
DELETE FROM tYv10enE WHERE c0 <= 1;
Query OK, 2 rows affected (0.00 sec)
--- session 1 ---
COMMIT;
--- session 2 ---
COMMIT;

How to repeat:
--- 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;
select @@transaction_isolation;
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select @@transaction_isolation;
--- session 1 ---
BEGIN;
--- session 2 ---
BEGIN;
--- session 1 ---
SELECT * FROM tYv10enE WHERE c0 <= 1;
--- session 2 ---
SELECT * FROM tYv10enE WHERE c0 >= 889101988;
--- session 1 ---
DELETE FROM tYv10enE WHERE c0 >= 889101988;
--- session 2 ---
DELETE FROM tYv10enE WHERE c0 <= 1;
--- session 1 ---
COMMIT;
--- session 2 ---
COMMIT;

The default initial isolation level is Repeatable Read.

Hi Mr. Xu,

Thank you for your bug report.

However, the results that you get are in complete alignment with SERIALIZABLE isolation level.

The results that you are getting are aligned fully with this isolation level.

Here is it's descrition:

--------------------------------------------------------------------------
SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

DML operations that read data from MySQL grant tables (through a join list or subquery) but do not modify them do not acquire read locks on the MySQL grant tables, regardless of the isolation level. For more information, see Grant Table Concurrency.

--------------------------------------------------------------------------

Hence, this is expected behaviour.

If you still do not think that this is expected behaviour, please pinpoint the result that you get and why do you think that it is not in line with the above description.

Not a bug.

Thanks for your response.

Do you mean that this statement “SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;” has no effect, i.e., the two transactions after this statement are still running under Repeatable Read isolation level?

There is also a definition of serializable in the sql standard:
“The execution of concurrent SQL transactions at the transaction isolation level SERIALIZABLE is guaranteed to be serializable. Serializable execution is defined as the execution of operations of concurrently executing SQL transactions that have the same effect as the serial execution of some of those same SQL transactions”.

If the two transactions after the SET statement run at the serializable isolation level, their results are not the same as the results of serial execution. This is because no matter which transaction executes first, this transaction will delete the data, while the other thing will not read the data.

Hi Mr. Xu,

Why don't you read our Reference Manual.

There you will find that SERIALIZABLE is implemented by setting locks.

Also, your example, when run with repeatable read should produce different results.

Have you tried that ?????

Hi.

Firstly, when executing the example under the Repeatable Read isolation level, the observed outcomes remain consistent with the aforementioned results.

Secondly, while the reference manual explicitly states that serializability is achieved through locking mechanisms, it's noteworthy that this implementation's behavior appears to contradict the definition of Serializable as stipulated in the SQL Standard (ISO/IEC 9075). Specifically, the execution results in this scenario demonstrate anomalies that violate the standard's requirements. Considering that most user may be more familiar with the SQL Standard, this divergence raises an question: Does the current locking-based implementation satisfy the serializability criteria defined by the SQL Standard?

Or is it that I need to disable autocommit if I want the execution results to be consistent with those in the SQL Standard?

Hi Mr. Xu,

This behaviour is fully compliant with SQL and MVCC standards.

Actually, it was checked by one of our system architects, who is a member of the SQL Committee. His name I cannot reveal to anybody. That person is no longer employed with us.

Also, our product has passed all relevant certifications and nobody ever found a glitch in that respect.

Your test case is not very well designed for that purpose. Hence, why don't you find examples in our Reference Manual.

With SERIALIZABLE, two concurrent transactions will definitely to one of them waiting on some lock. The same transaction would have much less lock waits under other isolation levels.

Not a bug.

You were presenting all this in auto-commit mode ????

No comments.

I think I probably know what the problem is. Setting the isolation level in a transaction will not work for the transaction.

In this test case:
(1) Begin;
(2) SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
(3) Begin;

When I set the isolation level directly, i.e., without statement (1), I find that the delete statement is blocked.But not in the above example.

mysql> DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 );
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Does this mean that I shouldn't set the isolation level this way, or that my setting isn't taking effect?

Hi Mr. Xu,

Setting isolation level takes effect after the next BEGIN.

This is all explained in our Reference Manual.