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.