Don't doom transactions unless there is a logical reason for it - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


14
0
Sign in
to vote
ID 740673 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/4/2012 2:25:21 PM
Access Restriction Public

Description

Currently, the inconsistency of what happens when an error occurs is just amazing. 

If there is no CATCH handler, any of these can happen:
1) Batch is aborted and transaction rolled back.
2) Batch is aborted but transaction is not rolled back. (New in SQL 2012!)
3) Scope is aborted, transaction is not rolled back. Execution continues in caller.
4) Statement is terminated and rolled back. Execution continues with next statement, any transaction is still active.

Which error that gives which reaction is quite willy-nilly, unless XACT_ABORT is ON, in which case, most - but not all errors - ends up in the first category.

If there is a CATCH handler, any of these three things can happen:
A) CATCH handler fires, any active transaction is doomed and must be rolled back.
B) CATCH handler fires and any active transaction can still be continued on.
C) CATCH handler does not fire. (But a CATCH handler in outer scope will fire.)

There is a corellation so that 1 goes with A, 2 and 4 goes with B and 3 goes with C.

In many cases doomed or not doomed does not matter, because you want to roll back anyway. However, there are situations where you want to recover from the error. That could be by taking a corrective action or an alternate strategy. But you may also want to roll back to a savepoint. This is not the least important if you want to cater for that you may have started a nested transaction, but you don't want to pull the rug for the caller.

One situation is worth a special mention: activation procedures for Service Broker. If the processing of a message fails, you may still want to get that message of the queue into an error table or whatever to avoid that the queue is closed down. Currently there is no safe way to do this. You can use a savepoint, but there is no guarantee that you will be able to rollback to the savepoint.

The behaviour if there is no CATCH handler would be that an error always aborted the batch, and the transaction would be rolled back if a) it was started in the current batch or b) it is critical for the server that the transaction is rolled back. (read: deadlock). Obviously, this would break backwards compatibility if this was introduced as the default behaviour, but if SET XACT_ABORT ON was extended to cover all errors, it would be close. (With XACT_ABORT ON, transactions are always rolled back. That's OK.)

The important is what should happen if there is a CATCH handler. First of all, it should always fire, but that is covered in a separate Connect request, 
https://connect.microsoft.com/SQLServer/feedback/details/739957/try-catch-should-always-work

Next, the transaction should only be doomed - XACT_ABORT ON or not - if there is a logical reason for it. In practice I would say that the errors that warrants a doom transactions are those with severity >= 17 and deadlocks. Again, this would be a behavioural change, but I find it difficult to believe that it would be a change that any user would call breaking.

I like to stress that even if I for completeness sake discusses both the situation where there is a CATCH handler, and when there is not, the much more important case is when there is a CATCH handler so that you can implement reliable retry code.

Sign in to post a comment.
Posted by Microsoft on 2/8/2013 at 10:59 AM
Erland,
Yes, this could be handled better. Unfortunately, every time we've attempted an overhaul, we've run into back-compat issues that caused us to doom that transaction...
For now, this isn't going to change. We may consider a whitepaper that documents which errors cause which responses, so it wouldn't be as unpredictable.