How to avoid PlatformNotSupportedException when using TransactionScope and SQL connection pooling in .NET Core/.NET 5+

Question

In a web service, I am querying a SQL Server 2016 database. Using a .NET TransactionScope as follows to keep transaction management in my service layer but data queries/commands in my data layer ("store" classes) code, we have a few places that follow this pattern:

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    bool needsInsert = await store1.Exists(request.id);
    if (needsInsert) mainRowsUpdatedCount = await store2.Insert(request);
    transaction.Complete();
}

Each of those "store" methods follow this pattern (using Dapper, though I suspect it doesn't matter):

const string query = @"SELECT ...";                        // or INSERT or MERGE as the case may be
using IDbConnection connection = new SqlConnection(ConnectionString.Value);
return await connection.QueryAsync<T>(query, new { ... }); // or connection.ExecuteAsync as the case may be

This works great on most calls, but sometimes I get the following (though quite rarely):

System.PlatformNotSupportedException: This platform does not support distributed transactions.

So could it be that, in the above example, store1.Exists runs, gets a connection, enlists it in the transaction, runs its query, closes, then sometimes before store2.Insert can run, some other unrelated thread gets the same connection from the connection pool that already has an open transaction, attempts to run a query and thus throws a PlatformNotSupportedException, since .NET Core (or .NET 5+) doesn't support distributed transactions?

If so, how can I overcome this without passing my connections around?

If not, what else could be causing this exception?

Answer 1

I encountered the same issue (also pretty rarely), and eventually concluded that it most likely is a bug in .NET Cores base libs (either SqlClient or Transaction).

To my understanding, there is a difference between a .NET transaction and a local SQL transaction. When you create a TransactionScope, you effectively start a new .NET transaction that is always accessible through Transaction.Current. Calling .Open() on a newly created SqlConnection internally detects this ambient .NET transaction and queries the internal connection pool–where the actual SQL connections are stored–for any suitable existing connection (that is, with the same connection string) that is already associated with that ambient .NET transaction. If one is available (i.e. free/idle), it directly uses that one without escalating to a distributed transaction.

So if all your code inside a TransactionScope ensures to always have at most one SqlConnection open at any given time, it should be guaranteed that the same actual SQL connection and thus local SQL transaction is re-used under the hood, and no attempt to escalate to a distributed transaction should be made.

In your sample, you use TransactionScopeAsyncFlowOption.Enabled to ensure that the ambient transaction is correctly maintained through async code flow (i.e. it is allowed to use await inside of the scope). As long as you don't break out of this flow everything should be fine.

Even if there is another TransactionScope (and thus another .NET transaction) created somewhere in the application, with TransactionScopeAsyncFlowOption.Suppress, outside of your scope, that is not properly disposed, and its associated thread happens to be the one which continues your async code flow inside your scope, then there shouldn't be any conflation, as either your next SqlConnection tries to be part of the leaked .NET transaction, or your .NET transaction. I wouldn't expect an escalation either way. Probably this circumstance throws a different type of exception earlier on (not tested).

All in all, I still believe this is some bug buried deep down in the .NET transaction spaghetti code. ;) My problem is that I only encounter the exception in rare cases in production, I just cannot reproduce it using a unit test or something.