Description:
Testing some heavy concurrency, I have accidentally reproduced an issue that has been haunting us in production for some time.
When many simultaneous transactions are open against the same database, SOMETIMES when such a transaction scope is being disposed (after the call to Complete()), the following MySqlException appears:
MySql.Data.MySqlClient.MySqlException (0x80004005): There is already an open DataReader associated with this Connection which must be closed first.
at MySql.Data.MySqlClient.Interceptors.ExceptionInterceptor.Throw(Exception exception)
at MySql.Data.MySqlClient.MySqlCommand.Throw(Exception ex)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlTransaction.Commit()
at MySql.Data.MySqlClient.MySqlTransactionScope.SinglePhaseCommit(SinglePhaseEnlistment singlePhaseEnlistment)
at System.Transactions.DurableEnlistmentCommitting.EnterState(InternalEnlistment enlistment)
at System.Transactions.DurableEnlistmentActive.ChangeStateCommitting(InternalEnlistment enlistment)
at System.Transactions.TransactionStateSPC.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateVolatilePhase1.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStatePhase0.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateActive.BeginCommit(InternalTransaction tx, Boolean asyncCommit, AsyncCallback asyncCallback, Object asyncState)
at System.Transactions.CommittableTransaction.Commit()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()
at MySqlExceptionCore.Program.PerformQuery(String connectionString, String tableName) in Program.cs:line 45
Reproducable in MySql.Data versions 6.10.8 and 8.0.12.
Reproducable on .NET Core 2.1 and .NET Framework 4.7.1.
The repro only reproduces the issue with async (Task-based) code. Changing the repro to synchronous code with Parallel.For(), the issue disappears. (See How to repeat.) I'm not sure if this is a flaw of the repro or a property of the issue.
In the more complex setup where I initially reproduced the issue by accident, I got a NullReferenceException instead of the one described, but at the same place. Reducing the code to a simple repro, the exception changed into the one described. Possibly, the NullReferenceException was a consequence. If not, I may be able to reproduce it once the described issue is fixed.
This may or may not be related to bug #90280 (https://bugs.mysql.com/bug.php?id=90280).
How to repeat:
Run this repro program for a few seconds:
using System;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace MySqlExceptionCore
{
class Program
{
private static void Main(string[] args)
{
PerformQueriesAtIntervals(TimeSpan.FromMilliseconds(50), connectionString: "Server=10.0.0.1;Database=Northwind;Uid=User;Pwd=Password;SslMode=None;", tableName: "Orders")
.GetAwaiter().GetResult();
}
private static async Task PerformQueriesAtIntervals(TimeSpan interval, string connectionString, string tableName)
{
while (true)
{
Task.Run(() => PerformQuery(connectionString, tableName));
await Task.Delay(interval);
}
// Alternative approach with same result:
//Parallel.For(0, 1000000, async i => await PerformQuery(connectionString, tableName));
}
private static async Task PerformQuery(string connectionString, string tableName)
{
try
{
using (var transactionScope = new System.Transactions.TransactionScope(
System.Transactions.TransactionScopeOption.Required,
new System.Transactions.TransactionOptions() {IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(10)},
System.Transactions.TransactionScopeAsyncFlowOption.Enabled))
{
await SelectStarLimit1(connectionString, tableName);
// Because the query is so quick, the exception is unlikely
// To simulate, introduce a delay
// The greater the delay, the more quickly we can get the exception
var delay = TimeSpan.FromSeconds(10);
await Task.Delay(delay);
transactionScope.Complete();
}
}
catch (Exception e)
{
Console.WriteLine(e); // Will print MySqlException: "There is already an open DataReader associated..."
}
}
private static async Task SelectStarLimit1(string connectionString, string tableName)
{
using (var connection = new MySqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = $"SELECT * FROM {tableName} LIMIT 1";
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
}
}
}
}
Suggested fix:
Presumably a connection is returned to the pool in an unsuitable state, which should be prevented.
