Transactionscope throwing exception this platform does not support distributed transactions while opening connection object

Question

TransactionScope is throwing a exception in .net core 2.2

In this example I created a scope of TransactioScop. Opening SQL transaction for one database which is working fine.
After the first transaction I´m calling commit which will commit the SQL transaction. I try to open call transaction for another database while creating a transaction and the system is throwing an exception

This platform does not support distributed transactions.

tried to remove SQL transaction

c#

using (TransactionScope scop =new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled))
{       
    _db1UOW.Begin(); //creating sql transaction
    await _db1UOW.IDenialDetailsRepositorydb1.InsertDenialDetails(denialsDetails);
    await _db1UOW.IRuleDetailsRepositorydb1.InsertRulesDetails(rulesDetails);
    _db1UOW.Commit(); //commitng sql transaction

    _db2UOW.Begin(); //creating sql transaction (but while opening connection object its throwing exception as This platform does not support distributed transactions)
    await _db2UOW.IRuleDetailsRepository.GetRulesDetails();
    await _db2UOW.IDenialDetailsRepository.InsertDenialDetails(denialsDetails);
    var data = await _db2UOW.IRuleDetailsRepository.InsertRulesDetails(rulesDetails);
    _db2UOW.Commit(); //commitng sql transaction
    scop.Complete();
}

Message

"This platform does not support distributed transactions."  at System.Transactions.Distributed.DistributedTransactionManager.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   at System.Transactions.TransactionInterop.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToDistributedTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()

Insectarium

Don't use both a transaction scope and Begin/CommitTransaction unless you really mean to create distributed transactions. The TransactionScope is used so you don't have to use explicit Begin/Commit calls – Airhead

TransactionScope only works on Windows. Not sure what OS you are using. – Shawm

I am using windows 7 with visual studio 2017 vs 15.9 I have tried removing sql transaction – Insectarium

Once we had an ExecuteReader with a DELETE SQL statement in a Transaction[Scope]. It will throw PlatformNotSupportedException: "This platform does not support distributed transactions". – Amitie

I also hit this error for a single database because I was opening multiple connections – Eyebrow

Answer 1

.NET Core doesn't support Distributed Transactions because it would require a different transaction manager on each platform. It may appear in the future (here's the issue in-progress), but for now any Transaction that would require two different resource managers will throw this exception.

Instead you can coordinate separate transactions. Have two separate transactions complete their work, and then commit them both. There is a possibility that the first commit succeeds and the second one fails, but for SQL Server (with one exception), that would be a very rare occurance. Something like:

_db1UOW.Begin(); //creating sql transaction
await _db1UOW.IDenialDetailsRepositorydb1.InsertDenialDetails(denialsDetails);
await _db1UOW.IRuleDetailsRepositorydb1.InsertRulesDetails(rulesDetails);

_db2UOW.Begin(); //creating sql transaction 
await _db2UOW.IRuleDetailsRepository.GetRulesDetails();
await _db2UOW.IDenialDetailsRepository.InsertDenialDetails(denialsDetails);
var data = await _db2UOW.IRuleDetailsRepository.InsertRulesDetails(rulesDetails);
 
_db1UOW.Commit(); //commitng sql transaction
try
{
    _db2UOW.Commit(); //commitng sql transaction
}
catch (Exception ex)
{
     LogError("Second transaction failed to commit after first one committed.  Administrators may need to fix stuff");
     throw;
}

Or if the two databases are on the same server you can use cross-database queries with a single SqlConnection to enlist the changes in a single SQL Server transaction.

Heptastich

The open GitHub issue is github.com/dotnet/corefx/issues/13532. – Destrier

The statement " There is a possibility that the first one succeeds and the second one fails, but for SQL Server, that would be a very rare occurance. " - is untrue. A transaction could fail for many reasons, which is precisely why distributed transactions exist! Not that distributed transactions are a good idea if you want throughout... – Zwart

It’s only the commit that is happening at that point. Both transactions are otherwise complete. So most potential transaction failures would already have happened. Updated answer to clarify. – Heptastich

Any update? I tried solution from microsoft, unfortunately it doesn't work. I'm not sure if this may be caused by 2014 SQL Server. Or System.Data.SqlClient (as Microsoft.Data.SqlClient doesn't connect on company server) – Gensler

Ok, nevermind i just noticed it's a solution for Framework not Core. I see that some sort of distributed transaction may come in 7.0 release (windows only) – Gensler

I want to use hangfire in meidatr command with transactionscope pipelinebehavior, but I get this error : "this platform does not support distributed transactions" – Curling

The linked GitHub issue is closed; this is now implemented for .NET 7 on Windows only. – Coexecutor

Answer 2

You are doing a distributed transaction across multiple databases. I used to encounter this problem, and have already written a distributed transaction framework which also support dotnet.

Transaction coordinator: https://github.com/dtm-labs/dtm

Dotnet SDK: https://github.com/dtm-labs/dtmcli-csharp

A blog about how to design a distributed transaction: https://www.c-sharpcorner.com/article/distributed-transaction-in-c-sharp-microservices-using-saga-pattern/

Arrington

Do you think .net 7 supports Distributed transactions? – Curling

Answer 3

I managed to resolve by suppressing if there is existing transaction and still setting TransactionScopeAsyncFlowOption.Enabled and it works perfectly

public class TransactionMiddleware : IMiddleware
{
    public async Task InvokeAsync(HttpContext context, RequestDelegate next)
    {
        // Check if there is an existing TransactionScope
        bool hasExistingTransaction = Transaction.Current != null;

        // If there is no existing transaction, create a new TransactionScope
        using (var scope = hasExistingTransaction
            ? new TransactionScope(TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled)
            : new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            try
            {
                await next(context);
                // If there was no existing transaction, complete the new TransactionScope
                if (!hasExistingTransaction)
                {
                    scope.Complete();
                }
            }
            finally
            {
                // Dispose of the TransactionScope (automatically calls Complete if no exception occurred)
                scope.Dispose();
            }
        }
    }
}

Invercargill