Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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</pre>
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.

Comments

Posted by Steve Jones on 18 May 2011

Very nice. I was unaware of this, but it's something I'll have to check on myself and make sure I handle errors in code after each statement.

Posted by brijeshs on 19 May 2011

Nice and learning article

Posted by shekhar-922148 on 19 May 2011

Good knowledge for us. Thank you.

Posted by kurle.nitin10 on 19 May 2011

Hey your article is good one...

but as per my reading...

I think XACT_STATE() returns 3 values...

1st is "1" The current request has an active user transaction.

2nd is "0" There is no active user transaction for the current request.

and 3rd is "-1" The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

Posted by Nils Gustav Str&amp;#229;b&amp;#248; on 19 May 2011

Also worth mentioning that SQL Server "Denali" introduces the THROW statement, so you can throw the original exception to the caller without having to use the ERROR_MESSAGE(), ERROR_NUMBER() and so on.

Posted by andy russell on 19 May 2011

Great article

Posted by Christopher G.S. Johnson on 19 May 2011

Thanks for the post, Gail.

One question.  I've seen different implementations.  Some people put the BEGIN TRAN after the BEGIN TRY, but others, like you, put it before.  Is there a particular reason that you placed those statements in that order?

Thanks again...Chris

Posted by Yuri on 19 May 2011

Thanks, Gail- very usefull info. I thought about it (failure of single statement in SP for example) but for some reasons (shame on me) did not test it myself.

Posted by tfifield on 19 May 2011

Very nice article Gail. Simple and useful.

Posted by Thiago Dantas on 19 May 2011

@Chris

I think it makes no difference whatsoever if you put the begin tran after or before the begin try

Good read, thanks Gail

Posted by GilaMonster on 19 May 2011

Nils, I can never remember what is NDA and what isn't around Denali, so it's easier to say nothing.

Chris: No difference. Begin tran is not a statement that should be able to fail (anyone?), but you can reverse the order if you like.

Posted by sam.mesh on 20 May 2011

It is worth to mention that assigning not-numeric char to numeric leads to uncommittable transaction. So, there is no generic way to write isAssignable as try a=b; return true; catch return false; end;

Posted by David Walker on 20 May 2011

sam.mesh: There's not a generic "IsAssignable", but there's IsNumeric() and IsDate().

Posted by Rich Mechaber on 20 May 2011

Thanks for the tip about this potential gotcha in rollbacks.

One item maybe worth mentioning: catching and reporting errors using the built-in error SQL functions in the CATCH block only reports the first-encountered error, which is how the TRY...CATCH construct is intended to work.

Posted by GilaMonster on 20 May 2011

David, IsNumeric is unfortunately a bit of a misnomer, perhaps more correctly called CanBeConvertedToOneNumericTypeNotGoingToSayWhichOne :-)

Leave a Comment

Please register or log in to leave a comment.