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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.