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.

XACT_ABORT

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.

Compilation Errors

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.

Error Handling

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