1

Reason for System.Transactions.TransactionInDoubtException mentions three reasons for transactions being promoted to MSDTC. The first two are fairly well known, however the third reason is the following:

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.

I am seeing this behavior in one of my server apps when it is under severe load (SQL 2012). I've tried Googling extensively, but I'm not finding any more info. Does anyone have any references to additional information on this topic?

thanks,

Larry

Community
  • 1
  • 1
LESchwartz
  • 11
  • 3
  • What symptoms are you experiencing? Just that exception? And why is that exception a problem? Your retry logic should swallow it. – usr Jul 09 '15 at 23:54
  • We have nested transactions. The inner one has the retry loop. Under severe load the retry loop times out, rolls back, escalates to MSDTC, retries. Most times it succeeds, but on occasion I also get MSDTC failues. The MSDTC failure can cause one of the transactions to rollback, while the other does not. – LESchwartz Jul 10 '15 at 14:51
  • 1
    Are you aware that neither SQL Server not System.Transactions support nested transactions? – usr Jul 10 '15 at 15:00
  • We have nested transactions, the inner one has the retry loop. Under load the retry loop times out, rolls back, and escalates. On occasion we get MSDTC failues too. MSDTC failures cause one transaction to rollback, while the other does not. I'm curious why we get the escalation to MSDTC. The post referenced originally says that what we're experiencing can happen, but doesn't elaborate on the details. We hypothesize that on occasion the connection gets so FUBAR that the underlying code abandons / closes it and opens a new one, leading to nested connections, and the escalation to MSDTC. Larry – LESchwartz Jul 10 '15 at 15:07
  • So you are running two transactions on two connections connected by MSDTC? You *cannot* run two transactions over one connection. It's really just one tran with a ref count of 2. – usr Jul 10 '15 at 15:10
  • I guess we're really running one transaction over one connection with a ref count of two. Which I agree is bogus and should be re-coded. However, there times when the inner transaction rolls back and retries. This looks to fire up a second connection for the inner transaction. Which would cause MSDTC to be invoked to try to coordinate. I'm trying to gather more information, which is why I asked is anyone had any additional information. – LESchwartz Jul 10 '15 at 18:18

1 Answers1

0

I guess we're really running one transaction over one connection with a ref count of two. Which I agree is bogus and should be re-coded.

This is not a problem by itself.

However, there times when the inner transaction rolls back and retries

The problem is that a rollback rolls back everything. You can't retry the "inner" work in isolation. (Yes, this would be super useful but SQL Server does not support it.)

This looks to fire up a second connection for the inner transaction. Which would cause MSDTC to be invoked to try to coordinate.

This is moot because at this point the "outer" work has been destroyed.

There is no great solution for this problem. The best strategy is likely to have only one transaction and retry outer and inner work as one unit. Retries always must retry the entire transaction. You can use transaction "ref counting" if you want but you can't use it to rollback.

A particular nasty feature of SQL Server is that it is unpredictable whether any particular error causes the transaction to roll back. Therefore it is the cleanest way to never handle errors with SQL Server and always declare the transaction to be lost. (There is no technical reason SQL Server has to do this. It's just a stupid design choice.)

usr
  • 168,620
  • 35
  • 240
  • 369