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?

  • Why are there 2 durations on the Glimpse SQL tab?
  • Using a Variable in OPENROWSET Query
  • Which is a better method for storing images - folder or SQL Server as binary?
  • Creating SQL Server backup file (.bak) with c# to any location
  • create pdf files in sql server
  • Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  • 5 Solutions collect form web for “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,

    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]
        set nocount on;
        declare @trancount int;
        set @trancount = @@trancount;
        begin try
            if @trancount = 0
                begin transaction
                save transaction usp_my_procedure_name;
            -- Do the actual work here
            if @trancount = 0   
        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
            if @xstate = 1 and @trancount = 0
            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   

    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.

    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.

    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 occuring in SQL server code. In this case the client code has no fault, but has to protect it self from blocking foreveor 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.

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.