Just to set things straight, the title of this post has nothing to do with US politics, but the infinitely more important (and exciting) subject of transactions in SQL Server and the concept of doomed transactions. Why I came across the idea for this post was due to a discussion I had with a colleague of mine - Sameer Chunilall - who didn’t agree with my post a while ago about
XACT_ABORT, he believes it is a good thing (mostly), where I believe it is a bad thing (mostly).
argued discussed at length, and then decided that we both were right. Or rather I decided I was right, and he decided he was right :). Jokes aside, during the discussions the concept of doomed transactions were brought up, and that led me to writing this blog post.
Before we go any further, I have written a couple (three actually) posts that deal with transactions and/or error-handling in SQL Server, so if you want to refresh your memory they can be found here (in chronological order):
- Transactions in SQL Server (take 2956)
- SQL Server Error Handling Gotchas
- Abort, Abort, We Are XACT_ABORT:ing, Or Are We?!
Before we start talking about doomed transactions, let’s look at something that has a definitive impact if a transaction will be doomed or not.
NOTE: There is no special demo code for this blog post, if you want you can download the code from the
XACT_ABORTblog post, and edit according to the code snippets here.
Statement, Scope and Batch Termination
In SQL Server you execute statements and batches. A statement is what it says it is, a single T-SQL statement, like:
Code Snippet 1: T-SQL Statement
A batch is what is executed at a point in time from an application (SSMS, ADO.NET, etc.):
Code Snippet 2: T-SQL Batch
GOstatement in Code Snippet 2, is an SSMS “thing”. When executing code from a script, the
GOstatement denotes batches.
When executing a stored procedure, that is also a batch. If a procedure calls other procedures, they are all executing inside the same batch. The code below shows 4 procedures we will use going forward:
Code Snippet 3: Call Chain of Procedures
As we can see,
dbo.pr_Caller starts a transaction, calls
dbo.pr_1, which in turn calls
dbo.pr_2, which finally calls
dbo.pr_3. When all is “said and done”,
dbo.pr_Caller commits the transaction. The procedure
dbo.pr_2 is the “dodgy” procedure in this call chain, and we will use it to simulate some error conditions that will result in various termination types. As you can see in Code Snippet 3,
dbo.pr_2 has some commented out code, and it is this code that will cause terminations. Initially we’ll execute the code as is, and this should not cause any errors:
Code Snippet 4: Batch Executions
NOTE: The code above starts with
TRUNCATE TABLEwe do that just to make sure everything is cleaned up before execution. Oh, and BTW - how many batches are executed above? Answer in comments or email.
When executing the above you should see three rows coming back from the
SELECT statement, all is good:
Figure 1: Procedure Execution No Errors
So what happens now if we were to
ALTER proc 2 a bit, comment out the insert for
OrderID 2, and uncomment the insert for
OrderID 5. There is no order with that id, so it should result in a foreign key constraint error. The proc should look like so:
Code Snippet 5: Proc Which Will Cause FK Violation
ALTER:ing the proc and executing as in Code Snippet 4, we get something like:
Figure 2: FK Error After Procedure Execution
So we got a FK violation error, but we can also see that we continued the execution in
dbo.pr_2, and called
dbo.pr_3. Furthermore, the result gives us two rows back, from
Figure 3: Result After FK Error
What we just have seen is statement termination. The statement causing an error stopped executing, but we continued executing - even within the same proc - straight after that statement. Furthermore, the error had no negative impact on the transaction. This may or may not be what you expect and want.
I guess that most of you who read this have heard about statement termination before, so what we just have done should not come as any surprise.
Some of the errors that can cause statement termination are:
- Most of
- Errors raised by the user
- Quite a few more :)
Scope Termination is probably not as well known as statement termination. A scope termination is when SQL Server terminates the statement that caused the exception and subsequent execution within the same scope. In the examples we are using, scope would be an individual procedure. So, let us have a look at an scope termination example, let us
ALTER our “naughty” procedure
dbo.pr_2 and comment out the statement which caused the FK violation, and un-comment where the procedure does a
SELECT from the non-existent table
Code Snippet 6: SELECT from Non-Existent Table
When you execute as in Code Snippet 4, the outcome is:
Figure 4: Scope Abort Error
As expected an error happened, but compared to the statement termination example,
dbo.pr_2 did not continue executing (we did not see
dbo.pr_2: Before EXEC dbo.pr_3). The statement that caused the error was terminated AND all subsequent statements within that scope (procedure). However, the batch continues and
dbo.pr_Caller committed the transaction. When you look at the Results tab in SSMS you will see one row returning from the
SELECT statement; the insert done by
So, what causes scope termination? I have tried to look into that, but so far the only error type I have found termination the scope is when you try to access a missing object. If you know more types, please email me, and I can update this post.
NOTE: Neither statement termination, nor scope termination have any effect on a transaction.
Error Handling and Scope Termination
An interesting aspect of scope termination is how it is being handled (or not) by error handling. From my previous posts about errors, (gotchas, and XACT_ABORT), we see how either
IF(@@ERROR <> 0 ), or
TRY ... CATCH catch exceptions. How about scope termination errors? You would expect them to be handled the same way as statement termination errors, as they do have the same severity (16), as can be seen from Figure 3, and Figure 4. In fact that is not the case! It turns out that an
IF(@@ERROR <> 0) will not catch the error, whereas a
TRY ... CATCH block will!
After having covered statement termination and scope termination above, I do think that it is pretty clear what batch termination is. Some code to show this; once again in
dbo.pr_2, comment out the
SELECT statement against the no existing table and un-comment the part where we try to do a
Code Snippet 7: Proc Which Will Cause CAST Conversion Error
When executing this (once again a in Code Snippet 4), this is what we get:
Figure 5: Batch Termination Error
When looking at the output as per in Figure 5, we can see that no more execution happened in
dbo.pr_2, as was also the case in scope-termination and Figure 4, BUT we do not see any further execution in
dbo.pr_Caller either! The batch completely terminated. But wait a second; what about the transaction that was originally started in
dbo.pr_Caller? That transaction has been rolled back automatically, when we exited the batch (more about that later).
Errors that can cause batch termination are:
- Conversion errors
Error Handling and Batch Termination
As with scope termination,
IF(@@ERROR <> 0) will not catch the error that caused the batch termination, but
TRY ... CATCH will. So, let’s see what happens when we introduce
TRY ... CATCH in
Code Snippet 8: TRY … CATCH and Batch Termination
dbo.pr_Caller to have a
TRY ... CATCH, but we do not do anything other than a
TRY ... CATCH, the transaction was automatically rolled back. It is a little bit different now when we execute it as in Code Snippet 4:
Figure 5: Uncommittable Transaction
What happened here? All of a sudden we get quite a few error messages, and what is this about Uncommittable transaction at the very end? The short story is that as soon as you introduce
TRY ... CATCH and you have active transactions - you need to decide what to do with that transaction.
OK makes sense. In this case, I don’t really care what went wrong, and I want to commit the transaction anyway. I conveniently don’t pay any attention to the part of uncommittable in the error message. After all - this is what I can do if I get a statement termination or scope termination, so why not do it here too! I
ALTER the procedure to do
COMMIT TRAN in the
CATCH block straight after the
Figure 6: Current Transaction Cannot be Committed
I am in trouble now. This time I am really told that I am doing something wrong: “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction”. What is this?
Doomed (a.k.a. “Oh, Oh, I am so up the cr33k”)
The errors we see in both Figure 5 as well as in Figure 6 are due to that SQL Server decides - for one reason or another - that it cannot commit the transaction. The transaction is doomed, and the only thing that can be done is to roll it back. It is not straight forward when an error causes a doomed transaction as the way SQL Server handles errors are case by case. Rule of thumb though is that in most cases a doomed transaction is due to some error that has caused a batch termination.
NOTE: To further drive home that SQL handles errors on a case by case basis, all errors we have seen so far (caused by statement, scope and batch terminations), have had a severity of 16.
Just to prove that we can recover from a batch termination without errors, we
dbo.pr_Caller procedure, replace the
COMMIT TRAN with
ROLLBACK TRAN, and execute. The outcome will be completely different:
Figure 6: Rollback Doomed Transaction
As Figure 6 shows, no “nasty” errors - all is OK! So what do we do if we sometimes want to commit the transaction if it is not doomed even if there has been an error, or rather - how can we figure out whether a transaction can be committed or not?
The way to determine if a transaction can be committed or not is to use
XACT_STATE() is a scalar function and it reports on the state of the current transaction. There are 3 possible return values:
- -1: a user transaction is active, but it is doomed, and it can only be rolled back
- 0: no user transaction is active
- 1: a user transaction is active, it can be committed or rolled back.
To see an example of this
CATCH block to look something like Code Snippet 9 below and then execute.
Code Snippet 9: Using XACT_STATE()
After execution the output would be like so:
Figure 7: Rollback Doomed Transaction
As we see from Figure 7, we ended up in the
XACT_STATE was -1, and the transaction was rolled back. You can if you want
ALTER the “naughty” proc
dbo.pr_2, and comment out the statement causing the batch termination and un-comment the statement which creates the statement termination. When executing, you should now get an output like so:
Figure 8: Rollback Doomed Transaction
Here we had decided that even if an exception was raised, we wanted to commit the transaction, unless it was doomed.
XACT_STATEcan be used outside
TRY ... CATCH, so you can use it even with “old style” error handling.
I started this blog-post by talking about
XACT_ABORT, and how I discussed with Sameer if it was good or bad. We have now come full circle and we are back to
XACT_ABORT. You are probably asking yourself how that fits into here?
The answer to that is that if you switch on
XACT_ABORT, even statement termination errors will cause the transaction to be doomed! To test this,
ALTER the calling proc
dbo.pr_Caller and insert a
SET XACT ABORT ON at the beginning of the proc, underneath the
SET NOCOUNT ON. The first few lines of the proc should look like so:
Code Snippet 10: Setting XACT_ABORT ON
If you haven’t already
dbo.pr_2 to cause a foreign key exception (statement termination and not a “doomable” error), do so - as discussed between Figure 7 and Figure 8. When you now execute you will get the same output as in Figure 7.
Finally, it is quite common to hear that triggers can be an issue, as they are rolling back transactions if an error happens in the trigger. The reason for this is that when a trigger executes, it automatically sets
I am not completely certain of the reason for this, but I guess it is a good “defensive” measure. So, if you don’t want this behavior, you can do a
SET XACT_ABORT OFF in your triggers.
Transactions are doomed:
- When an exception happens which causes a batch termination
- For any exception when
- When SQL Server feels like it. :)
Exception handling in SQL Server is NOT straight forward, especially when mixed with transactions!
comments powered by Disqus