Why Isn't Set Xact_Abort on the Default Behavior

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:

Sample Image

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



Leave a reply



Submit