Send retained transaction descriptor in MARS TDS header for .NET Core and .NET 5+

[swh-cb]

Describe the bug

The retained transaction descriptor (TdsParser._retainedTransactionId) is not send in the MARS TDS header for .NET Core and .NET 5+ applications when a distributed MSDTC transaction is explicitly rolled back without defecting. The retained transaction descriptor must be sent to the server on subsequent executions even though the transaction is considered to be rolled back. The .NET Framework driver already sends the retained transaction descriptor in this case (see here).

The missing retained transaction descriptor in the TDS header can lead to a corrupted connection pool in .NET Core and .NET 5+ when the MSSQL transaction is explicitly aborted (e.g. ROLLBACK TRANSACTION) in a distributed transaction scope and the application reuses the same connection from the general connection pool. In this case Microsoft.Data.SqlClient always throws the exception Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction when unenlisting/defecting the distributed transaction from the pooled connection (SqlInternalConnection.EnlistNull). Despite the error, the "broken" connection is put back into the pool and reused when the application tries to establish another database connection. The application must be restarted or the pool must be cleared (SqlConnection.ClearPool) to successfully reestablish connections to the database.

At the moment .NET Core and .NET 5+ do not support distributed transactions (dotnet/runtime#715) and this error therefore has no major impact yet. However official support for distributed transactions has been announced for .NET 7.0. We found this error in one of our production system since we use use a library (see here) as a temporary workaround to enable distributed MSDTC transactions for MSSQL servers in .NET Core and .NET 5+ (Disclaimer: I am the author of this library).

The application throws the following exception when the driver fails to unenlist/defect the transaction from the pooled connection:

Exception message: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction
Stack trace:
Microsoft.Data.SqlClient.SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.TdsParser.PropagateDistributedTransaction(Byte[] buffer, Int32 timeout, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
   at Microsoft.Data.SqlClient.SqlInternalConnection.EnlistNull()
   at Microsoft.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   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, DbConnectionInternal& 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.ProviderBase.DbConnectionClosed.TryOpenConnection(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)

To reproduce

This code reproduces the error:

// Patch the OleTx implementation in System.Transactions to support distributed transactions in .NET Core and .NET 5+ applications
Softwarehelden.Transactions.Oletx.OletxPatcher.Patch();

// Connection pooling must be enabled
string connectionString1 = "Server=mssql-server1;Database=Db1;Integrated Security=true;Connection Timeout=30;Pooling=True";
string connectionString2 = "Server=mssql-server2;Database=Db2;Integrated Security=true;Connection Timeout=30;Pooling=True";

try
{
    using (var transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        using (var sqlConnection = new SqlConnection(connectionString1))
        {
            await sqlConnection.OpenAsync();
        }

        using (var sqlConnection = new SqlConnection(connectionString2))
        {
            // Promote to a distributed MSDTC transaction
            await sqlConnection.OpenAsync();

            using (var command = sqlConnection.CreateCommand())
            {
                // Abort the transaction explicitly
                command.CommandText = "ROLLBACK TRANSACTION";

                await command.ExecuteNonQueryAsync();
            }
        }

        // transactionScope.Complete();
    }
}
catch (TransactionException)
{
    // The distributed transaction was aborted successfully
}

// Opening new connections from the connection pool now always fails
for (int i = 0; i < 5; i++)
{
    using (var sqlConnection = new SqlConnection(connectionString2))
    {
        // Throws SqlException "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." (Error Number = 8525)
        // Further information: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8525-database-engine-error
        await sqlConnection.OpenAsync();
    }
}

// Clear the connection pool or restart the application to workaround the error
SqlConnection.ClearAllPools();

This error is not reproducible when the same code runs on .NET Framework (without patching System.Transactions).

Expected behavior

The .NET Core driver sends the retained transaction descriptor in the MARS TDS header for TransactionManagerRequest requests when a distributed transaction is rolled back and unenlists/defects the distributed transaction successfully from the pooled connection on subsequent connection attempts. Currently the driver always sends 0 (SqlInternalTransaction.NullTransactionId) if there is no current transaction. See here.

Further technical details

Microsoft.Data.SqlClient version: 4.1.0
.NET target: .NET 6.0
SQL Server version: SQL Server 2019
Operating system: Windows 10 21H2

Additional context

Event Trace Log for Microsoft.Data.SqlClient.EventSource:

SqlConnection.InternalOpenAsync | API | Object Id 4
SqlConnection.InternalOpenAsync | API | Correlation | Object Id 4, Activity Id 54e3155d-a421-41a5-a5ba-b2099aa4cb63:1
<prov.DbConnectionPool.GetConnection|RES|CPOOL> 2, Getting connection.
<prov.DbConnectionPool.GetFromGeneralPool|RES|CPOOL> 2, Connection 7, Popped from general pool.
<prov.DbConnectionInternal.PostPop|RES|CPOOL> 7, Preparing to pop from pool,  owning connection 0, pooledCount=0
<prov.DbConnectionInternal.ActivateConnection|RES|INFO|CPOOL> 7, Activating
SqlInternalConnection.EnlistNull | ADV | Object Id 7, unenlisting.
TdsParserStateObject.TryProcessHeader | ADV | State Object Id 2, Client Connection Id 723ecd21-601b-45ae-8947-71bfe3e306b2, Server process Id (SPID) 60
SqlError.ctor | ERR | Info Number 8525, Error State 3, Error Class 16, Error Message 'Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.', Procedure '', Line Number 1
TdsParserStateObject.DecrementOpenResultCount | INFO | State Object Id 2, Processing Attention.
<sc.TdsParser.FailureCleanup|ERR> Exception caught on ExecuteXXX: 'Microsoft.Data.SqlClient.SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
ClientConnectionId:723ecd21-601b-45ae-8947-71bfe3e306b2
Error Number:8525,State:3,Class:16'
<prov.DbConnectionInternal.PrePush|RES|CPOOL> 7, Preparing to push into pool, owning connection 0, pooledCount=0
<prov.DbConnectionPool.DeactivateObject|RES|CPOOL> 2, Connection 7, Deactivating.
<prov.DbConnectionInternal.DeactivateConnection|RES|INFO|CPOOL> 7, Deactivating
SqlInternalConnection.Deactivate | ADV | Object Id 7 deactivating, Client Connection Id null
<sc.TdsParser.Deactivate|ADV> 2 deactivating
<sc.TdsParser.Deactivate|STATE> 2
           _physicalStateObj = 2
           _pMarsPhysicalConObj = (null)
           _state = OpenLoggedIn
           _server = mssql-server2
           _fResetConnection = False
           _defaultCollation = (LCID=1033, Opts=25)
           _defaultCodePage = 1252
           _defaultLCID = 1033
           _defaultEncoding = System.Text.SBCSCodePageEncoding
           _encryptionOption = OFF
           _currentTransaction = (null)
           _pendingTransaction = (null)
           _retainedTransactionId = 257698037761 // <-- this id is not send in the MARS TDS header
           _nonTransactedOpenResultCount = 0
           _connHandler = 7
           _fMARS = False
           _sessionPool = (null)
           _isYukon = True
           _sniSpnBuffer = 1
           _errors = (null)
           _warnings = (null)
           _attentionErrors = (null)
           _attentionWarnings = (null)
           _statistics = True
           _statisticsIsInTransaction = False
           _fPreserveTransaction = False         _fParallel = False
<prov.DbConnectionPool.PutNewObject|RES|CPOOL> 2, Connection 7, Pushing to general pool.

The transaction descriptor in the MARS TDS header is described here.

[JRahnama]

@swh-cb we will look into this and will update the thread,