Do I really need to use "SET XACT_ABORT ON"?

Question

if you are careful and use TRY-CATCH around everything, and rollback on errors do you really need to use:

SET XACT_ABORT ON

In other words, is there any error that TRY-CATCH will miss that SET XACT_ABORT ON will handle?

Braze

Answer 1

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.

Faery

so there is a benefit to using this if there is a timeout. what happens to locks if you have an error and are not using XACT_ABORT ON? – Braze

They remain until the connection is closed, when it's all rolled back. When you close a connection in the client, connection pooling may kepp it alive longer than you think.. so it stays open and no rollback happens. XACT_ABORT ON forces a rollback. And with SQL 2005 it has no adverse effects. – Faery

if I intend to rollback on any error, would it be wise to just use XACT_ABORT ON in addition to all the normal try-catch? – Braze

We use it in all code. We also have explicit BEGIN/COMMIT/ROLLBACK. It may not be needed but we added XACT_ABORT afterwards – Faery

Also see this related question. – Kerbing

One exception to the "rollback guaranteed" rule: if you use RAISERROR with XACT_ABORT, SQL will not roll back the transaction. You must ROLLBACK explicitly in this scenario. See sommarskog.se/error-handling-I.html#XACT_ABORT – Picked

@DanielNolan: Can't recall exactly. Nowadays I use TRY/CATCH anyway with XACT_STATE as a template (as per #2074237) – Faery

@Faery I think it's worth mentioning that this only works for severity levels > 16. – Pneumonia

@Faery I haven't seen any change in "conventional wisdom" on using XACT_ABORT ON other than the answer by Ian Boyd at the bottom of this page. Has the thinking on this changed over the years or is XACT_ABORT ON still best practice? – Doleful

@jimdrang: See my comment please to his answer. I still use it. See https://mcmap.net/q/183171/-nested-stored-procedures-containing-try-catch-rollback-pattern too please – Faery

If XACT_ABORT ON rollbacks for any error, then does it hit the CATCH block? – Medicinal

Answer 2

I believe SET XACT_ABORT ON was a requirement when executing distributed transactions.

From the books on line: 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. For more information, see Distributed Queries and Distributed Transactions.

Okhotsk

Answer 3

My understanding is that even if a try catch is used and no rollback statement is used in a catch block, any un-commitable transaction will be rolled back when XACT_ABORT is ON.

Lowelllowenstein

Answer 4

There is a caveat to blindly always using SET XACT_ABORT ON; which burned me recently.

i read a convincing argument on StackOverflow that suggested that you should always use XACT_ABORT ON. i changed the system to set that option during connect. Except it lead to data corruption and a lot of pain.

begin transaction
try
    perform insert
    catch duplicate key violation and react appropriately

    perform more actions

    commit transaction
catch
    rollback transaction
end

Except that your "more actions" will no longer be happening in a transaction. Because even though you caught the duplicate key violation, the server is no longer in a transaction:

begin transaction
try
    perform insert
    catch duplicate key violation and react appropriately
    transaction implicitly rolled back

    perform more actions

    commit transaction -> fails because not in a transaction
catch
    rollback transaction -> fails because not i a transaction
end

i've since reversed myself. Never use SET XACT_ABORT ON.

---

Edit: People seem to think the issue comes from attempting to call ROLLBACK TRANSACTION while not in a transaction. They think the problem can be fixed by not calling ROLLBACK if a transaction is not in progress.

Let's use some pseudo-code, with the names changes to protect the NDA:

const
   SQLNativeErrorPrimaryKeyViolation = 2627; //Primary keys. 2601 is for other unique index

void x(String sql)
{
   database.Connection.ExecuteNoRecords(sql);
}

which is a pedantic way of making this answer more readable; we use x to represent eXecution of some SQL statement:

void DoStuff()
{
   x("BEGIN TRANSACTION");
   try
   {
      try
      {
         x("INSERT INTO Patrons (AccountNumber, Name, Gender)"+
           "VALUES (619, 'Shelby Jackson', 'W'"); 
      } 
      catch (ESqlServerException e)
      {
         //check if the patron already exists (or some other hypothetical situation arises)
         if (e.NativeError == SQLNativeErrorPrimaryKeyViolation)
         {
            //This patron already exists. Set their frob to grob because contoso the blingblong
            x("UPDATE Patrons SET Frob='Grob' WHERE AccountNumber = 619");

            //20110918: Dont forget we also need to bang the gardinker
            x("EXECUTE BangTheGardinker @floof=619");
         }
         else
            throw e;
      }

      //Continue with the stuff
      x("EXECUTE Frob('{498BBB4D-D9F7-4438-B7A6-4AB5D57937C0}')");

      //All done, commit the transaction
      x("COMMIT TRANSACTION");       
   }
   catch (Exception e)
   {
      //Something bad happened, rollback the transaction 
      //(if SQL Server didn't kill the transaction without our permission)
      x("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION");


      throw e;
   }
}

XACT_ABORT ON is cool, lets use it

So, that code works. If there is an error that we expect, we handle it and continue. This is called handling the error. If some unknown exception happens (something we did not expect), we rollback any transaction that may be in progress.

Now lets see if we blindly follow the suggestion that XACT_ABORT should always be on:

 DbConnection Connection()
 {
    if (_connection == null)
    {
       _connection = new SqlConnection();

       //It is generally recommended that you always have xact_abort on.
       //If a connection is closed with a transaction still in progress
       //it still leaves locks held until that connection is finally recycled
       //Also, when querying linked severs in a client-side transaction, the
       //operation won't work until xact_abort is on (SQL Server will throw an saying xactabort is off
       _connection.ExecuteNoRecords("SET XACT_ABORT ON");
    }

    return _connection;
 }

void x(String sql)
{
   database.Connection.ExecuteNoRecords(sql);
}

Do you see the corruption that will cause in DoStuff?

DoStuff was correctly written to handle error cases. But the introduction of XACT_ABORT ON to the connection will now cause database corruption. For those of you who don't see the bug, lets walk through the code:

void DoStuff()
{
   x("BEGIN TRANSACTION");
   try
   {
      try
      {
         x("INSERT INTO Patrons (AccountNumber, Name, Gender)"+
           "VALUES (619, 'Shelby Jackson', 'W'"); 

      } 
      catch (ESqlServerException e)
      {
         //WARNING: WE ARE NO LONGER OPERATING IN A TRANASCTION
         //Because XACT_ABORT is on, the transaction that we started has been implicitly rolled back.
         //From now on, we are no longer in a transaction. If another error happens
         //the changes we make cannot be rolled back

         //check if the patron already exists (or some other hypothetical situation arises)
         if (e.NativeError == SQLNativeErrorPrimaryKeyViolation)
         {
            //WARNING: This update happens outside of any transaction!
            //This patron already exist. Set their frob to grob because contoso the blingblong
            x("UPDATE Patrons SET Frob='Grob' WHERE AccountNumber = 619");

            //WARNING: This stored procedure happens outside of any transaction!
            //20110918: Dont forget we also need to bang the gardinker
            x("EXECUTE BangTheGardinker @floof=619");
         }
         else
            throw e;
      }

      //WARNING: This stored procedure happens outside of any transaction!
      //If any error happens from
      //Continue with the stuff
      x("EXECUTE Frob('{498BBB4D-D9F7-4438-B7A6-4AB5D57937C0}')");

      //WARNING: This stored procedure happens outside of any transaction. It will throw:
      //   Msg 3902, Level 16, State 1, Line 1
      //   The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
      //All done, commit the transaction
      x("COMMIT TRANSACTION");       
   }
   catch (Exception e)
   {
      //If there was an error during Frob, we would want to catch it and roll everything back.
      //But since SQL Server ended the transaction, we have no way to rollback the changes

      //And even if the call to Frob (or Updating the patron's Grob, or Banging the Gardinder)
      //didn't fail, the call to COMMIT TRANSACTION will throw an error

      //Either way, we have detected an error condition that cannot be rolled back in the database


      //Something bad happened, rollback the transaction 
      //(if SQL Server didn't kill the transaction without our permission)
      x("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION");


      throw e;
   }
}

Code that was written correctly, and works, becomes broken, causes errors, and at worst causes database corruption. All because i turned on XACT_ABORT ON.

Isochor

First, the transaction may not be present on error, say if you have a trigger rollback or error. Why are you not using XACT_STATE()? https://mcmap.net/q/183171/-nested-stored-procedures-containing-try-catch-rollback-pattern. Second, I see no reason to "do more actions" on error condition when I should be in the CATCH block after a duplicate error. Give me a better example please: and I've never had corruption for my pattern... – Faery

I'm not sure people get notifications on edits of answers they have commented on. @Faery - This information seems very relevant and useful. Do you suggest we start this as a new questions for others benefit/input? – Doleful

So how you deal with situations where the connection was lost and an open transaction was left on server (I'm dealing with this now and XACT_ABORT ON seemed to be the solution. Seems now it's not? – Greensboro

@Greensboro Pick your poison. If you use XACT_ABORT_ON then you lose transactional integrity. Personally i want the ACID. If there's half-open lingering connections on a remote SQL Server, then i leave it up to TCP to close those dead connections. – Isochor

@Ian Boyd. I really want ACID, but when the connection died and left the open transaction, it affected a lot of users. The transaction can vary from entering a few rows to a few thousand rows, so I have to have a reasonable timeout. Problem I see is that once I've lost the connection, there's nothing I can do from VBA to fix it (gotta go to SSMS, find query and kill it). Any suggestions? – Greensboro

@Greensboro If you can, turn on READ COMMITTED SNAPSHOT. It will allow open transactions to not block other users (they'll read the snapshot of what the data was at the start of the transaction). ALTER DATABASE [Foo] SET READ_COMMITTED_SNAPSHOT ON – Isochor

Thanks Ian. We work with a lot of 3rd party vendor apps, so I've no idea what'll happen if I turn on read committed snapshot. Thanks anyway – Greensboro

@Greensboro We love READ_COMMITTED_SNAPSHOT here. It improves concurrency, and makes everything much better. – Isochor

Your pseudocode looks like some kind of application programming language, whereas I thought the try/catch which "overrides" xact_abort was the T-SQL variety. Perhaps your scenario happens because it's from a caller to sql server as opposed to t-sql running in the db engine? – Studdingsail

@Paul You are exactly right; it's from code. The point was that adding SET XACT_ABORT ON to existing code can turn it from functioning correct code into data corruption. – Isochor

What is the SQL that does catch duplicate key violation and react appropriately - is it another TRY CATCH block? – Medicinal

The entire root transaction will be rolled back sir. I don't understand what your problem is. In your first example, if perform insert fails, everything is rolled back. Your "catch duplicate key violation" does not even execute. – Quadrumanous

@Charlieface, yes it will, with SQL Server 2008+. Also, you need to use THROW instead of RAISERROR with SQL 2005+ to respect XABORT. RAISERROR does not trigger XABORT. – Quadrumanous

Would it have the same impact if you began the transaction inside the TRY? For example see this: https://mcmap.net/q/183791/-sql-server-set-xact_abort-on-vs-try-catch-block-inside-the-stored-procedure – Medicinal

@Medicinal It would not have any positive impact to move the call to .BeginTransaction inside the try. In fact, it can only cause more problems: if you move the .BeginTransaction inside the try, and there's an error calling .BeginTransaction, then the exception handler will be INTENTIONALLY trying to rollback a transaction that never started. The correct way to write a try-catch is to catch the code that you need to handle the error from. – Isochor

Answer 5

XACT_ABORT does indeed affect error handling: it will abort the entire batch when an error is encountered, and any code following the line that produced the error (including error checking!) will NEVER execute. There are two exceptions to this behavior: XACT_ABORT is superseded by TRY...CATCH (the CATCH block will always execute, and transactions will NOT be rolled back automatically, only rendered uncommitable), and XACT_ABORT will ignore RAISERROR.

Ancilla

Not completely correct: not all transactions will be doomed, some will still remain committable. Also CATCH block does not catch all errors. – Cassy

Answer 6

When XACT_ABORT set to OFF in trigger and I call RAISEERROR in trigger body, changes not rolled back.

Dharma

NB: RAISERROR (NB: one E) does not trigger rollback; instead you should use Throw. Ref: Note at the top of this link: msdn.microsoft.com/en-us/library/ms188792.aspx – Clubman