Menu
Reading things like this post on Dan Guzman's blog, I wonder: why isn't SET XACT_ABORT ON the default behavior? Is there a case where it's harmful, or much less desirable/efficient than SET XACT_ABORT OFF?
316
OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
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 is a function that returns to the user the state of a running transaction. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.
It's an automatic response to an error, it's more desirable if you can handle the error and recover from it. If the transaction automatically rolls back then you don't get this opportunity.
The problem Dan mentions in his blog arises because of the abort from the client, within SQL this abort doesn't exist. Hence within SQL the default is not to automatically abort transactions.
138
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
