On Transactions, errors and rollbacks

Do errors encountered within a transaction result in a rollback?

It seems, at first, to be a simple question with an obvious answer. Transactions are supposed to be atomic, either the entire transaction completes or none of it completes.

Maybe too simple…

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO
BEGIN TRANSACTION
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
COMMIT TRANSACTION
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

If a transaction rolled back at the first failure, that final select would return no rows. But it doesn’t, it returns 3 rows. The failure of the individual statements was ignored and the transaction completed and committed. If that had been an important business process, not a made-up example, that could have some nasty consequences for transactional consistency of data.

What’s really going on here? Aren’t transactions supposed to be atomic? Isn’t SQL supposed to roll them back if they don’t complete successfully?

Well, kinda.

Books Online states

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

That suggests that indeed the transaction should roll back automatically, however it also states

If the client’s network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error.

The default behaviour is to roll back only the statement that generated the error. Not the entire transaction.

A transaction will be rolled back if the connection closes (network error, client disconnect, high-severity error) and the commit was not reached. A transaction will be rolled back if the SQL Server terminates (shutdown, power failure, unexpected termination) and the commit was not reached. Under default settings, a non-fatal error thrown by a statement within a transaction will not automatically cause a rollback. (fatal = severity 19 and above)

So what can we do if we do want a transaction to completely roll back if any error is encountered during the execution?

There are two option.
1) Use the Xact_Abort setting
2) Catch and handle the error, and specify a rollback within the error handling

Xact_Abort

From Books Online:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Sounds simple enough. Let’s try the example from above with Xact_Abort on.

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO
SET XACT_ABORT ON
GO

BEGIN TRANSACTION
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
COMMIT TRANSACTION
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

Now the first of the run-time errors results in the entire transaction rolling back.

This is great if all you want is the transaction rolled back if an error occurs and aren’t interested in any additional error handling or logging.

Error Handling

Error handling used to be an absolute pain in SQL 2000. With no automatic error trapping in that version, error handling was limited to checking the value of @@error after each statement and using GOTO.

Fortunately in newer versions of SQL, there’s the TRY … CATCH construct. Not quite as fully-functional as the form that many front-end languages have (no finally block, no ability to catch specific classes of exceptions and ignore others) but still far, far better than what we had before.

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL
 PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

The first exception transfers execution into the Catch block, the transaction is then rolled back and when the select runs there’s 0 rows in the table.

This looks like it does the same as XactAbort, just with far more typing, but there are advantages to handling the errors rather than just letting SQL roll the transaction back automatically. The catch block is not limited to just rolling back the transaction, it can log to error tables (after the rollback, so that the logging is not rolled back), it can take compensating actions, and it’s not even required to roll the transaction back (in most cases).

One of the reasons for using a catch block is that there are a number of error-related functions that only return data when they are called from within a catch block. These functions make it possible to create a friendly error and raise that (using raiserror) so that the client application doesn’t get the default SQL error messages. It’s also possible to check what error was thrown and behave differently for different errors (though not as easily as in applications like C# which allow catching of exception classes)

CREATE TABLE TestingTransactionRollbacks (
<pre> ID INT NOT NULL
 PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT  ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure
END CATCH
GO
EXEC InsertWithError

GO
DROP TABLE TestingTransactionRollbacks
DROP PROCEDURE InsertWithError

With those functions, the exact error text can be logged to a table for further analysis, along with the line and the procedure that the error occurred in, and then a friendly error can be sent back to the user.

Just one thing, of course, if using a logging table the insert should be done after the transaction rollback, or temporarily inserted into a table variable so as to not be affected by the rollback.

One last thing that does need mentioning is the concept of a doomed transaction. This is a transaction that, once execution is transferred to the catch block, must be rolled back. The easiest way to see this in action is to combine XactAbort and a Try-Catch block

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

SET XACT_ABORT ON ;

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 COMMIT TRANSACTION
END CATCH
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

In this case I’m ignoring the error and committing anyway. Probably not something that will be done often in real systems, but for just demonstration purposes it’ll serve.

Running this however returns another error (one thrown in the catch block)

Msg 3930, Level 16, State 1, Line 24
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

So how do you check for this? The built-in function XactState will tell us the state of the transaction. A value of 1 means that the transaction can be committed, a value of -1 means that the transaction is doomed and can only be rolled back.

Replacing the catch block with the following allows the code to run without error

BEGIN CATCH
  IF XACT_STATE() = 1
    COMMIT TRANSACTION
  IF XACT_STATE() = -1
    ROLLBACK TRANSACTION
END CATCH

Now this is only half the story, as I haven’t touched on nested transactions at all. That’s an entire post of its own though.

In conclusion, while SQL does no provide the rich exception handling of front end applications, what it does provide is adequate for good error handling, especially in conjunction with transactions that must commit or roll back as atomic units.

All the error handling in the world however will not help if is not used, and leaving it out and just hoping the code will run correctly every time is never a good development practice.

16 Responses to “On Transactions, errors and rollbacks”

  1. This is something that I have often witnessed when ad-hoc statements are run against a database and haven’t quite been able to explain. Thanks for the explanation which you backed up very well with your example code.

  2. Very nice post Gail. Clearly explained. Thanks!

  3. Good article explained very well in detail. Thanks!

  4. [...] On Transactions, errors and rollbacks – A very interesting look at error handling and transaction management from Gail Shaw (Blog|Twitter) this week. I’ll be very surprised if you do not learn something from this insightful post. [...]

  5. Nice post! One tiny glitch, in the source where you introduce friendly SQL errors, there are some tags that are showing up and messing up the syntax.

  6. Best article I’ve read on this issue. Thanks. Wondering though, why would you add a COMMIT command in the catch block?

  7. You probably wouldn’t in most cases. I put that more as an example of what can’t be done

  8. Thanks. Reading my post again I think I was unclear because you made a point of saying that your wouldn’t do that as a rule. My question related to the final catch block illustration where you preceded COMMIT with IF XACT_STATE() = 1.

    I’m working with multiple batches within the transaction and wonder whether the IF XACT_STATE() [=-1| =1] code should be in every catch block or whether it could be placed at the end of the sproc outside the final catch block.

  9. Only if you want to potentially commit the transaction after an error has occurred.

    If you are always rolling back when an error occurs, it doesn’t matter what state the transaction is (at most you’d check to see if there is an open transaction)

    If you want to catch an error, do something then carry on with more operations in that transaction and potentially commit it, then you do need to check and make sure the transaction isn’t doomed.

  10. Gail I really appreciate your comments and suggestions. And I admire you for taking the time to not only write the blog but also respond so quickly to questions like mine.

  11. Great post. Thanks for the insight.

  12. Really excellent blog Gail!! I hadn’t done SQL transactions for a few years and was struggling to remember all the exception stuff and this was absolutely the best information I found anywhere on it.
    One question – did you ever write a blog regarding nested transactions? I ask because I am writing a new script (that must handles tranaactions) which is turn calls an established production sproc (which I can’t change) which has it’s own TRANSACTION – if that failed should the parent one fail as well? In other words are failed transactions thrown back up the chain?

  13. It’s still in the list of to-be-written. Soon(tm)

    One rollback anywhere in the nested transactions and the whole lot roll back right the way to the first begin tram, so you don’t really have a choice in the matter.

    Nested transaction sre a lie, they don’t actually exist.

  14. Good article, thank you.
    In the case where you have several DML sentences in the TRY block, how can you identify the exact sentence wich produced the error from inside the CATCH block?, or how you would manage that situation?

  15. You can get the line which the error occurred on (with the ERROR_LINE() function) which will pretty much tell you which statement it was

  16. […] was from Did you Know? Nesting Transactions by Kalen Delaney. And more from SQL in the Wild by Gail Shaw. This entry was posted in SQL on May 11, 2010 by […]

Leave a Reply