TransactionAbortedException when performing queries in parallel inside a transaction scope #1675
Description
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
Metadata
Metadata
Assignees
Labels
No labels
Type
Projects
Status