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. Show
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
That suggests that indeed the transaction should roll back automatically, however it also states
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. Xact_AbortFrom Books Online:
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 HandlingError 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)
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 CATCHNow 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. Share this:
← Previous post Next post → 19 Comments
|
