CODEWITHSUNDEEP
c#mysqltransactionsmysql-connectortransactionscope
tris
tris

Reputation: 1830

TransactionScope with MySQL and Distributed Transactions

I have an ASP.Net WebAPI instance setup that uses a MySQL database for storage. I have written an ActionFilter that handles creating a TransactionScope for the lifetime of a single endpoint request.

public async Task<HttpResponseMessage> ExecuteActionFilterAsync(
    HttpActionContext actionContext,
    CancellationToken cancellationToken,
    Func<Task<HttpResponseMessage>> continuation)
{
    var transactionScopeOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted };
    using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, transactionScopeOptions, TransactionScopeAsyncFlowOption.Enabled))
    {
        var handledTask = await continuation();

        transaction.Complete();

        return handledTask;
    }
}

Then throughout the endpoints I have different queries/commands that open/close connections using the autoenlist=true functionality of DbConnection's. An example endpoint may be:

public async Task<IHttpActionResult> CreateStuffAsync()
{
    var query = this.queryService.RetrieveAsync();

    // logic to do stuff

    var update = this.updateService.Update(query);

    return this.Ok();
}

I don't create a single DbConnection and pass it around from the top, as this is a simplistic example, when in practise passing the connection between services would require a large refactor (although if necessary, this can be done). I also read that it is better to open/close the connections as necessary (i.e. keep them open for as little time as possible). The queryService and updateService open/close DbConnection's via using statements:

var factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
using (var connection = factory.CreateConnection())
{
    connection.ConnectionString = "Data Source=localhost;Initial Catalog=MyDatabase;User ID=user;Password=password;Connect Timeout=300;AutoEnlist=true;";

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    var result = await connection.QueryAsync(Sql).ConfigureAwait(false);

    return result;
}

The same DbConnection is generally not used for multiple queries within the same API endpoint request -- but the same connection string is.

Intermittently I am seeing an exception thrown when attempting to open the connection:

"ExceptionType": "System.NotSupportedException",
"ExceptionMessage": "System.NotSupportedException: MySQL Connector/Net does not currently support distributed transactions.\r\n   at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)\r\n   at MySql.Data.MySqlClient.MySqlConnection.EnlistTransaction(Transaction transaction)\r\n   at MySql.Data.MySqlClient.MySqlConnection.Open()"

I do not understand why it is attempting to escalate the transaction to a distributed transaction, when all of the connections are against the same database. Or am I misunderstanding/misusing the TransactionScope and DbConnection instances?

Upvotes: 1

Views: 3432

Answers (1)

Bradley Grainger
Bradley Grainger

Reputation: 28162

The System.Transactions.Transaction object makes the determination of whether to escalate to a distributed transaction based on how many separate "resource managers" (e.g., a database) have enlisted in the transaction.

It does not draw a distinction between connections to different physical databases (that do require a distributed transaction) and multiple MySqlConnection connections that have the same connection string and connect to the same database (which might not). (It would be very difficult for it to determine that two separate "resource managers" ① represent the same physical DB and ② are being used sequentially, not in parallel.) As a result, when multiple MySqlConnection objects enlist in a transaction, it will always escalate to a distributed transaction.

When this happens, you run into MySQL bug #70587 that distributed transactions aren't supported in Connector/NET.

Workarounds would be:

  1. Make sure only one MySqlConnection object is opened within any TransactionScope.
  2. Change to a separate connector that does support distributed transactions. You could use MySqlConnector (NuGet, GitHub) as a drop-in replacement for Connector/NET. I've heard that dotConnect for MySQL supports them also (but haven't tried that one).

Upvotes: 5

Related Questions