MySQL Bugs: #115978: Inconsistent results at the serializable isolation level

Bug #115978	Inconsistent results at the serializable isolation level
Submitted:	2 Sep 2024 7:06	Modified:	4 Sep 2024 15:00
Reporter:	Huicong Xu	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.0	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hello!
When I test MySQL at the serializable isolation level, I've come across a confusing situation.According to the definition of serializability in the SQL standard, the result of concurrent execution of transactions should be the same as the result of serial execution of transactions.However, I get inconsistent test results.Here's my simplified test case.
T1('BEGIN;', [])
T2('BEGIN;', [])
T1('INSERT INTO t(c0) VALUES(1);', [])
T2('INSERT INTO t(c0) VALUES(2), (3);', [])
T2('COMMIT;', [])
T1('SELECT * FROM t LIMIT 2;', ((1,), (2,)))
T1('COMMIT;;', [])
This case has two serial execution results, T1 ahead or T2 ahead.
When the execution order is T1 and T2, the result is as follows.
T1('BEGIN;', [])
T1('INSERT INTO t(c0) VALUES(1);', [])
T1('SELECT * FROM t LIMIT 2;', ((1,)))
T1('COMMIT;', [])
T2('BEGIN;', [])
T2('INSERT INTO t(c0) VALUES(2), (3);', [])
T2('COMMIT;;', [])
When the execution order is T2 and T1, the result is as follows.
T2('BEGIN;', [])
T2('INSERT INTO t(c0) VALUES(2), (3);', [])
T2('COMMIT;;', [])
T1('BEGIN;', [])
T1('INSERT INTO t(c0) VALUES(1);', [])
T1('SELECT * FROM t LIMIT 2;', ((2,), (3,)))
T1('COMMIT;', [])
We can notice that the result of the execution of this statement( SELECT * FROM t LIMIT 2;) is different from the result of any kind of serial execution.
Looking forward to your reply and thank you for your help.

How to repeat:
T1('BEGIN;', [])
T2('BEGIN;', [])
T1('INSERT INTO t(c0) VALUES(1);', [])
T2('INSERT INTO t(c0) VALUES(2), (3);', [])
T2('COMMIT;', [])
T1('SELECT * FROM t LIMIT 2;', ((1,), (2,)))
T1('COMMIT;;', [])

Hi,

What exact version did you tested with (I would like you to test with 9.0.1) and how did you set isolation level, with config for whole server or only for your session or ?

Thanks

Hello！
This version is 8.0.36-0ubuntu0.22.04.1.
mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.36-0ubuntu0.22.04.1 |
+-------------------------+
1 row in set (0.00 sec)
I set up serializable isolation levels for the entire server.
mysql> SELECT @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

Why two report?

Duplicate of Bug #115995