Manage Nested Transactions

DataStream

LANGUAGES: C#

ASP.NETVERSIONS: 1.0 | 1.1

Manage Nested Transactions

Wrap calls to multiple stored procedures in a singletransaction and handle errors appropriately.

By Brian Noyes

ADO.NET lets you easily wrap multiple calls to thedatabase within the scope of a single transaction. Unfortunately, programmersare sometimes reluctant to use this technique because they don't fullyunderstand the interaction between transactions declared in ADO.NET code andtransactions declared within the stored procedures. This is sometimesaggravated by a confusing exception that can get thrown by SQL Server whenusing nested transactions with stored procedures that themselves usetransactions. In this article, I will clear up those misconceptions and show youhow to use nested transactions from code and handle errors appropriately.

SQL Server Transactions 101

The first thing to understand is the basics of howtransactions work within the database. Transactions allow you to ensure thatany modifications made to the database within the scope of a transaction aredone in an "all or nothing" fashion. There are different isolation levels thatdetermine whether data read in the scope of a transaction can be changed bysomeone else while that transaction is in progress. The default is ReadCommitted, meaning the data can be changed while in the scope of a transaction,but you won't get back data that is only partially done changing from anothertransaction in progress. If you want to make sure the data from a query is notchanged by some other transaction until the transaction you are managingcompletes, you will need to increase the isolation level to Repeatable Read orSerializable. Read up on the isolation levels in the SQL Books online for moredetails.

Any individual query statement that gets executed by SQLServer is automatically wrapped within a transaction. So if you have an updatestatement that will modify 100 rows in a table, and something goes wrong whilemodifying record 99, all the other changes will be rolled back and nomodifications will persist to the table after the error is raised. If you areusing stored procedures to access your database as you should, those storedprocedures might also wrap one or many SQL statements within a transaction thatis scoped to the stored procedure. For example, consider the simple storedprocedure in Figure 1.

CREATE PROCEDURE AddOrder

@CustomerName nvarchar(50),

@StatusID int

AS

  SET TRANSACTIONISOLATION LEVEL REPEATABLE READ

  BEGIN TRANSACTION

  IF NOT EXISTS

    (SELECT StatusID FROM OrderStatus

    WHERE StatusID =@StatusID)

  BEGIN

    ROLLBACK TRANSACTION

    RAISERROR('You mustprovide a valid Status ID',11,1)

    RETURN

  END

  INSERT INTO Orders(CustomerName, StatusID)

   VALUES (@CustomerName,@StatusID)

  COMMIT TRANSACTION

RETURN

Figure 1. A simplestored procedure that uses a transaction to wrap multiple calls to thedatabase.

This stored procedure starts its own transaction to wrap aSELECT and an INSERT within a single transaction. The SELECT ensures that avalid StatusID has been provided (maybe to check to ensure a foreign keyconstraint will not be violated), and the INSERT adds the record to the Orderstable. If you have multiple queries wrapped in a stored procedure, you may wantor need to manage a transaction at that level. Rolling back the transactionanywhere before the COMMIT TRANSACTION call will prevent any changes made tothat point from being persisted to the database.

You may think that including the SELECT statement withinthe transaction is unnecessary because it is not changing the database. For alot of situations, you would be wrong. Executing queries like this is likedealing with multithreaded programming. You have to assume that between thepoint where you execute the SELECT statement and the point where you performthe INSERT someone could have modified the OrderStatus table to remove thevalue you just checked for, and the INSERT could fail as a result. You oftenneed to ensure that all the queries you are executing are working off the samestate of the database, and the best way to do that is to wrap those querieswithin the same transaction, and bump the isolation level up to Repeatable Reador Serializable if needed.

If the INSERT statement throws an error, the transactionwill be rolled back automatically, so you don't need to explicitly check for anerror and roll back the transaction after each statement. But there may beresults that you would check for that would cause you to explicitly roll back atransaction.

ADO.NET Transactions

What if you want to execute several stored procedures in arow from code like the one above that will make modifications to the database,and you want to make sure those changes are made in an "all or nothing"fashion. ADO.NET includes a SqlTransaction class that you can associate withyour connection to do exactly that. The process is simple, you just callBeginTransaction on the SqlConnection object to get back a new transactionobject, then you call SqlTransaction.Commit when you know everything went OK,or SqlTransaction.Rollback if something bad occurs (see Figure 2).

// Create the connection (conn)

// declare the transaction

SqlTransaction trans;

// Create the commands

try{

  conn.Open();  // Open the connection

  trans = conn.BeginTransaction();

  // Associate thetransaction with the commands

  // Execute the queries

  // If no exceptions tothis point, commit

  trans.Commit();

}

catch (Exception ex)

{

  // Something badhappened - rollback

  if (trans != null)trans.Rollback();

}

finally

{

  conn.Close();

}

Figure 2. Wrappingmultiple queries within a transaction is as simple as calling BeginTransactionon the connection to start it, and committing or rolling it back at theappropriate points.

So if it is that simple, we are done, right? Well, sortof. The trick comes in properly handling the exception in your catch block andunderstanding the meaning of the exceptions that get thrown. If you are callingstored procedures that don't explicitly manage their own transactions, you shouldn'trun into any difficulty. If an error is raised anywhere in the execution of anyof the individual queries in your code, it will raise a SqlException, whichwill be caught in the catch block in the code in Figure 2. The code in thecatch block will call Rollback and none of the updates that occurred within thescope of the transaction started in your code will be persisted.

Don't Be Afraid of Your Nesting Instincts

So what happens if the stored procedures you are callingmanage their own transactions? Don't those calls to COMMIT TRANSACTION withinthe stored procedure mean that the changes will be persisted to the databasewhen that statement is hit in calls that succeed? The answer is no, and thereason is the way nested transactions work in SQL Server. When you begin atransaction in your code, you are really just beginning a SQL Servertransaction scoped to the connection that will span any calls made against thatconnection until Commit or Rollback is called - by someone. That someone can beyour code, it could be code in a stored procedure, or it could be SQL serveritself if an error occurs.

If the stored procedure itself tries to start atransaction with a BEGIN TRANSACTION statement, it really just joins the scopeof the existing transaction. So any rollback that occurs within the storedprocedure will roll back the outer transaction that started in your code, whichis what you really want anyway.

The only additional hitch to be aware of that trips somepeople up is that when the rollback occurs, a SqlException will be raised withthe following error description:

"Transaction count after EXECUTE indicates that a COMMIT or

ROLLBACK TRANSACTION statement is missing. Previous

count = 1, current count = 0."

This is being thrown by SQL Server because it sees thatthe stored proc was entered with one transaction in progress, but when thestored proc is exited, there are none in progress because it was rolled back.Because it is thrown as an exception, you will obviously want to catch it.There is no harm in still calling Rollback on the exception you are managing incode, so you can just consistently call Rollback in your exception handler, andnot have to do it conditionally based on the contained information in theexception.

The only additional thing you might want to do is stripout the error info about the transaction count if you are going to do loggingof the exception somewhere, because the message is a little confusing if youdon't understand the nature of these nested transactions. If you manually rollback a transaction in a stored procedure with a ROLLBACK TRANSACTION statement,and do not raise an error in addition to rolling back the transaction, then theSqlException that gets raised will have the error message above. If you raise anerror in addition to rolling back as shown in Figure 1, the error you raisewill be the one that is the top level exception, and the one about thetransaction count will be the inner exception, but the top level exceptionmessage will still have the statement above appended. If you want to get eacherror separately, iterate through the Errors collection as I described in myarticle GainInsight into SQL Errors. The SQL error number for the transaction counterror is number 266, so you can screen against that if you want to preventpushing it out to any logging mechanism you are using for your exceptions.

The download code for this article includes a simple applicationthat you can play with that demonstrates these concepts by performing updatesagainst a sample database that can fail in a couple different ways.

The code accompanying this article is available fordownload.

Brian Noyes is asoftware architect with IDesign, Inc. (http://www.idesign.net),a .NET focused architecture and design consulting firm. Brian specializes indesigning and building data-driven distributed Windows and Web applications. Hehas over 12 years of experience in programming, engineering, and projectmanagement, and is a contributing editor and writer for C#PRO, asp.netPRO, and other publications. Contacthim at [email protected].