TransactionScope with MySQL and Distributed Transactions
<p>I have an ASP.Net WebAPI instance setup that uses a MySQL database for storage.  I have written an <code>ActionFilter</code> that handles creating a <code>TransactionScope</code> for the lifetime of a single endpoint request.</p>

<pre><code>public async Task&lt;HttpResponseMessage&gt; ExecuteActionFilterAsync(
    HttpActionContext actionContext,
    CancellationToken cancellationToken,
    Func&lt;Task&lt;HttpResponseMessage&gt;&gt; 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;
    }
}
</code></pre>

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

<pre><code>public async Task&lt;IHttpActionResult&gt; CreateStuffAsync()
{
    var query = this.queryService.RetrieveAsync();

    // logic to do stuff

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

    return this.Ok();
}
</code></pre>

<p>I don't create a single <code>DbConnection</code> 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 <code>queryService</code> and <code>updateService</code> open/close <code>DbConnection</code>'s via <code>using</code> statements:</p>

<pre><code>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;
}
</code></pre>

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

<p>Intermittently I am seeing an exception thrown when attempting to open the connection:</p>

<pre><code>"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()"
</code></pre>

<p>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 <code>TransactionScope</code> and <code>DbConnection</code> instances?</p>