MS SQL Transport: System.Transactions.TransactionAbortedException: The transaction has aborted error

codemech · March 24, 2020, 4:30pm

I am interested in troubleshooting the TransactionAbortedException error in the stacktrace below. We don’t get many of these errors (1 or 2 per month) but I would like to resolve them if possible. Can you suggest any course of action for me to follow? Please let me know if I can provide any additional information.

I am looking forward to your assistance on this issue.

NServiceBus version=“6.4.3” targetFramework=“net461”

SQL Connection
providerName=“System.Data.SqlClient”
connectionString=“Password=;Persist Security Info=True;User ID=;Initial Catalog=nservicebus;Data Source=****;Connection Timeout=10;Application Name=Handler2.Service;Asynchronous Processing=True” />

Stacktrace
System.Transactions.TransactionAbortedException: The transaction has aborted. —> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. —> System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first. —> System.ComponentModel.Win32Exception: The wait operation timed out
— End of inner exception stack trace —
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
— End of inner exception stack trace —
at System.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.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.SqlConnectionFactory.<>c__DisplayClass2_0.<b__0>d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.SqlConnectionFactory.d__1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.TableBasedQueueDispatcher.d__4.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.TableBasedQueueDispatcher.d__2.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.MessageDispatcher.d__1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.TransportReceiveToPhysicalMessageProcessingConnector.d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\Incoming\TransportReceiveToPhysicalMessageProcessingConnector.cs:line 60
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.MainPipelineExecutor.d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\MainPipelineExecutor.cs:line 34
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.ReceiveStrategy.d__14.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Transport.SQLServer.ProcessWithTransactionScope.d__3.MoveNext()

SzymonPobiega · March 25, 2020, 2:30pm

Hey

Based on the There is already an open DataReader associated with this Command which must be closed first. message I think you might have a data reader in your handler’s data access code that is not closed/disposed within the handler. Then when sending messages that have been batched the transaction escalation fails. I assume that you use a different database for the data modified in the handler and for the transport, right?

Szymon

codemech · March 25, 2020, 3:10pm

Thank you for your fast response. I will review the data access code as you suggested and report my findings here. Also, I will check on if we use a different database for the data modified in the handler and for the transport. I believe that we recently added a table to the NServiceBus database for tracking metrics on Saga usage. i will report back with my findings.

Richard

codemech · March 26, 2020, 7:11pm

Szymon,

You mention in your response:
“I assume that you use a different database for the data modified in the handler and for the transport, right?”
We actually have several tables that we read and write to in this nservicebus database.
Can you point me to a link that discusses why this a bad practice?
Thank you,
Richard

SzymonPobiega · March 27, 2020, 12:13pm

@codemech I did not mean to suggest that it is a bad practice. I wanted to make sure that you do have at last two databases involved because otherwise the transaction escalation exception would mean that something is off with the connection management (transaction escalation can happen e.g. when in a single transaction scope you open two concurrent connection to the same database).

Szymon

codemech · March 27, 2020, 1:04pm

Szymon,

Thank you for that clarification. I will continue to analyze our exiting code to see if we are disposing of our data readers properly.

Richard

khafc · March 27, 2020, 3:01pm

Hi Szymon,

Would you recommend separating all data access out of the db nsb uses to run?
If this is not an nsb specific error could we also run into it in another db?
Is the issue a single saga/handler instance accessing the db again without disposing or could it be an issue with multiple sagas/handlers accessing the same db?
I am wondering if it has something to do with the the data access framework we are using. Are there any recommended frameworks? We are using AsyncPoco which is open source. GitHub - tmenier/AsyncPoco: A long-"awaited" fully asynchronous PetaPoco fork

SzymonPobiega · April 2, 2020, 11:40am

Hey

Would you recommend separating all data access out of the db nsb uses to run?

That depends on what the system is doing and what are the consistency requirements in it. We generally don’t recommend to do any other custom DB operations when a message is handled by a Saga as it complicates things.

If this is not an nsb specific error could we also run into it in another db?

No, this is not a NSB-specific error. That error means that something is off with the DB operations when using distributed transaction support provided by the System.Transactions package.

Is the issue a single saga/handler instance accessing the db again without disposing

NServiceBus executes all handlers (including sagas) sequentially so the fact that multiple handlers access the same database should not cause the problems. There are generally two strategies for doing it. One is to have a shared Unit of work which is basically a data access context that is opened before any handler is executed and closed/disposed after last handler is done. This way all the changes are executed in a single transaction.

The second strategy is for each handler to open and dispose its own data access context, e.g. when each handler uses a different data access framework. In that case System.Transactions ensures all the changes are executed atomically either by propagating a transaction to a distributed one or by maintaining an open local transaction in the connection pool.

I am wondering if it has something to do with the the data access framework we are using

Well, any framework can have a bug. That’s of course possible. It is hard for us to tell anything about this particular one.

Now I have some questions for you

Can you tell me what is happening what that message is processed i.e. if it is handled by a saga, a regular handler or both?
Can you tell me if the transport uses the same database as the persistence and what persistence you use?
Can you tell me how are you managing the connections to the data access framework?

Szymon