Abort, Abort, We Are XACT_ABORT:ing, Or Are We?!
SET XACT_ABORT defines whether a transaction is automatically rolled back when a T-SQL statement raises a run-time exception, and when you read posts from prominent SQL bloggers you quite often see that they recommend to always have
XACT_ABORT set to
ON. From the title of this post you may get the impression that I do not necessarily agree, and to an extent you may be right. So, let us see ...
Derivco's main OLTP production database has around 5,000 stored procedures, where a small procedure has about 600 - 800 LOC, and a big procedure can have 3,000 - 4,000 LOC. The procedures are also quite heavily nested, where it is not uncommon to have a call chain of 10 - 15 procedures. It is not only one team working on the procedures, but multiple teams are maintaining and developing procedures.
So, back to the issue at hand, and let's begin looking at why we have
XACT_ABORT in the first place? That has to do with transactions and SQL exceptions; remember back before SQL Server 2005 and
CATCH, an exception did not normally stop execution of a batch, even though the execution of the statement stopped. Let us look at some code for this, and let's start with creating a couple of tables with a foreign key constraint between them:
Note: Most of the code in this post can be downloaded from here
Code Snippet 1: Setup Code
As you see, we are creating a couple of tables, and then inserts some data into the primary table
dbo.tb_Order. Now, if we write some code inserting data into
dbo.tb_OrderDetail, inside a transaction, and we cause an exception to happen - what will the result be:
Code Snippet 2: Error
From the code we see how we:
- start a transaction
- insert for
- try to insert for
- this causes a foreign key exception
- insert for
- commit the transaction
SELECTfrom the table
The result from executing the code is that we get an foreign key exception raised, and the
INSERT statement terminated as in Figure 1:
Figure 1: Foreign Key Exception
However, when looking at the result from the
SELECT statement, we see how the first and third
Figure 2: SELECT After Exception
So, as mentioned before, the exception did not affect the transaction, and anything after the exception executed as nothing had happened, plus the transaction was committed. This may not be the behavior you really wanted, e.g. you expected the transaction to roll back when an exception happened.
If that's the behavior you wanted, there are a couple (actually 3) of ways to achieve it:
INSERT, and then
- If you are in an environment using SQL Server 2005+, catch the exception with
BEGIN TRY ... END TRYand
BEGIN CATCH ... END CATCH, and do a
XACT_ABORT we can ensure that the executing batch is terminated as well as any transaction being rolled back if an exception is raised, by setting
SET XACT_ABORT ON, and then executing your code:
Code Snippet 3: Executing with XACT_ABORT ON
The code looks almost like in Code Snippet 2, with the addition that we switch on
XACT_ABORT in the beginning of the batch, oh and yes - we are also cleaning up the
dbo.tb_OrderDetail table with a
TRUNCATE TABLE command. When executing the code you almost get the same output as from Code Snippet 2, except for the fact that the output message does not say anything about statement termination:
Figure 3: Output Message after Executing with XACT_ABORT ON
Also, there is no Result tab in the output, which indicates that the
SELECT statement at the end of the batch did not execute, e.g. the exception caused a batch termination, due to
ON. So what about the transaction, remember that before we switched
ON, the first and second
INSERT statement succeeded. We can safely assume that in this example the third
INSERT did not succeed, as the batch was terminated, but what about the first? Well, let's see; go ahead and execute the
SELECT * FROM dbo.tb_OrderDetail and see what the result is. You should get something like in Figure 4:
Figure 4: Result After XACT_ABORT
No rows coming back,
XACT_ABORT rolled back the transaction as well as terminating the batch! That is fairly cool! What about something - somewhat (not much) - more realistic than just a batch execution; like
XACT_ABORT and stored procedures. Below in Code Snippet 4, is code to create three stored procedures. The top level procedure (
XACT_ABORT and then goes on to start a transaction, do an insert and call
dbo.pr_2, which in turns calls
dbo.pr_3. The last procedure -
dbo.pr_3 - generates a foreign key exception:
NOTE: The transaction handling in all the procs, in all examples, is very much simplified, whereby the procs being called by the top-level proc is not doing anything with transactions, as the transaction should only be committed/rolled back by the proc that started the transaction. See my blog post from a couple of years ago about proper transaction handling.
Code Snippet 4: Procedures with XACT_ABORT
If you are copying and pasting the code above, make sure you create the procs in opposite order to what is in the Code Snippet.
Let's see what happens when we execute
Code Snippet 5: Execution of the Procedures
The result is exactly the same as when we executed the code in Code Snippet 3. So, even when we execute multiple procedures under the same SPID, the
XACT_ABORT ensures that the batch (call chain) is terminated and the transaction rolls back!
How cool is that, what is there not to like about automatic transaction rollback when an exception happens!
What Could Possibly Go Wrong?!
Right let's have a look at a couple of scenarios where
XACT_ABORT may not be the answer to your prayers.
We'll start where we are in a situation where we still are doing SQL Server error-handling the old way, by checking
@@ERROR after execution of statements. This could be a scenario where we have quite a few old procedures, which have not been update to
TRY ... CATCH yet. The procedures look like so:
Code Snippet 6: Procedures with Old Style Error Handling
They do not look much different than the procedures in my blog post a while ago about "gotcha's" in error handling. The procedures are being "good citizens", and check for errors after executing something that could go wrong, and if there is an error, they re-wind, clean up, and raise the exception up the call-chain. When executing
dbo.pr_1, as in Code Snippet 5, you would see something like so:
Figure 5: Executing Procs Old Style Error Handling
We see how the exception happens and then how each proc is handling the exception, cleaning up, and re-raising. When
dbo.pr_1 receives the error, it also rolls back the transaction. If everythig had executed successfully, the
dbo.pr_1 proc would have gone on executing code after the error-handling block. In this case we can see it did not do that. Then, when executing the
SELECT statement, no results are coming back - as everything has been rolled back.
What would happen if the
dbo.pr_1 proc were to be modified to use
XACT_ABORT? Let's say a developer has heard about
XACT_ABORT, and think it sounds cool, so while he is doing other changes to the proc, he also changes it to use
Code Snippet 7: dbo.pr_1 Using XACT_ABORT
Not much have changed, the developer:
SET XACT_ABORT ONbefore the
- took out the
ROLLBACKin the error-handling block (as
- we still need to do cleanup in there though
What is the result now when executing
Figure 6: Executing Procs Using XACT_ABORT
Whoa, no clean-up, rewinds, anything! I guess that should be expected seeing that
XACT_ABORT terminates the batch, and rolls back the transaction. However this is one of the reasons I do not like
XACT_ABORT: you have no control over what happens when an error occur!
So far the errors we have seen are errors from T-SQL statements, what if we were to raise an exception through
RAISERROR? The answer to that is that
RAISERROR will not cause
XACT_ABORT to trigger! This means we can be in a very messed up state transaction wise. So if we use
XACT_ABORT we need to be very careful how we handle exceptions, and we cannot solely rely on it to automatically do a
XACT_ABORTto work as intended, however that would require SQL Server 2012, and
THROWin itself adds its own issues. See my blog post for more around that.
TRY ... CATCH
What happens if we are in a
TRY ... CATCH situation; e.g. using somewhere in the call-chain the exception handling capabilities introduced in SQL Server 2005?
See my blog post about what issues you can run into with mixing and matching error-handling styles.
So let us edit
dbo.pr_3 to do "new" error-handling, and let the other procs stay the same:
Code Snippet 8: TRY ... CATCH in dbo.pr_3
Here it is the last proc in the call-chain that are using
TRY ... CATCH, and as it is doing proper exception handling it knows that it did not start the transaction, so in the error handling code it just raises the error. The result is the following:
Figure 7: XACT_ABORT and TRY ... CATCH
Ooops, we really are in a messed up state. I guess that is to be expected seeing what we discovered above regarding
RAISERROR. Once again, we need to be very careful what we do when we use
XACT_ABORT. Oh, and what would the result be if we only changed
dbo.pr_1 to use
TRY ... CATCH, e.g roll back the change in
dbo.pr_3, and add the "new" style exception handling in
Code Snippet 8: TRY ... CATCH in dbo.pr_1
Notice that we in the
CATCH block we are not doing rolling back the transaction as we rely on
XACT_ABORT to handle that. When executing the result is:
Figure 8: Top Level TRY ... CATCH and XACT_ABORT
From the result we see how we immediately ended up in the
TRY ... CATCH block in
dbo.pr_1! This indicates that
TRY ... CATCH overrides
XACT_ABORT! In reality there is no use having
TRY ... CATCH in the same proc!
In fact, I would argue that when we have
TRY ... CATCH we don't need
XACT_ABORT, as we can decide what to do with the transaction in the
We have now seen quite a few examples where
XACT_ABORT may not be ideal. My biggest "gripe" with
XACT_ABORT is not any of those...
All Errors are Equal, Some are More Equal then Others
I am paraphrasing George Orwell above, and that phrase summarizes why I do not like
XACT_ABORT. Let me explain...
If we go back to what the code looked like at Code Snippet 6. We had three procs which did proper error handling and all was good. Let us assume these procs were some procs for a financial institution where they were called for deposits. We then realized that we needed some code that did something that were not vital for the actual deposit process, but we still needed it to execute together with the other procs (this may be logging, etc). So we introduce a new proc (call it
dbo.pr_Logging) into the call chain, and in that proc we make sure we handle any errors, because we do not want to affect the deposit process. We handle the errors in the "old" way, as we do not want to mix "old" and "new". All is well and good!
However, we now come to the same scenario we saw in Code Snippet 7; the developer who had heard about
XACT_ABORT. So the developer introduces the
XACT_ABORT as in Code Snippet 7.
What happens now if an exception happens in
dbo.pr_Logging? The batch is terminated and the transaction is rolled back. So a non-vital error is now causing the transaction (the deposit) to fail! Ouch!!!!
I am against
- You are losing control of what to do when an exception happens
- It does not play well with
TRY ... CATCH
- Non-vital exceptions causes the whole transaction to roll back!
Share this Post:Twitter | Google+ | LinkedIn