Do I really need to use "SET XACT_ABORT ON"?
error-handling, sql-server, sql-server-2005, transactions
Solution
Remember that there are errors that TRY-CATCH will not capture with or without `XACT_ABORT`.
However, `SET XACT_ABORT ON` does not affect trapping of errors. It does guarantee that any transaction is rolled back / doomed though. When "OFF", then you still have the choice of commit or rollback (subject to xact_state). This is the main change of behaviour for SQL 2005 for `XACT_ABORT`
What it also does is remove locks etc if the client command timeout kicks in and the client sends the "abort" directive. Without `SET XACT_ABORT`, locks can remain if the connection remains open. My colleague (an MVP) and I tested this thoroughly at the start of the year.
Problem
if you are careful and use TRY-CATCH around everything, and rollback on errors do you really need to use: ``` SET XACT_ABORT ON ``` In other words, is there any error that TRY-CATCH will miss that SET XACT_ABORT ON will handle?