Bug #86263 Wrong Isolation Level for Begin Transaction
Submitted: 10 May 2017 8:10 Modified: 10 May 2017 10:06
Reporter: ZOKIAD ZOKIAD Email Updates:
Status: Verified 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

[10 May 2017 8:10] ZOKIAD ZOKIAD
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."
[10 May 2017 10:06] Chiranjeevi Battula
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.