15

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

20

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.

2
  • What about the opposite? i.e. are there any times when you would want XACT_ABORT OFF?
    – Dai
    Aug 14, 2023 at 9:06
  • Yes if you specifically want statement-aborting errors to fall through. But explicit BEGIN CATCH blocks would make more sense there, and in any case is a bit unusual. Aug 14, 2023 at 14:51
4

The two options serve different purposes. While it's common to issue rollback within a CATCH block, this is not the only thing you can use it for. For example...

  • Maybe you want to log the errors to disk or the Event log.
  • Maybe you want to call a another procedure if the TRY block fails.
  • Or maybe you want to send an email.

The point is the CATCH block is not limited to just rolling back a transaction. It's an entire code block on its own.

XACT_ABORT is geared more towards ensuring a block of code exits on the first error and everything is rolled back. Traditionally in SQL Server, if you do not use XACT_ABORT, it's possible for statement 1 to fail and be rolled back, while all remaining statements continue to be executed and committed. This can produced unexpected results. It's important to note that if you're enabling XACT_ABORT, be sure to use THROW instead of RAISERROR, as RAISERROR does not honor XACT_ABORT.

If you're looking for a more in-depth understanding of how TRANSACTIONS, XACT_ABORT and TRY/CATCH all work, Brent Ozar recently did a 4 part blog series on this topic.

  1. Tryin Try/Catch
  2. Will a Transaction Help?
  3. Combining Transactions and TRY/CATCH
  4. Making a Turkey Sandwich with XACT_ABORT
1
  • Excellent series of articles, thanks. Also see Remus Rusanu's answer to a Stackoverflow question: stackoverflow.com/a/7490088/216440 . He checks XACT_STATE() to determine whether to rollback a transaction or not.
    – Simon Elms
    Nov 16, 2022 at 23:28
-6

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
  • 5
    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, 2022 at 12:43
  • @Charlieface thanks for clearing that up. I am sure I can remember that now.
    – st100
    Feb 2, 2022 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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