TransactionScope() and parallel query execution

Question

We're trying to run parallel queries inside a transaction scope to improve performance of our code. We have several changes to be made in the database that have no connection to each other. We could run the code like this:

using(var tran = new System.Transactions.TransactionScope())
{
    await queryMethod1Async();
    await queryMethod2Async();
    await queryMethod3Async();

    tran.Complete();
}

however, since the methods are independent of each other, we would like to run the code like this:

using(var tran = new System.Transactions.TransactionScope())
{
    var tasks = new List();

    tasks.Add(queryMethod1Async());
    tasks.Add(queryMethod2Async());
    tasks.Add(queryMethod3Async());

    await Task.WhenAll(tasks);

    tran.Complete();
}

We are running into some issues with the parallel execution:

Running the queries in parallel seems to escalate the transaction. During escalation, sometimes an error occurs:

The wait operation timed out  --> There is already an open DataReader associated with this Command which must be closed first.    
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)     
at System.Data.SqlClient.SqlDelegatedTransaction.Promote() --> Failure while attempting to promote transaction.    
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()     
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)     
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx) --> The transaction has aborted.    
at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)     
at System.Transactions.Transaction.Promote()     
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(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.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)

After some investigation, this seems to be because during escalation, the original connection for the transaction is used, but during parallel execution, this connection might be in use. I've tried to enable MARS to avoid this issue, but this results in a different error:

Current Microsoft Distributed Transaction Coordinator (MS DTC) transaction must be committed by remote client.    
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)     
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)     
at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment) --> The transaction has aborted.

To clarify, each of the above methods create a new sqlconnection, and we have configured MSDTC correctly

I'm not sure why this second error occurs, but I have a feeling I'm going about this the wrong way. Is it possible to do parallel query execution inside a transaction scope, and if so, what is the right way to go about this?

user_7297700 · Answer

Is it possible to do parallel query execution inside a transaction scope, and if so, what is the right way to go about this?

Yes. You must you a separate SqlConnection for each query, and have MSDTC properly configured and running. But it's rarely useful to do this, as each command can be run in parallel on SQL Server, so running multiple commands at the same time often doesn't decrease the overall runtime.

user_17408195 · Answer

I think it is better that you create procedure in your database that run all queries in it. then you can get it in one request with dataset. now use each datatable in dataset at its place.

user_15883952 · Answer

You can try few options:

With MARS enabled, create your transaction scope with TransactionScopeAsyncFlowOption.Enabled parameter:

using(var tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)
{...

I used System.Data.SqlClient.SqlConnection with dapper to read / write data and it worked.

If you are using Entity Framework Core, you can use SemaphoreSlim to synchronize access to your DbContext to avoid this problem. Example:

// declare semaphore
var syncSemaphore = new SemaphoreSlim(1);
// [...]
// and use it in all your async functions like this:
async Task queryMethod1Async()
{
    await syncSemaphore.WaitAsync();
    try
    {
       // Use your database connection - read or write and release semaphore so other tasks can access
    }
    finally
    {
        syncSemaphore.Release();
    }            
}

user_1838837 · Answer

Is any of your queryMethodXAsync spawning it's own Transaction (as neasted transaction) and does not rollback or commit it before you try to complete the TransactionScope

Things like that could happen if you call stored procedure that uses transactions inside of it and does not dispose in the correct way of it.

Do you have an InnerExcpetion in your exception with more details?

TransactionScope() and parallel query execution

4 Answers