In Part 1 of this series I talked about transactions in general, I explored the three Transaction Modes that SQL Server supports, and nested transactions. In that article I stated "...just placing several SQL commands between a BEGIN TRAN and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction." In this installment we will look into just what I meant by that statement.
The lack of error handling is the primary reason that your transactions may not act the way you expect. The following example should illustrate the point:
CREATE TABLE State (State char(2) PRIMARY KEY) CREATE TABLE City (City varchar(30), State char(2) REFERENCES State(State)) --Populate a few states INSERT INTO State SELECT 'AK' INSERT INTO State SELECT 'CA' INSERT INTO State SELECT 'KS' INSERT INTO State SELECT 'UT' INSERT INTO State SELECT 'NY' BEGIN TRAN INSERT INTO City SELECT 'Anchorage', 'AK' INSERT INTO City SELECT 'Los Angles', 'CA' INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table INSERT INTO City SELECT 'Topeka', 'KS' COMMIT SELECT * FROM City --The transaction is partially completed SELECT @@TRANCOUNT --Returns 0 to show that the partial transaction is actually committed.
Even though we explicitly told SQL Server to treat all four INSERT's as a single transaction, it didn't. The reason is the error in the third INSERT. This example would work as a single transaction if the error encountered was fatal, like a deadlock, broken connection, hardware failure, etc... So, somewhat paradoxically, we have to worry more about minor (non-fatal) errors than we do about major errors, at least when it comes to creating robust transactions. SET XACT_ABORT is a command that at first glance fits the bill. Books Online states "When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back." Using the same example let's verify this:
TRUNCATE TABLE City --Reinitialize the table data SET XACT_ABORT ON BEGIN TRAN INSERT INTO City SELECT 'Anchorage', 'AK' INSERT INTO City SELECT 'Los Angles', 'CA' INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table INSERT INTO City SELECT 'Topeka', 'KS' COMMIT SELECT * FROM City --Now it works as it should! SELECT @@TRANCOUNT --Returns 0 again
Sure enough, since one statement failed, none of them are committed. But, can we leave the subject of error handling? Not quite! XACT_ABORT (XA for short) works fine for run-time errors in a single transaction, but there is a problem that might not be very obvious. In fact, Books Online is outright misleading in this regard since it talks primarily of transactions; the word "batch" is only mentioned only once in a somewhat offhand way. Despite what Books Online leads you to believe, XA works at the batch level, not the transaction level. It will rollback any open transactions but it also stops processing the batch at the point the error is encountered.
TRUNCATE TABLE City SET XACT_ABORT ON INSERT INTO City SELECT 'Anchorage', 'AK' INSERT INTO City SELECT 'Los Angles', 'CA' INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table INSERT INTO City SELECT 'Topeka', 'KS' SELECT * FROM City --Anchorage and Los Angles are there SELECT @@TRANCOUNT --No open transactions so there is nothing to ROLLBACK or COMMIT
This example uses Autocommit Mode so each statement is its own transaction. If XA was off, the third INSERT would fail and SQL would return an error, but it would continue processing the fourth statement. With XA on, the current transaction is rolled back, and the batch is aborted so the fourth statement is never executed. This rule applies no matter what Transaction Mode you are in.
TRUNCATE TABLE City SET XACT_ABORT ON BEGIN TRAN --Begin first transaction INSERT INTO City SELECT 'Anchorage', 'AK' INSERT INTO City SELECT 'Los Angles', 'CA' INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table. Nothing below this point is processed. INSERT INTO City SELECT 'Topeka', 'KS' COMMIT BEGIN TRAN --Begin second transaction INSERT INTO City SELECT 'New York', 'NY' COMMIT SELECT * FROM City --0 rows returned
SQL Server stopped processing the batch at the point of the first error. It should be noted that if the error had been in the second transaction, the first transaction would complete and commit successfully, only the second one would fail. Since the error occurs in the first transaction, neither one is committed, actually, at the risk of being redundant, the second transaction is never even executed, much less committed. If XA truly worked at the transaction level, the first transaction would be rolled back and the second one would be processed and committed. Hopefully it is now obvious that if you turn on XA, you also eliminate your ability to trap and respond to runtime errors. XA can be a useful shortcut, but you should be aware of its limitations.
A word about compile errors: Books Online states "Compile errors, such as syntax errors, are not affected by SET XACT_ABORT." The reason for this is that SQL Server employs a three step process when executing a batch. First the entire batch is parsed, or checked for syntactical correctness. Then the batch is compiled and an execution plan is created. Finally the compiled code is run. If the batch isn't able to be successfully parsed and compiled, no part of the batch is ever executed. Personally, I think compile errors is a bit of a misnomer here since if you have a syntax error, the batch never makes it to the compilation stage.
But certain run-time errors are handled in a rather inconvenient way. Unlike most run-time errors, problems with object name resolution are not handled by XA, or any other kind of error checking for that matter. Let's take a look at an example using the same table from Part 1 of this series:
CREATE TABLE TranTest ( Col1 int IDENTITY, Col2 int) --Populate some sample data DECLARE @count int SET @count = 0 WHILE @count <15 Begin INSERT TranTest (Col2) VALUES (0) SET @count = @count + 1 END SET XACT_ABORT ON BEGIN TRAN UPDATE TranTest SET Col2 = 2 WHERE Col1 = 2 INSERT INTO NoTable --This table does not exist SELECT TOP 1 * FROM TranTest COMMIT SELECT * FROM TranTest --XA does not have any effect, you can run the same statements with XA off and get the same result SELECT @@TRANCOUNT --Retruns 1...?!?!
Name resolution errors cause SQL Server to stop processing the batch at the point it hits the error, thus the COMMIT is never issued. This means that the transaction can still be rolled back; it also means that it can be committed in its partially completed state. The problem is that if you are not aware of this fact you are likely to do nothing, leaving the transaction open and the locks held. These errors are most likely to occur in Dynamic SQL or stored procedures where deferred name resolution would allow us to create the above query as a stored procedure without any indication of a problem. One interesting thing to note is that if you created a stored procedure like this and then executed it, you would get two errors. Error 208 indicates an invalid object, and error 266 indicates that we are missing a COMMIT or ROLLBACK and that the transaction has been left open. Why error 266 isn't returned if we just run the query ad hoc, I'm not sure...
Unfortunately name resolution errors aren't easy to deal with because like having XA on, SQL Server stops processing the batch as soon as it hits the error. This means that there is no practical way of dealing with these errors programmatically. The only option is to find and eliminate them in the testing process.
OK, on to error checking. While the current state of T-SQL error checking isn't optimal, it isn't all that bad either and besides, you need to do it if you want your SQL to be robust. Essentially it boils down to this: SQL Server has a built in @@ERROR function that returns the error code for the last SQL statement executed, or it returns 0 if the last statement was successful. Since the value of @@ERROR is reset after each statement (regardless of Transaction Mode or anything else) you must check it after every statement in your transactions. When trapping for errors there are many stylistic variations, but generally along two major themes. Which you use is purely a matter of style and familiarity. Let me demonstrate by revisiting our City table again:
TRUNCATE TABLE City BEGIN TRAN INSERT INTO City SELECT 'Anchorage', 'AK' IF @@ERROR <> 0 GOTO ErrBlock INSERT INTO City SELECT 'Los Angles', 'CA' IF @@ERROR <> 0 GOTO ErrBlock INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table IF @@ERROR <> 0 GOTO ErrBlock INSERT INTO City SELECT 'Topeka', 'KS' IF @@ERROR <> 0 GOTO ErrBlock COMMIT --We never get to this point unless all IF statements evaluate to false (the preceding INSERT is successful) RETURN --Ensures that SQL Server never executes the ErrBlock unless explicitly told to do so by one of our IF statements ErrBlock: ROLLBACK RETURN /*This is somewhat superfluous--like Krusty's third nipple--since it is the end of the batch anyway. I usually include it for clarity and consistency*/ SELECT * FROM City --The entire transaction was rolled back as expected SELECT @@TRANCOUNT --"nothing up my sleeve"
Essentially, the addition of this error handling code does the same thing as turning on XA. However, this kind of explicit error checking allows you much greater control over what happens in response to an error. It does tend to clutter things up a bit and makes for additional typing, but I've found that with this particular style of error handling (meaning the GOTO ErrBlock) it is pretty much a matter of cut and paste that takes a minimal amount of time and virtually eliminates typo's.
One of the alternative styles of error handling is like this example:
DECLARE @Err1 int, @Err2 int, @Err3 int, @Err4 int BEGIN TRAN INSERT INTO City SELECT 'Anchorage', 'AK' SET @Err1 = @@ERROR INSERT INTO City SELECT 'Los Angles', 'CA' SET @Err2 = @@ERROR INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table SET @Err3 = @@ERROR INSERT INTO City SELECT 'Topeka', 'KS' SET @Err4 = @@ERROR IF @Err1 = 0 AND @Err2 = 0 AND @Err3 = 0 AND @Err4 = 0 COMMIT ELSE ROLLBACK
These two examples both do the job, but I think the first one is easier. This method doesn't lend itself to cutting and pasting as well as the first, plus you have to declare variables which adds keystrokes and increases the likelihood of a typo. If there is a performance difference between the two I can't seem to detect it. Whichever style you like, and there are a number of different variations, just pick one that works for you and stick with it. Consistency is one of the great keys to good coding.
While we are all looking forward to SQL Server 2005 and its "TRY...CATCH" error handling, we have to deal with what we have. If you want your transactions, and by extension, your applications to be robust you cannot ignore errors. It isn't really that difficult and it can save you some pretty significant headaches down the road.
In the next article I'll tackle Transaction Isolation Levels; which I think is a big enough subject to rate its own article.
By Don Peterson Tuesday, October 05, 2004