What is the point of TRY CATCH block when XACT_ABORT is turned ON?



  • 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?



  • 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 https://www.sommarskog.se/error_handling/Part2.html#uncatchable ). Syntax errors are also not caught, 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 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=12bf2ce747394da1a15bd632759fae3c 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 be ON




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2