XACT_ABORT controls how strictly a SQL Server transaction responds to run-time errors. With the default OFF setting, some errors fail only the statement, allowing earlier work to commit. That can violate the practical expectation of atomicity: all of a transaction succeeds, or none of it does. section 7.1 section 7.7
Mistake #26: treating every failed statement as a failed transaction. A foreign-key failure can leave a sales-order header committed without any line items when XACT_ABORT is OFF. Such partial data is harder to diagnose when the error is not written to the error log. section 7.1
Protecting a multi-table write
Turn XACT_ABORT ON before a transaction that loads related tables. If a minor error such as a failed foreign-key constraint occurs during XML shredding, the transaction stops rather than continuing with inconsistent rows across the target tables. Add explicit error handling as well, particularly for externally supplied data. section 3.4.1 section 7.1
Use XACT_ABORT as part of a safer transaction rule set:
- Expect OFF by default, and do not assume a statement error automatically rolls back earlier statements. section 7.1
- Enable it where a transaction must not commit partial work after lower-severity run-time errors. section 7.1 section 7.7
- Pair it with TRY..CATCH and meaningful raised errors; XACT_ABORT does not replace error handling. section 7.1 section 7.7
Listing 7.5 Turning XACT_ABORT on globally for instance connections
EXEC sp_configure
'user options'
, '16384' ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
This instance-level configuration example sets the user-options bit for XACT_ABORT so new instance connections use it. Treat a global change cautiously because it changes session behavior broadly. section 7.1
