Menu
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?
811
SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)
XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
The @@OPTIONS function returns a bitmap of the options, converted to a base 10 (decimal) integer. The bit settings are stored in the locations described in a table in the topic Configure the user options Server Configuration Option.
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.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
