What Is the Benefit of Using "Set Xact_Abort On" in a Stored Procedure

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,

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.

Does SET XACT_ABORT ON do anything in a stored procedure if you're NOT in a transaction?

SET XACT_ABORT ON changes the behavior of statement-terminating errors so that they instead become batch-aborting errors. The batch within which your procedure is executing would be more likely to stop completely when an error was encountered, rather than continuing on to the next T-SQL statement.

This has implications for error-handling. Statement-terminating errors which would normally allow you to continue and execute handling code via a IF @@ERROR <> 0 block within your procedure will not be executed. What's worse, there is no way you can intercept batch-abortion in T-SQL code, so unless there is a check on @@ERROR immediately in a following batch on the same connection, you might not know there was a problem.

Perhaps your INSERT is failing quietly in some way that still allows the IDENTITYseed to be incremented. This is not uncommon...identity values reside in the SQL Server memory cache, the volatility of which provides no guarantee that those values be continuous and without gaps.

Alternatively, the calling context of the stored procedure matters. If called from within an outer-scope transaction, either initiated by SQL Server or at the application level, then a rollback at that outer scope will rollback the inner scope work, regardless of explicit transaction handling code at the inner scope. Again, the IDENTITY seed would be incremented.

TRY/CATCH blocks (available since SQL Server 2005) obviate the need for SET XACT_ABORT ON.

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.

How to use SET XACT_ABORT ON the right way

It is not the same. It decides when errors are thrown.

You should always use SET XACT_ABORT ON, because it is more consistent; almost always, an error will stop execution and throw an error. Else, half things throw errors and the other half continue execution.

There is a great article about this whole subject on Erland Sommarskog's site, and if you go at this point you will see a table which describes this strange behaviour. In conclusion, I recommend the General Pattern for Error Handling, as it is very well documented as well as provide you the opportunity to tweak it according to its own documentation.

What is the difference between SET xact_abort ON and try/catch block with Transaction handling in sqlserver 2005?

Try/Catch blocks are new with SQL server 2005 and allow you to handle errors as opposed to just having them rolled back - Try/Catch blocks restrict you to a single batch, but of course that's moot within a stored procedure. If your procedures must remain compatible with previous versions of SQL server, you might consider XACT_ABORT if it helps, but I would submit that Try/Catch is the way to go going forward.

Do you need to call SET XACT_ABORT only when starting a transaction?

As far as I can see when execution of a child batch ends it restores the previous value of this setting.

SET NOCOUNT ON;

BEGIN TRAN

SELECT CASE
         WHEN 16384 & @@OPTIONS = 16384
           THEN 'XACT_ABORT IS ON'
         ELSE ' XACT_ABORT IS OFF'
       END

CREATE TABLE #T
  (
     C INT
  )

SET XACT_ABORT OFF

INSERT INTO #T
VALUES      (1)

EXEC ('SET XACT_ABORT ON; 
      INSERT INTO #T VALUES(2);
        SELECT CASE
                    WHEN 16384 & @@OPTIONS = 16384
                    THEN ''XACT_ABORT IS ON''
                    ELSE '' XACT_ABORT IS OFF''
                END 
      ')


INSERT INTO #T
VALUES      (1 / 0)

/*If XACT_ABORT was on we would never get here but we do!*/
COMMIT

SELECT *
FROM   #T

DROP TABLE #T

SELECT CASE
         WHEN 16384 & @@OPTIONS = 16384
           THEN 'XACT_ABORT IS ON'
         ELSE ' XACT_ABORT IS OFF'
       END 

Returns

------------------
 XACT_ABORT IS OFF


------------------
XACT_ABORT IS ON

Msg 8134, Level 16, State 1, Line 31
Divide by zero error encountered.
The statement has been terminated.
C
-----------
1
2


------------------
 XACT_ABORT IS OFF

Does SET XACT_ABORT ON do anything in a stored procedure if you're NOT in a transaction?

SET XACT_ABORT ON changes the behavior of statement-terminating errors so that they instead become batch-aborting errors. The batch within which your procedure is executing would be more likely to stop completely when an error was encountered, rather than continuing on to the next T-SQL statement.

This has implications for error-handling. Statement-terminating errors which would normally allow you to continue and execute handling code via a IF @@ERROR <> 0 block within your procedure will not be executed. What's worse, there is no way you can intercept batch-abortion in T-SQL code, so unless there is a check on @@ERROR immediately in a following batch on the same connection, you might not know there was a problem.

Perhaps your INSERT is failing quietly in some way that still allows the IDENTITYseed to be incremented. This is not uncommon...identity values reside in the SQL Server memory cache, the volatility of which provides no guarantee that those values be continuous and without gaps.

Alternatively, the calling context of the stored procedure matters. If called from within an outer-scope transaction, either initiated by SQL Server or at the application level, then a rollback at that outer scope will rollback the inner scope work, regardless of explicit transaction handling code at the inner scope. Again, the IDENTITY seed would be incremented.

TRY/CATCH blocks (available since SQL Server 2005) obviate the need for SET XACT_ABORT ON.

---

Related Topics