9

Code sample with XACT_ABORT_ON:

SET XACT_ABORT_ON;

BEGIN TRY
  BEGIN TRANSACTION
    //do multiple lines of sql here
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  IF (@@TRANCOUNT > 0) ROLLBACK;
  //may be print/log/throw error
END CATCH

Since XACT ABORT is ON, any error will automatically rollback the transaction. So what purpose does the TRY CATCH block serve?

1

3 Answers 3

12

You are right that it is not necessary to catch errors you are not intending on handling. SET XACT_ABORT ON; ensures a rollback in all circumstances (except for a couple of very weird edge cases of uncatchable errors, which Erland Sommarskog says are basically unfixed bugs). Syntax errors from dynamic SQL are also not caught and rolled back, however that is just one more good reason to use a good IDE, proper version control and avoid dynamic SQL.

In my opinion, it is only necessary to CATCH errors if you intend on dealing with them. Erland's articles are generally misunderstood, they are intended for handling errors, not just catching and re-throwing.
SET XACT_ABORT ON; is always necessary, in order to correctly roll back transactions.

And in triggers, you must never explicitly roll back. If you do, you will get a spurious error # 3609 The transaction ended in the trigger. The batch has been aborted. And XACT_ABORT is ON by default in triggers.


But there are sometimes circumstances when you do actually want to catch and handle errors within your SQL code. For this you must use BEGIN TRY BEGIN CATCH, and you also must use a conditional ROLLBACK as shown.

For example, you can see in this fiddle that a second insert outside of the transaction is still committed, even though XACT_ABORT was ON, because BEGIN CATCH was used.


TL;DR;
You only need to use BEGIN CATCH and conditional ROLLBACK; if handling the error.
SET XACT_ABORT must always be ON if you have an explicit transaction, irrespective of whether there is a CATCH, to ensure that rollback happens correctly.

0
1

Brent Ozar just did a really great blog series on this. It would be impossible to summarize all of that here, so go check out that series, in order. At the end, he also makes a few references to Erland Sommarskog's articles. Make sure to read all of those as well.

  1. https://www.brentozar.com/archive/2022/01/error-handling-quiz-week-tryin-try-catch/
  2. https://www.brentozar.com/archive/2022/01/error-handling-quiz-week-will-a-transaction-help/
  3. https://www.brentozar.com/archive/2022/01/error-handling-quiz-week-combining-transactions-and-try-catch/
  4. https://www.brentozar.com/archive/2022/01/error-handling-quiz-week-making-a-turkey-sandwich-with-xact_abort/
4
  • I'm sorry but with all due respect to Brent (who has great articles), using SET XACT_ABORT ON guarantees abortion and rollback in the case of catastrophic errors. And adding CATCH (and even another belt-and-braces ROLLBACK on the outside) in that case is pointless as it is never run. There is pretty much no case where XACT_ABORT by itself does not roll back and CATCH will do so. The only time you need an explicit rollback is if you have a CATCH for error handling, then you must explicitly ROLLBACK. The only thing remaining is syntax errors, and those you can't CATCH either Mar 21 at 0:49
  • "[One] of the main points was that XACT_ABORT may not roll back ever possible error condition." Yes but that point was never proved by Brent, and Erland has none apart from syntax errors and a couple weird bugs, none of which CATCH will help. If you can prove me wrong, I will gladly delete all this and possibly my post also, I'm not afraid of admitting I got it wrong Mar 21 at 0:51
  • @Charlieface all good points. When I look at the options between two solutions, the one question I usually ask is, “if one of the two is wrong, which one brings less risk.” In this case, if Brent was wrong, what’s the harm of still having the last rollback, even if it’s never executed? Likewise, if he’s right, and it’s not there, what’s the risk? Be it small or large. BTW, not trying to discredit your answer, which did get my upvote. You clearly are very knowledgeable on this topic. Mar 21 at 10:58
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.
    – Hannah Vernon
    Mar 25 at 12:58
-4

Reading trough this great article by Erland Sommarskog it is my understanding, that the TRANSACTION opened by your piece of code is rolled back but none of the TRANSACTIONs that may be were opened by the //do multiple lines of sql here or even TRANSACTIONs that were opened by code that code called

2
  • 4
    That's provably not true, SET XACT_ABORT ON will everything back dbfiddle.uk/…. Nested transactions are a myth, SQL Server does not support them Feb 1 at 12:43
  • @Charlieface thanks for clearing that up. I am sure I can remember that now.
    – st100
    Feb 2 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.