[ACCEPTED]-Do I really need to use "SET XACT_ABORT ON"?-transactions

Accepted answer
Score: 37

Remember that there are errors that TRY-CATCH 13 will not capture with or without XACT_ABORT.

However, SET XACT_ABORT ON does 12 not affect trapping of errors. It does guarantee 11 that any transaction is rolled back / doomed 10 though. When "OFF", then you still 9 have the choice of commit or rollback (subject 8 to xact_state). This is the main change 7 of behaviour for SQL 2005 for XACT_ABORT

What it also 6 does is remove locks etc if the client command 5 timeout kicks in and the client sends the 4 "abort" directive. Without SET XACT_ABORT, locks 3 can remain if the connection remains open. My 2 colleague (an MVP) and I tested this thoroughly 1 at the start of the year.

Score: 4

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

From the books on line: XACT_ABORT 7 must be set ON for data modification statements 6 in an implicit or explicit transaction against 5 most OLE DB providers, including SQL Server. The 4 only case where this option is not required 3 is if the provider supports nested transactions. For 2 more information, see Distributed Queries 1 and Distributed Transactions.

Score: 2

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

Score: 2

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

i read a convincing 31 argument on StackOverflow that suggested 30 that you should always use XACT_ABORT ON. i changed the system 29 to set that option during connect. Except 28 it lead to data corruption and a lot of 27 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 26 in a transaction. Because even though you 25 caught the duplicate key violation, the server is no longer in 24 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 23 SET XACT_ABORT ON.


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

Let's use some pseudo-code, with 18 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 17 is a pedantic way of making this answer 16 more readable; we use x to represent eXecution 15 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 14 there is an error that we expect, we handle it 13 and continue. This is called handling the error. If some unknown exception 12 happens (something we did not expect), we 11 rollback any transaction that may be in progress.

Now 10 lets see if we blindly follow the suggestion 9 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 8 written to handle error cases. But the introduction 7 of XACT_ABORT ON to the connection will now cause database 6 corruption. For those of you who don't see 5 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 4 that was written correctly, and works, becomes 3 broken, causes errors, and at worst causes 2 database corruption. All because i turned 1 on XACT_ABORT ON.

Score: 1

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

Score: 0

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

More Related questions