4

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?

LarsTech
  • 80,625
  • 14
  • 153
  • 225
Patrick Szalapski
  • 8,738
  • 11
  • 67
  • 129
  • An attempt to escalate to Distributed Transactions, perhaps? I’d expect the same without MSDTC where such “is supported”. – user2864740 Mar 17 '21 at 20:29
  • What does "escalate to distributed transactions" mean? – Patrick Szalapski Mar 17 '21 at 21:38
  • 1
    I wonder if perhaps there is *some* TS code that is not using `TransactionScopeAsyncFlowOption.Enabled`? The error might "go away" if using `RequiresNew`, religiously, which will avoid enlisting into an existing transaction. – user2864740 Mar 17 '21 at 22:12
  • RequiresNew might be exactly what I need. Thanks, I'll try it. – Patrick Szalapski Mar 18 '21 at 14:07
  • 1
    Any progress on this one? We run into the same issue, also pretty rarely, but still. „RequiresNew“ is not an option, since we want to join any outer transaction going on, if any. But did „RequiresNew“ solve your problem? – domin Aug 27 '21 at 18:47
  • @user2864740: I don‘t think another TransactionScope without `TransactionScopeAsyncFlowOption.Enabled` could be the problem, since that would try to enlist the connection in *another* transaction altogether, wouldn’t it? – domin Aug 28 '21 at 09:20
  • Transactions are per-connection, not per scope. So if you reused a connection that already has a transaction scope open, you'd automatically be enlisted in that transaction, even though it is a different scope. – Patrick Szalapski Aug 28 '21 at 22:03
  • Did „RequiresNew“ solve your problem? What is your current explanation why the exception happened? – domin Aug 29 '21 at 02:11
  • Another question: Did you run this from within an `IHostedService`? Becaue we encounter the issue only when run there. – domin Sep 10 '21 at 08:09

1 Answers1

2

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.

domin
  • 1,192
  • 1
  • 7
  • 28