TransactionAbortedException when performing queries in parallel inside a transaction scope

[joostmeijles]

Describe the bug

When performing multiple queries in parallel, and each pair of 2 queries are inside a transaction scope, an unexpected transaction error is thrown (see below).

Exception message: System.Transactions.TransactionAbortedException: The transaction has aborted.
Stack trace:
Unhandled exception. System.Transactions.TransactionAbortedException: The transaction has aborted.
 ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction.
 ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.SqlDelegatedTransaction.Promote()
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SqlDelegatedTransaction.Promote()
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(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 Microsoft.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at Microsoft.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at Microsoft.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInterna
l& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.InternalOpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---

To reproduce

Below code reproduces the error. It runs 100K times 2 queries in parallel where each 2 queries are inside a single transaction scope.
Note that after each query the database connection is disposed (and thus closed). This is important to trigger the error.
When we change the code, and use 1 connection for both queries and only open 1 connection the error does not occur (see also code comments below).

using System.Transactions;
using Microsoft.Data.SqlClient;

static async Task PerformTransactionWithQuery(int num)
{
    try
    {
        using (new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            // Adding "max pool size=1000;" to the connection string seems to trigger the problem less often
            string connStr = @"Server=.\SQLEXPRESS;Database=master;Trusted_Connection=True;Encrypt=False";

            var query = "SELECT COUNT(*) FROM sys.dm_tran_active_transactions";

            await using (var dbConn = new SqlConnection(connStr))
            {
                await dbConn.OpenAsync();

                await using (var command1 = new SqlCommand(query, dbConn))
                {
                    await command1.ExecuteScalarAsync();
                }
            } // Connection is disposed (and thus closed)

            await using (var dbConn = new SqlConnection(connStr)) 
            {
                // Reopening the connection triggers the following error:
                // System.Transactions.TransactionAbortedException: The transaction has aborted.
                //
                // NB. Using a single connection and opening it once does NOT trigger the error
                await dbConn.OpenAsync();
                
                await using (var command2 = new SqlCommand(query, dbConn))
                {
                    await command2.ExecuteScalarAsync();
                }
            } // Connection is disposed (and thus closed)
            
            //Do not complete transaction
        }
    }
    catch (Exception e)
    {
        Console.WriteLine($"Failed {num}");
        throw;
    }
} 

var tasks = Enumerable.Range(0, 100000).ToList().Select(PerformTransactionWithQuery);
await Task.WhenAll(tasks);

Expected behavior

Being able to use multiple connections (with the same connection string) in sequence within the same transaction without errors.

Further technical details

Microsoft.Data.SqlClient version: 4.1.0
.NET target: .NET 6
SQL Server version: SQL Server 2019
Operating system: Windows 11

[DavoudEshtehari]

@joostmeijles Thank you for the repro. I'll back to you if I had any questions to reproduce the issue.

[joostmeijles]

@DavoudEshtehari any update on an ETA to verify that this is a bug and get it fixed?

[DavoudEshtehari]

@joostmeijles I confirm it's reproducible with MDS and we'll return to it after the next release. I'm afraid we can't provide any estimation for a fix while it's in the investigation stage. We'll keep updating here with any findings, and we welcome any contribution as this is an open-source driver.

[JRahnama]

@joostmeijles can you test with a reduced MaxPoolSize to 20? I wonder if this is something similar to #422?

[joostmeijles]

@joostmeijles can you test with a reduced MaxPoolSize to 20? I wonder if this is something similar to #422?

Changing the pool size to 20 does not change much, but setting it very large (e.g. 1000) seems to trigger the problem less often.

[JRahnama]

@joostmeijles one more question, were you able to see the same problem on Windows 10?

[JRahnama]

@joostmeijles regarding the comment in the repro

// NB. Using a single connection and opening it once does NOT trigger the error

does this mean if you open a connection first and then try the rest of the code is working fine or just a single connection?

[joostmeijles]

@joostmeijles regarding the comment in the repro

// NB. Using a single connection and opening it once does NOT trigger the error

does this mean if you open a connection first and then try the rest of the code is working fine or just a single connection?

It means changing the code to:

await using (var dbConn = new SqlConnection(connStr))
{
    await dbConn.OpenAsync();

    await using (var command1 = new SqlCommand(query, dbConn))
    {
        await command1.ExecuteScalarAsync();
    }

    await using (var command2 = new SqlCommand(query, dbConn))
    {
        await command2.ExecuteScalarAsync();
    }
} // Connection is disposed (and thus closed)

does not trigger the error.

[joostmeijles]

@joostmeijles one more question, were you able to see the same problem on Windows 10?

Yes, same.

[JRahnama]

@joostmeijles couple of things I noticed:

1. If we set the pooling to false the issue does not show up (this confirms the issue only occurs with pooling)
2. if you set SqlConnection.ClearAllPools(); before the second using of dbConn your code should be working fine as well.
3. If we start with one connection first and then try to open the rest the connections the issue does not com up.
   The above makes me suspicious that we need to adjust the code to do pool warming.
   Too many parallel connections are trying to create the very same pool. If we let one connection to get opened a pool is stablished and the rest will use that.

[David-Engel]

Edit: Timeout isn't the issue here.

It sounds to me like your transaction is timing out. With 100,000 parallel operations and a limited connection pool and thread pool, there will likely be a significant delay between the first query completing, the connection closing, and the second connection being obtained from the pool during the transaction. Idle transactions aren't kept around forever. The default transaction timeout is only one minute.

Useful blog post: https://docs.microsoft.com/en-za/archive/blogs/dbrowne/using-new-transactionscope-considered-harmful

[joostmeijles]

It sounds to me like your transaction is timing out. With 100,000 parallel operations and a limited connection pool and thread pool, there will likely be a significant delay between the first query completing, the connection closing, and the second connection being obtained from the pool during the transaction. Idle transactions aren't kept around forever. The default transaction timeout is only one minute.

Useful blog post: https://docs.microsoft.com/en-za/archive/blogs/dbrowne/using-new-transactionscope-considered-harmful

An error possibly occur within seconds, so doesn't seem to hit a timeout. Also, the 100K parallel operations are just to make it easier to reproduce. I have seen the issue also with less (~100) parallel operations.

Creating the transaction scope as follows (as mentioned in the blog post + async option) results in the same error.

static TransactionScope CreateTransactionScope()
{
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled);
}

[joostmeijles]

@joostmeijles couple of things I noticed:

1. If we set the pooling to false the issue does not show up (this confirms the issue only occurs with pooling)
2. if you set SqlConnection.ClearAllPools(); before the second using of dbConn your code should be working fine as well.
3. If we start with one connection first and then try to open the rest the connections the issue does not com up.
   The above makes me suspicious that we need to adjust the code to do pool warming.
   Too many parallel connections are trying to create the very same pool. If we let one connection to get opened a pool is stablished and the rest will use that.

Without connection pooling (on the connection string or by clearing the pool before the second connection) System.PlatformNotSupportedException: This platform does not support distributed transactions. will be thrown immediately.