MySQL Bugs: #108904: Setting isolation level to serializable doesn't really set it.

Bug #108904	Setting isolation level to serializable doesn't really set it.
Submitted:	27 Oct 2022 19:28	Modified:	7 Nov 2022 13:40
Reporter:	Aniruddha Prasad	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Windows (11)
Assigned to:		CPU Architecture:	x86

Description:
When using MySql.Data to set the transaction isolation level to Serializable transactions are actually executed under Repeatable Read and not Serializable isolation.

Server version:  8.0.31 MySQL Community Server

Please find the code below to reproduce the issue. The test method fails.

How to repeat:
[TestMethod]
        public void TestBuggyIL()
        {
            var connection = new MySqlConnection(ConnectionString);
            connection.Open();
            var transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
            MySqlCommand command = new("SELECT @@transaction_isolation;", connection, transaction);
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                string value = reader.GetString(0);
                Assert.AreEqual(value, "SERIALIZABLE");
            }
        }

This may not be a Connector/NET issue. Executing the following SQL in MySQL Workbench shows the same results:

set transaction isolation level serializable;
start transaction;
select @@transaction_isolation;
rollback;

-- @@transaction_isolation
-- REPEATABLE-READ

This may be a duplicate of bug #53341 -- there is no way to read the current transaction's isolation level.

Hello Aniruddha Prasad,

Thank you for the bug report.
Imho this is duplicate of Bug #53341, please see Bug #53341.

Regards,
Ashwini Patil

Hi,

Thanks for that. I have code that will reproduce non serializable isolation level even after setting isolation level in code.

I will need some time (3-4 days) to make modifications to my code to reproduce the issue with minimal dependencies. Could this ticket be worked on till then?