MySQL Bugs: #86263: Wrong Isolation Level for Begin Transaction

Bug #86263	Wrong Isolation Level for Begin Transaction
Submitted:	10 May 2017 8:10	Modified:	27 Mar 2020 20:54
Reporter:	ZOKIAD ZOKIAD	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	isolation level begintransaction

Description:
Hi,

When create a transaction with BeginTransaction, isolation level is set for all SESSION.

When execute:
                            using (MySqlTransaction transaction = db.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))

The generated SQL is:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
...

the issue is the isolation level is for all session. All SQL command after a begin transaction keep the isolation level (and connection is in the pooling).

How to repeat:
Follow a example in C#:
                            using (MySqlConnection db = new MySqlConnection("XXX"))
                            {
                                db.Open();
                                MySqlCommand cmd = new MySqlCommand("SELECT variable_value IsolationLevel FROM information_schema.session_variables WHERE variable_name = 'tx_isolation'", db);
                                Console.WriteLine("Isolation Level after connextion: " + cmd.ExecuteScalar().ToString());

                                using (MySqlTransaction transaction = db.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                                {
                                    MySqlCommand cmd2 = new MySqlCommand("SELECT variable_value IsolationLevel FROM information_schema.session_variables WHERE variable_name = 'tx_isolation'", db);
                                    Console.WriteLine("Isolation Level in transaction: " + cmd2.ExecuteScalar().ToString());
                                }

                                MySqlCommand cmd3 = new MySqlCommand("SELECT variable_value IsolationLevel FROM information_schema.session_variables WHERE variable_name = 'tx_isolation'", db);
                                Console.WriteLine("Isolation Level after transaction: " + cmd3.ExecuteScalar().ToString());
                            }

Suggested fix:
When execute:
                            using (MySqlTransaction transaction = db.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))

should generate SQL without SESSION:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
...

In doc:
https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
"Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level."

Hello ZOKIAD,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.

Posted by developer:
 
The Transaction Characteristic Scope was fixed as SESSION, this was causing after execute some transactions, the isolation level could be different than the initial state. The "SESSION" keyword is now removed when the isolation level is set. 

as per documentation: (https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html)

"Without any SESSION or GLOBAL keyword:
The statement applies only to the next single transaction performed within the session.
Subsequent transactions revert to using the session value of the named characteristics."

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

The isolation level set for a transaction did not revert to using the
session value after the transaction finished.

Thank you for the bug report.