Everyone knows that a transaction is defined as a "Unit of work." The transaction completes only if all the individual steps are successful, otherwise it all rolls back as if nothing ever happened, right? That simple definition belies some devilishly complex behavior on the part of SQL Server. Odds are that if you are a DBA or developer SQL Server's transaction handling behavior has left you scratching your head and muttering "What in the world...?" at some point in your career. I was recently confronted by an angry mob of developers (OK, it was only three...and they were more confused than angry) who were baffled at SQL Server's behavior regarding transaction processing and locking. I was able to explain what was happening and fix their problem, but over the years I have come to realize that there is widespread confusion on the subject. I suspect that the details of transaction handling go largely ignored because they CAN be; in most circumstances the default behavior works well--or well enough. My objective is to pull the lid off the transaction "black box" giving you the tools you need to understand what's going on under the covers. When you understand the details you can make SQL Server do your bidding instead of crossing your fingers and hoping for the best.

It has become evident that many of us don't really understand how to put the power of transactions to work. For instance, just placing several SQL commands between a BEGIN TRAN<ACTION> and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction. To get consistent, reliable results you need to understand Transaction Modes, Transaction Isolation Levels, locking, nesting, error handling, Savepoints, XACT_ABORT, bound connections, and just for good measure you need to understand how ANSI_DEFAULTS and your choice of provider affect transaction behavior.

Due to the large number of variables to be considered, I will treat this subject in a series of articles; this will allow me to cover each topic in some depth. I'll state right upfront that the vast majority of this information is available in Books Online. It just tends to be scattered across many different pages, and frankly Books Online, as good a resource as it is, can sometimes be downright misleading. So I'm trying to pull it all together for easier consumption and provide some easy to follow examples. Enough introduction, let's dig in...

Transaction Modes

SQL Server supports three Transaction Modes: Autocommit, Explicit, and Implicit.

• Autocommit Mode is the default behavior for SQL Server. In this mode each SQL statement that is issued acts as a single transaction and commits on completion so there is no need--or even an opportunity--to issue a COMMIT or ROLLBACK. Autocommit is the default behavior for ADO, OLE DB, ODBC, or DB-Library connections.
• Explicit Mode is entered whenever you issue a BEGIN TRAN command and ends when you COMMIT the top level transaction (see below for a discussion of Nested Transactions) or issue a ROLLBACK. There is no configuration setting that turns Explicit Mode on and off. Once your explicit transaction is committed or rolled back SQL Server immediately returns to the transaction mode it was in before.
• Implicit Mode is a bit trickier for most of us to grasp. By default IMPLICIT_TRANSACTIONS is off (in other words Autocommit Mode). If you issue the SET IMPLICIT_TRANSACTIONS ON command a transaction is started for each SQL statement listed in the table below, unless there is already an open transaction, but is not committed on completion. Subsequent commands join the transaction until you issue a COMMIT or ROLLBACK. SET ANSI_DEFAULTS ON puts SQL Server into Implicit Mode, it also turns on several other options that I won't deal with here, but are documented in Books Online.

The following SQL statements start transactions.

*Notice that SELECT, OPEN, and FETCH start transactions but COMMIT and ROLLBACK make only limited sense for them because they don't modify data. Nevertheless the transaction is held open until explicitly ended, depending on your Transaction Isolation Level, or Lock Hints used in the query this can have a major impact on concurrency if you're not careful.

There is a widely circulated myth that CREATE, ALTER, DROP, and TRUNCATE statements can't be rolled back, they can be if you are in Implicit or Explicit Mode. In Autocommit Mode nothing can be rolled back since any SQL statement automatically commits upon completion.

A demonstration seems be in order: (issue each statement in turn from Query Analyzer, if you execute them all as a batch it still works, but won't be as clear.)

SET IMPLICIT_TRANSACTIONS ON
CREATE TABLE TranTest (
 Col1 int IDENTITY,
 Col2 int)
SELECT @@TRANCOUNT 
--Returns 1, indicating that there is one open transaction
--Now let's throw some data into our table with a simple loop
DECLARE @count int
SET @count = 0
WHILE @count <15
 Begin
 INSERT TranTest (Col2)
 VALUES (0)
 SET @count = @count + 1
 END
SELECT @@TRANCOUNT 
/*
This still returns 1 indicating that all our statements so far are part of the same 
transaction. Of course it could also mean that each previous statement was its own 
transaction and was committed when the next statement was executed and only the last 
statement's transaction is now left open, but I will show you that this is not the case.
*/SELECT * FROM TranTest --We see our data.
COMMIT 
--Makes our table and data changes permanent. 
-- Notice there is no corresponding BEGIN TRAN since we are in Implicit Mode.
SELECT @@TRANCOUNT 
--This should now return 0 to show that there are no open transactions
--Now let's alter the table and add a few more rows to show that the 
-- statements are all lumped into a single transaction
ALTER TABLE TRANTEST ADD Col3 int
DECLARE @count int
SET @count = 0
WHILE @count <15
 Begin
 INSERT TranTest (Col2)
 VALUES (0)
 SET @count = @count + 1
 END
SELECT * FROM TranTest 
--Now we see Col3 and the additional data
SELECT @@TRANCOUNT 
--Returns 1
ROLLBACK
SELECT * FROM TranTest 
/*
Now you see it...Now you don't. Both the ALTER and INSERTs are rolled back 
showing that all the statements are grouped into a single transaction until a
COMMIT or ROLLBACK is issued.. 
*/

Nested Transactions

Transactions can be nested, but some aspects of nested transactions may be a bit surprising for the uninitiated; it does make sense though, so let's take a look. First, unlike stored procedures which are limited to 32 levels, there is apparently no practical limit to the number of nesting levels for transactions. I quit trying to reach the limit when I got to well over 200 million levels--which is way more than I'll ever need! @@TRANCOUNT's return data type is an integer so it would probably run into problems if your nesting level exceeded 2.14 billion or so...

To nest transactions you simply string together two or more BEGIN TRAN statements without a COMMIT or ROLLBACK statement "between" them. It should be obvious that transactions can only be nested when in Explicit Mode. Or to be more precise, upon issuing the required BEGIN TRAN statements SQL enters Explicit Mode.

BEGIN TRAN --We enter Explicit Mode here
INSERT TranTest (Col2)
VALUES (1)
SELECT @@TRANCOUNT
--Returns 1
SELECT * FROM TranTest
BEGIN TRAN
 DELETE TranTest
  WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
 SELECT @@TRANCOUNT 
 --Returns 2 indicating that there are now two open transactions.
 SELECT * FROM TranTest 
 --We can see that both the INSERT and DELETE appear to have taken effect.
COMMIT 
--Decrements @@TRANCOUNT by one but doesn't really commit the inner transaction.
ROLLBACK 
--Rolls back both transactions and sets @@TRANCOUNT to 0

Remember that a transaction--to include any nested transactions--must all commit or rollback

together. Because of this, the first COMMIT really does nothing more than closes the inner

transaction, it can't make the changes permanent since the inner transaction depends on the outcome

of any higher level transactions. The COMMIT that is issued when @@TRANCOUNT is 1 is what I call the

"Golden COMMIT" and finally makes all the changes permanent. Even though the inner COMMIT doesn't

seem to do much, you can't neglect to issue it. The entire transaction is only committed after one

COMMIT has been issued for every open transaction. In contrast ROLLBACK will always roll back all

open transactions; "if one fails, they all fail" A result of ROLLBACK's behavior is that if the

ROLLBACK is issued and then you try to issue a COMMIT or ROLLBACK you will get a 3902 or 3903 error

respectively which indicates that there are no open transactions to COMMIT or ROLLBACK. For this, and

other reasons I'll get into later, you should include error handling in your SQL code. If you check

@@TRANCOUNT before issuing a COMMIT or ROLLBACK you can pretty well eliminate 3902 and 3903

errors.

Just to cement the concept, try the following example:

DECLARE @Count int
SET @Count = 0
WHILE @Count < 100
BEGIN
  BEGIN TRAN 
  --We aren't doing any work here, just nesting transactions...
  SET @Count = @Count + 1
END
SELECT @@TRANCOUNT 
--Should return 100
COMMIT
SELECT @@TRANCOUNT 
--"99 bottles of beer on the wall..."
ROLLBACK
SELECT @@TRANCOUNT 
--"Go to Jail, don't pass GO..." We're right back to 0

The idea of nesting transactions applies to triggers and stored procedures too. If you have a complex set of stored procedures that call each other it can get difficult to follow all the possible paths, but any nested transactions will act exactly as I have already described. I should mention that there is always the error caveat that can really throw a monkey wrench into things. I'll deal with errors in some detail later, but will completely ignore errors for now. Below is a simple example of how you can nest a transaction across stored procedures:

CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
 INSERT TranTest (Col2)
 VALUES (1)
 EXEC TranProc2 --Call the other procedure from within the transaction
COMMIT --First level
CREATE PROC TranProc2
AS
BEGIN TRAN --Start the second level transaction
 DELETE TranTest
 WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
COMMIT --Second level

It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures.

CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
 INSERT TranTest (Col2)
 VALUES (1)
 BEGIN TRAN --Start the second level transaction
 EXEC TranProc2
 COMMIT --Second level
COMMIT --First level

This stored procedure would do exactly the same thing as the first example, assuming that the second procedure lacked the BEGIN TRAN and COMMIT statements. Actually, even if the second procedure had the BEGIN...COMMIT statements in it, this procedure would work fine and perform the same function, we would just have three levels of transactions, the second of which would do nothing. So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function.

So far we have discussed SQL Server's Transaction Modes and the idea of nested transactions. I have tried to show how these two concepts relate to each other and hinted at how they relate to some of the other concepts listed in the introduction. In the future articles I'll cover these additional topics and try to tie them all together.

By Don Peterson Thursday, September 30, 2004