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?
+1
A:
I believe SET XACT_ABORT ON was a requirement when executing distributed transactions.
From the books on line: XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.
Ralph Shillington
2009-05-27 23:22:23
+3
A:
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.
gbn
2009-05-28 05:15:10
so there is a benefit to using this if there is a timeout. what happens to locks if you have an error and are not using XACT_ABORT ON?
KM
2009-05-28 12:38:22
They remain until the connection is closed, when it's all rolled back. When you close a connection in the client, connection pooling may kepp it alive longer than you think.. so it stays open and no rollback happens. XACT_ABORT ON forces a rollback. And with SQL 2005 it has no adverse effects.
gbn
2009-05-28 13:15:50
if I intend to rollback on any error, would it be wise to just use XACT_ABORT ON in addition to all the normal try-catch?
KM
2009-05-28 13:39:03
We use it in all code. We also have explicit BEGIN/COMMIT/ROLLBACK. It may not be needed but we added XACT_ABORT afterwards
gbn
2009-05-28 14:12:28
+1
A:
XACT_ABORT does indeed affect error handling: it will abort the entire batch when an error is encountered, and any code following the line that produced the error (including error checking!) will NEVER execute. There are two exceptions to this behavior: XACT_ABORT is superseded by TRY...CATCH (the CATCH block will always execute, and transactions will NOT be rolled back automatically, only rendered uncommitable), and XACT_ABORT will ignore RAISERROR.
Not completely correct: not all transactions will be doomed, some will still remain committable. Also CATCH block does not catch all errors.
AlexKuznetsov
2009-09-29 13:25:09
A:
Hi, Can we use XACT_ABORT ON to execute DDL statements, to revert changes in metadata if error occurs? Is there any constraint like that type of statements does not get reverted or mermory issues encounters etc? Moreover if we can do so may i have some code sample for the same? Thanks in advance...
-- Lalit
Lalit
2009-12-03 12:16:39