System.Transaction may fail in multiple-thread environment

When implementing the same System.Transaction concurrently in multiple-threaded environment, for example:

 

a. Main thread creates DTC transaction, receives DependentTransaction (created using Transaction.Current.DependentClone on the main thread

b. Child thread 1 enlists in this DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)

c. Child thread 1 opens a connection

d. Child thread 2 enlists in DTC transaction by creating a transaction scope based on the dependent transaction (passed via constructor)

e. Child thread 2 opens a connection

 

errors may be various when multiple threads as above executing together:

 

Transaction context in use by another session…..

Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(error not found)…

The transaction has aborted…..

 

Exception Call Stack on client side can be:

 

Exception Details:

 Message: Transaction context in use by another session.

 StackTrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

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.PropagateTransactionCookie(Byte[] cookie)

   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)

   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)

   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

 

And on SQL 2005 side, the transaction aborting call stack can be:

 

0:004> kL

ChildEBP RetAddr

3449f484 61169e82 sqlservr!ISSResourceAsynch::AbortRequest+0x7e

3449f4a4 61169fb2 msdtcprx!CIEnlistmentAsynch::AbortRequestWrapper+0x2f

3449f4d8 6116a12b msdtcprx!CIEnlistmentAsynch::SetState+0x8a

3449f4fc 6116ae89 msdtcprx!CIEnlistmentAsynch::SMD_ESTATE_ENLISTED+0x4e

3449f53c 6116d6c6 msdtcprx!CIEnlistmentAsynch::StateMachineDriver+0x13b

3449f584 611afaaf msdtcprx!CIEnlistmentAsynch::Receive+0x42e

3449f5ac 611b011a msdtcprx!CIConnSink::ReceivedUserMessage+0x9a

3449f5c4 611a4904 msdtcprx!CIConnSink::IncomingEvent+0xac

3449f614 611a4b4d msdtcprx!CConnectionObject::DeliverIncomingEvents+0x246

3449f628 611a5021 msdtcprx!CConnectionObject::AttemptToDeliverIncomingEvents+0x58

3449f640 611a7b20 msdtcprx!CConnectionObject::QueueInComingMessage+0x4d

3449f81c 611a2328 msdtcprx!CQueueManagerInIP::Receive+0x551

3449f848 6119a623 msdtcprx!CRpcIOManagerServer::Receive+0xe2

3449f890 77c70f4b msdtcprx!SendReceive+0x42

3449f8b4 77ce23f7 rpcrt4!Invoke+0x30

 

From the SQL trace side, before spid 10 dropped the transaction, spid 53 and 54 tried to get the DTC address cocurrently (spid 52 promote to DTC transaction as more than one connect ion needs to join the SQL transaction) and then the failure happened:

 

DTC 

 

There is one article which doesn’t deeply discuss this symptom, but mentioned it:

 

https://msdn.microsoft.com/en-us/library/cc511672.aspx  

 

[Multiple threads sharing the same transaction in a transaction scope will cause the following exception: "Transaction context in use by another session."]

 

Actually when two or more separate SqlConnections from same process or even different processes attempt to simultaneously enlist in the same distributed transaction, one or more may fail to enlist and report an exception. The reason for this is the server side code has no tolerance for multiple concurrent enlist operations on the same transaction, it will just immediately fail one of them. Server will not try to wait for a little and try again, server will not queue the requests, it will just immediately fail the conflicting one. So far the SQL Product team has no plans to support this functionality (simultaneously enlist two different connections in the same distributed ) right now.

 

Based on the information, synchronizing the connections or keep trying enlist until it succeeds are workarounds. From my understanding the second method could be unpredictable when distributed environment and exception conditions become complicated, the “synchronizing the connections” can be better for this scenario.

 

Best Regards,

Freist Li