Xact_abort ON VS Begin Transaction in Stored Procedure.

  • Is there a scenario whereby you would create a stored procedure with the option

    Set Xact_abort ON

    And validly need to create transactions with commits inside that stored procedure?

    I have looked around online and have not had any clear answer as most refer to xact_abort and try catch blocks.

    My feeling is that Xact_abort overrides the need for begin trans with a commit, but would like to be sure.

    For a picture of my example see below

    create procedure MyProc (@Var int)

    as

    Begin

    set xact_abort on

    Begin Try

    Begin transaction trans1

    doStuff

    Commit transaction Trans1

    END Try

    Begin Catch

    Rollback transaction Trans1

    logerror(@@Error)

    END Catch

    END

    GO

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • By default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

    But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

    Like in case of PK violation it just terminates the duplicate row, and do not terminate the batch. Check the examples here I've shown in my blog post

    1. http://sqlwithmanoj.com/2011/12/06/sql-servers-weird-behavior-in-error-handling-with-transactions/[/url]

    2. http://sqlwithmanoj.com/2011/12/01/xact_abort-with-transactions/[/url]

    Thanks,

    Manoj

  • My one thought here is that a call to a stored procedure may not roll back within a transaction, whereas if you do ,say,  just inserts to multiple tables within the body of your code , then those would Rollback.  So Xact_abort is called to address a scenario such as this (with the stored procedure).

    Still, I have to admit this is a hypothesis as I have not tested this out. Maybe I will try to do that and give a definitive answer.

    ----------------------------------------------------

  • Old thread on a 7 year delay!  Minor correction to the earlier response tho.  For generic SQL statements the default is OFF, however, for TRIGGERS it's ON.  The documentation only mentions this in the Remarks afaik.  From the MS Docs:

    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. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • From Mircrosoft Docs : "

    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. "

    Implied here is the word ''Transaction." So to me you need a transaction to apply Xact_abort to.

    There is a good example here on the very page i got the quote from. Inserts into multiple tables within one transaction.

     

     

    ----------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply