Why isn't SET XACT_ABORT ON the default behavior?
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.
Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?
You can set XACT_ABORT ON
as a global default connection setting at the server level, although the command is a bit obscure:
EXEC sys.sp_configure N'user options', N'16384'
GO
RECONFIGURE WITH OVERRIDE
GO
See here for details.
The option can also be set through SSMS Object Explorer > Server Properties > Connections:
Do I really need to use SET XACT_ABORT ON?
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.
What is the benefit of using SET XACT_ABORT ON in a stored procedure?
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.)
Since a query timeout will leave the transaction open, SET XACT_ABORT ON
is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.
There's a really great overview on Dan Guzman's Blog,
How to set SET XACT_ABORT ON in a SQL Server transaction?
You normally set xact_abort
as part of the body of the stored procedure:
CREATE PROCEDURE MyProc
AS
SET XACT_ABORT ON
BEGIN TRAN
....
There are two "special" settings that are remembered from the session that created the procedure. Explanation from MSDN:
Stored procedures execute with the SET settings specified at execute
time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored
procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the
setting specified at stored procedure creation time. If used inside a
stored procedure, any SET setting is ignored.
So when you create a stored procedure, SQL Server copies the QUOTED_IDENTIFIER option from the connection to the procedure definition. The goal is that someone else with a different QUOTED_IDENTIFIER setting still gets the behavior the author of the procedure intended.
The same is not true for XACT_ABORT
.
What is the scope of XACT_ABORT
Technet Using Options in SQL Server hints that all SET options are scoped at connection or batch level.
MSDN SET Statements adds details:
If a SET statement is run in a stored procedure or trigger, the value
of the SET option is restored after control is returned from the
stored procedure or trigger. Also, if a SET statement is specified in
a dynamic SQL string that is run by using either sp_executesql or
EXECUTE, the value of the SET option is restored after control is
returned from the batch specified in the dynamic SQL string.
It's also possible to enable XACT_ABORT by default for all users via user options:
EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE
It can also be enforced for selected users only via custom logon trigger.
See also important details on XACT_ABORT behaviour.
How to set SET XACT_ABORT ON in a SQL Server transaction?
You normally set xact_abort
as part of the body of the stored procedure:
CREATE PROCEDURE MyProc
AS
SET XACT_ABORT ON
BEGIN TRAN
....
There are two "special" settings that are remembered from the session that created the procedure. Explanation from MSDN:
Stored procedures execute with the SET settings specified at execute
time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored
procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the
setting specified at stored procedure creation time. If used inside a
stored procedure, any SET setting is ignored.
So when you create a stored procedure, SQL Server copies the QUOTED_IDENTIFIER option from the connection to the procedure definition. The goal is that someone else with a different QUOTED_IDENTIFIER setting still gets the behavior the author of the procedure intended.
The same is not true for XACT_ABORT
.
Transactions Rolling Back by default when XACT_ABORT is off
If XACT_ABORT = OFF
then it is very unpredictable if the transaction rolls back or not. SQL Server sometimes does not, sometimes it does and sometimes it even aborts the batch. (Yes, this does not make any sense.) Other possible outcomes include dooming the transaction or cutting the connection.
In your case you can reliably use TRY-CATCH
to prevent a rollback and handle the exception.
I found it a good practice to not rely on error processing if possible. Instead, roll back the transaction. Also push error handling into the client if possible.
Related Topics
#1146 - Table 'Phpmyadmin.Pma_Tracking' Doesn't Exist
Counting Rows for All Tables at Once
Subtracting One Row of Data from Another in SQL
Django Orm - Get Latest Record for Group
Pros and Cons of Autoincrement Keys on "Every Table"
Example of Three Valued Logic in SQL Server
Access SQL Using Top 5 Returning More Than 5 Results
Counting Number of Records Hour by Hour Between Two Dates in Oracle
How to Transform Rows into Columns in SQL Server 2005
SQL Server Triggers - Order of Execution
Execute Query on SQL Server Analysis Services with Ironpython
Can There Be Constraints with the Same Name in a Db
Pairwise Array Sum Aggregate Function
SQL Server Equivalent of MySQL's Now()
Stratified Random Sampling with Bigquery
Split a Varchar in Db2 to Retrieve a Value Inside
Concatenate Many Rows into a Single Text String with Grouping