SET XACT_ABORT in SQL Server

SET XACT_ABORT ON instructs SQL Server to roll back the entire transaction and abort the batch when a run-time error occurs.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

SET XACT_ABORT ON is still needed with structured error handling. A client attention event (timeout or query cancel) will stop the executing code and the CATCH block will not be invoked. This could leave an open transaction unless the application code performs a rollback or closes the connection.

Its recommended to specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly.

When you use XACT_ABORT with structured error handling, you should check XACT_STATE() in the CATCH block to determine whether or not COMMIT/ROLLBACK is possible.