Connection pool corruption when the distributed MSSQL transaction is aborted explicitly

[swh-cb]

If the MSSQL transaction is explicitly aborted (e.g. ROLLBACK TRANSACTION) in a distributed transaction scope, the connection pool managed by Microsoft.Data.SqlClient will be corrupted after the transaction is aborted. The connection is returned from the transacted pool back to the general connection pool. If the application reuses the same connection from the connection pool, Microsoft.Data.SqlClient always throws 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.

This code reproduces the error:

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)
        await sqlConnection.OpenAsync();
    }
}

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

The error is not reproducible when the code runs on .NET Framework.

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)

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
           _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.

This error occurs when the following four conditions are met:

• The application has enlisted into a distributed transaction.
• The transaction has ended, either committed or rolled back, for any reason.
• The user application has not explicitly defected from a distributed transaction or explicitly enlisted into a new distributed transaction.
• The application tries to do any transactional operation other than defecting from existing distributed transaction or enlisting to a new distributed transaction, such as issuing a query or starting a local transaction.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8525-database-engine-error

[swh-cb]

Unlike the .NET Framework MSSQL data provider, the .NET Core MSSQL data provider does not send the previous transaction ID to the database on the TransactionManagementRequest request when the distributed transaction is explicitly rolled back. As a result, the transaction cannot be unenlisted from the pooled connection. The bug is reported here: dotnet/SqlClient#1623

[swh-cb]

Version 1.4.0 will contain a new patcher MsSqlPatcher for Microsoft.Data.SqlClient and System.Data.SqlClient that patches the MARS TDS header write logic in the MSSQL data provider. To avoid the connection pool corruption, call MsSqlPatcher.Patch(typeof(SqlConnection).Assembly) in the entry point of your application. The patcher should be used until Microsoft releases a new version that includes the fix from dotnet/SqlClient#1624.