TransactionScope.IsolationLevel is not respected

[Timovzl]

The connector shows some deadlock behavior that does not occur when using the database directly or when using Connector/NET.

With UseXaTransactions=False, and the following simple table:

CREATE TABLE `orders`(  
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(50),
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Let's run a program that does the following, from two threads simultaneously:

1. Open a transaction scope.
2. SELECT MAX(id) FROM orders FOR UPDATE.
3. INSERT INTO orders.
4. Complete the transaction scope.

What should happen (and does with Connector/NET or directly on the database):

One thread should get the lock, while the other gets temporarily stuck on step 2. The winning thread finishes the rest of its work. The losing thread then gets the lock and can finish its work.

What currently happens differently with MySqlConnector:

As soon as the winning thread begins its INSERT, without even committing, the losing thread gets a deadlock exception:

MySqlException: Deadlock found when trying to get lock; try restarting transaction

Here is a simple program we can use to reproduce this on the table mentioned above:

internal class Program
{
	public static void Main(string[] args)
	{
		Task.Run(() => Perform(1));
		Perform(2);

		Console.ReadKey();
	}

	private static void Perform(int i)
	{
		// This TransactionScope may be overly configured, but let's stick with the one I am actually using
		using (var transactionScope = new System.Transactions.TransactionScope(
			TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(10) }, TransactionScopeAsyncFlowOption.Enabled))
		{

			using (var connection = new MySqlConnection("Server=localhost;Uid=user;Pwd=pwd;TreatTinyAsBoolean=False;UseAffectedRows=True;SslMode=None;UseXaTransactions=False;"))
			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"SELECT MAX(id) FROM orders FOR UPDATE;";
				connection.Open();
				Console.WriteLine(i + " locking");
				command.ExecuteScalar();
			}

			using (var connection = new MySqlConnection("Server=localhost;Uid=user;Pwd=pwd;TreatTinyAsBoolean=False;UseAffectedRows=True;SslMode=None;UseXaTransactions=False;"))
			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"INSERT INTO orders (description) VALUES ('blabla'), ('blablabla');";
				connection.Open();
				Console.WriteLine(i + " inserting");
				command.ExecuteNonQuery();
			}

			transactionScope.Complete();
		}
	}
}

[bgrainger]

Thanks for the thorough bug report and repro!

When executing this code, the MySQL general log contains the following:

2019-01-24T05:08:05.421870Z	    3 Query	set transaction isolation level repeatable read;
2019-01-24T05:08:05.421982Z	    3 Query	start transaction
2019-01-24T05:08:05.422014Z	    2 Query	set transaction isolation level repeatable read;
2019-01-24T05:08:05.422076Z	    2 Query	start transaction

It clearly should be set transaction isolation level read committed; based on your example code new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }.

The System.Transactions.IsolationLevel value needs to be passed through to the non-XA transaction that is started.

[bgrainger]

Fixed in 0.49.3.

[Timovzl]

I can confirm that this solves both the repro and the original scenario where I encountered the problem. Cheers!