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

[27 Oct 2022 19:28] Aniruddha Prasad
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");
            }
        }
[28 Oct 2022 13:22] Bradley Grainger
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.
[7 Nov 2022 6:54] MySQL Verification Team
Hello Aniruddha Prasad,

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

Regards,
Ashwini Patil
[7 Nov 2022 13:40] Aniruddha Prasad
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?