Reason for System.Transactions.Transactionindoubtexception

Reason for System.Transactions.TransactionInDoubtException

Even if the transaction is local, transaction will still escalated to the MSDTC if you open multiple connections within the same transaction scope, according to this article: http://msdn.microsoft.com/en-us/library/ms229978(v=vs.110).aspx

An escalation that results in the System.Transactions infrastructure
transferring the ownership of the transaction to MSDTC happens when:
...

• At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting
  a single connection with does not cause a transaction to be promoted.
  However, whenever you open a second connection to a database causing
  the database to enlist, the System.Transactions infrastructure detects
  that it is the second durable resource in the transaction, and
  escalates it to an MSDTC transaction.

NOTE: I have read some articles that state that this only applies to SQL 2005, and that SQL 2008+ is smarter about the MSDTC promotion. These state that SQL 2008 will only promote to MSDTC when multiple connections are open at the same time. See: TransactionScope automatically escalating to MSDTC on some machines?

Also, your inner exception is a Timeout (System.Data.SqlClient.SqlException: Timeout expired), not a Deadlock. While both are related to blocking, they are not the same thing. A timeout occurs when blocking causes the application to stop waiting on a resource that is blocked by another connection, so that the current statement can obtain locks on that resource. A deadlock occurs when two different connections are competing for the same resources, and they are blocking in a way they will never be able to complete unless one of the connections is terminated (this why the deadlock error messages say "transaction... has been chosen as the deadlock victim"). Since your error was a Timeout, this explains why you deadlock query returned a 0 count.

System.Transactions.TransactionInDoubtException from MSDN (http://msdn.microsoft.com/en-us/library/system.transactions.transactionindoubtexception(v=vs.110).aspx) states:

This exception is thrown when an action is attempted on a transaction
that is in doubt. A transaction is in doubt when the state of the
transaction cannot be determined. Specifically, the final outcome of
the transaction, whether it commits or aborts, is never known for this
transaction.

This exception is also thrown when an attempt is made to
commit the transaction and the transaction becomes InDoubt.

The reason: something occurred during the TransactionScope that caused it's state to be unknown at the end of the transaction.

The cause: There could be a number of different causes, but it is tough to identify your specific cause without the source code being posted.

Things to check:

1. If you are using SQL 2005, and more than one connection is opened, your transaction will be promoted to a MSDTC transaction.
2. If you are using SQL 2008+, AND you have multiple connection open at the same time (i.e. nested connections or multiple ASYNC connections running in parallel), then the transaction will be promoted to a MSDTC transaction.
3. If you have "try/catch{retry if timeout/deadlock}" logic that is running within your code, then this can cause issues when the transaction is within a System.Transactions.TransactionScope, because of the way that SQL Server automatically rolls back transaction when a timeout or deadlock occurs.

TransactionInDoubtException using System.Transactions on SQL Server 2005

The answer is that it can't. What apparently was happening was that promotion was taking place. (We accidentally discovered this) I still don't know how to detect if a promotion attempt is happening. That would have been extreamly useful in detecting this.

How to handle TransactionInDoubtException

A transaction is in doubt when it's unknown whether it has been completed or aborted. The database is still in a consistent state. If you ever get such an exception, you need to access the database to figure out whether the changes has taken place or not, but you can be sure you won't see half of the changes.

Note, I don't know what happens if the component in doubt is the DTC, and you have a distributed transaction - I sure hope Microsoft took care of that, and that a doubtful distributed transaction is still treated as a whole.

See here: "Specifically, the final outcome of the transaction, whether it commits or aborts, is never known for this transaction."

TransactionScope automatically escalating to MSDTC on some machines?

SQL Server 2008 can use multiple SQLConnections in one TransactionScope without escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.

I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?

The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.

---

Related Topics