SET XACT_ABORT
Usually we use TRY… CATCH block of error handling where in CATCH block transaction is rolled back. But sometimes we simply use BEGIN.. COMMIT transaction without any TRY..CATCH block. In this scenario entire batch is not rolled back.
Usually we use TRY… CATCH block of error handling where in CATCH block transaction is rolled back. But sometimes we simply use BEGIN.. COMMIT transaction without any TRY..CATCH block. In this scenario entire batch is not rolled back.
BEGIN TRANACTION
INSERT 1
INSERT 2 --This statement failed
INSERT 3
COMMIT TRANSACTION
In above case INSERT 1 and INSERT 3 still executes successfully.
SET XACT_ABORT
- It is ideal for "all or nothing" kind of transactions.
- It takes the whole batch as one transaction. If any of the statement in batch fails, whole batch is rolled back.
SET XACT_ABORT ON
BEGIN TRANACTION
INSERT 1
INSERT 2 --This statement failed
INSERT 3
COMMIT TRANSACTION
- In this case INSERT 1 and INSERT 3 are also rolled back.
Advantage
- TRY .. CATCH does not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction. So You will end up with an opened transaction.
- XCAT_ABORT rolls back transaction as soon as error occurs. So there will be no open transaction. Open transactions are major cause of performance degradation.
Disadvantage of using with TRY .. CATCH block (For non-schema related errors)
- As CATCH block will not be executed, If you are handling / raising error in CATCH block. It will not be possible.
