What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

Question

What is the benefit of using SET XACT_ABORT ON in a stored procedure?

Filippo

msdn ref for convenience: msdn.microsoft.com/en-us/library/ms188792.aspx – Howland

This is a really good article about it: sommarskog.se/error_handling/Part1.html – Surface

Answer 1

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 (original link)

Kibbutz

so why is it not ON by default? – Enrichetta

Is XACT_ABORT still required if you have the BEGIN TRY-BEGIN CATCH and ROLLBACK with the BEGIN CATCH block in Sql ? – Nadinenadir

@Nadinenadir BEGIN TRY-BEGIN CATCH won't catch things like a timeout occurring on the client application, and some SQL errors are uncatchable, too, leaving you with an open transaction where you would not expect one. – Adonai

Answer 2

In my opinion SET XACT_ABORT ON was made obsolete by the addition of BEGIN TRY/BEGIN CATCH in SQL 2k5. Before exception blocks in Transact-SQL it was really difficult to handle errors and unbalanced procedures were all too common (procedures that had a different @@TRANCOUNT at exit compared to entry).

With the addition of Transact-SQL exception handling is much easier to write correct procedures that are guaranteed to properly balance the transactions. For instance I use this template for exception handling and nested transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

It allows me to write atomic procedures that rollback only their own work in case of recoverable errors.

One of the main issues Transact-SQL procedures face is data purity: sometimes the parameters received or the data in the tables are just plain wrong, resulting in duplicate key errors, referential constrain errors, check constrain errors and so on and so forth. After all, that's exactly the role of these constrains, if these data purity errors would be impossible and all caught by the business logic, the constrains would be all obsolete (dramatic exaggeration added for effect). If XACT_ABORT is ON then all these errors result in the entire transaction being lost, as opposed to being able to code exception blocks that handle the exception gracefully. A typical example is trying to do an INSERT and reverting to an UPDATE on PK violation.

Sainthood

Except for client timeouts... and my view is the SET XACT_ABORT is more effective in SQL 2005 because behaviour is more predictable: far fewer batch aborting errors. – Electric

client timeouts are application bugs. No app should impose a command timeout, ever. Is such an appallingly poor practice that the default SqlClient timeout gives me a headache every time I think how could they ship something like that. You can guide your entire Transact-SQL error handling around the timeout problem, or you can code the client properly and address the business problems in error handling. Of course, my view is from a developer point of view. the ones in the admin/dba role usually view the world differently, given they are stuck with the apps other create. – Sainthood

I agree somewhat, but I plan my error handling around all eventualities, because I know I'll the get the blame as Developer DBA if a command timeout occurs. – Electric

@RemusRusanu How else would you handle a long running, synchronous, database operation? – Muldon

MSDN documentation states: "XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions." msdn.microsoft.com/en-us/library/ms188792(v=sql.120).aspx – Sundowner

In some cases the TRY doesn't THROW and the CATCH block never executes (depends on severity level and also warnings don't throw but they do abort the transaction).. see: https://mcmap.net/q/67032/-issues-with-t-sql-try-catch – Updo

"In my opinion SET XACT_ABORT ON was made obsolete by the addition of BEGIN TRY/BEGIN CATCH" - I hear you, but please see sommarskog.se/error_handling/Part1.html – Surface

TRY...CATCH versus XACT_ABORT behavior is tabulated sommarskog.se/error_handling/Part2.html#classification – Bloodsucker

"No app should impose a command timeout, ever. " I've been waiting 9 years 4 months, but i have a good feeling about year 10! – Muldon

@mutex copy-paste from some production code I had at the time. I was using GOTO lbexit – Sainthood

"client timeouts are application bugs." I don't care whose bug it is. My data must remain consistent regardless of why any failure occurred. – Footman

@RemusRusanu No, not really.. in most cases I've encountered a client timeout is due to a bad query plan selection. The client protects itself (ie. not stalling the site/program indefinitely); the server should do the same. The previous [dolt] developers probably thought it was "bug" for a client timeout too and setup a default 100 minute limit timeout; that wasted so many human resources and caused so many production issues. – Golgotha

Answer 3

Quoting MSDN:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

In practice this means that some of the statements might fail, leaving the transaction 'partially completed', and there might be no sign of this failure for a caller.

A simple example:

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

This code would execute 'successfully' with XACT_ABORT OFF, and will terminate with an error with XACT_ABORT ON ('INSERT INTO t2' will not be executed, and a client application will raise an exception).

As a more flexible approach, you could check @@ERROR after each statement (old school), or use TRY...CATCH blocks (MSSQL2005+). Personally I prefer to set XACT_ABORT ON whenever there is no reason for some advanced error handling.

Celik

Answer 4

Regarding client timeouts and the use of XACT_ABORT to handle them, in my opinion there is at least one very good reason to have timeouts in client APIs like SqlClient, and that is to guard the client application code from deadlocks occurring in SQL server code. In this case the client code has no fault, but has to protect it self from blocking forever waiting for the command to complete on the server. So conversely, if client timeouts have to exist to protect client code, so does XACT_ABORT ON has to protect server code from client aborts, in case the server code takes longer to execute than the client is willing to wait for.

Lassa

Answer 5

It is used in transaction management to ensure that any errors result in the transaction being rolled back.

Paco

Answer 6

Adding new updates here. The latest MSDN update shows how to use both XACT_ABORT ON and TRY/CATCH Block. MSDN Link

    -- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE usp_GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- A FOREIGN KEY constraint exists on this table. This   
        -- statement will generate a constraint violation error.  
        DELETE FROM Production.Product  
            WHERE ProductID = 980;  
  
    -- If the DELETE statement succeeds, commit the transaction.  
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.
    -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO

Marvellamarvellous