DataStream

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 | 1.1

 

Manage Nested Transactions

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

 

By Brian Noyes

 

ADO.NET lets you easily wrap multiple calls to the database within the scope of a single transaction. Unfortunately, programmers are sometimes reluctant to use this technique because they don't fully understand the interaction between transactions declared in ADO.NET code and transactions declared within the stored procedures. This is sometimes aggravated by a confusing exception that can get thrown by SQL Server when using nested transactions with stored procedures that themselves use transactions. In this article, I will clear up those misconceptions and show you how to use nested transactions from code and handle errors appropriately.

 

SQL Server Transactions 101

The first thing to understand is the basics of how transactions work within the database. Transactions allow you to ensure that any modifications made to the database within the scope of a transaction are done in an "all or nothing" fashion. There are different isolation levels that determine whether data read in the scope of a transaction can be changed by someone else while that transaction is in progress. The default is Read Committed, 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 another transaction in progress. If you want to make sure the data from a query is not changed by some other transaction until the transaction you are managing completes, you will need to increase the isolation level to Repeatable Read or Serializable. Read up on the isolation levels in the SQL Books online for more details.

 

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

 

CREATE PROCEDURE AddOrder

@CustomerName nvarchar(50),

@StatusID int

AS

  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

  BEGIN TRANSACTION

  IF NOT EXISTS

    (SELECT StatusID FROM OrderStatus

    WHERE StatusID = @StatusID)

  BEGIN

    ROLLBACK TRANSACTION

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

    RETURN

  END

  

  INSERT INTO Orders (CustomerName, StatusID)

   VALUES (@CustomerName, @StatusID)

  COMMIT TRANSACTION

RETURN

Figure 1. A simple stored procedure that uses a transaction to wrap multiple calls to the database.

 

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

 

You may think that including the SELECT statement within the transaction is unnecessary because it is not changing the database. For a lot of situations, you would be wrong. Executing queries like this is like dealing with multithreaded programming. You have to assume that between the point where you execute the SELECT statement and the point where you perform the INSERT someone could have modified the OrderStatus table to remove the value you just checked for, and the INSERT could fail as a result. You often need to ensure that all the queries you are executing are working off the same state of the database, and the best way to do that is to wrap those queries within the same transaction, and bump the isolation level up to Repeatable Read or Serializable if needed.

 

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

 

ADO.NET Transactions

What if you want to execute several stored procedures in a row 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 with your connection to do exactly that. The process is simple, you just call BeginTransaction on the SqlConnection object to get back a new transaction object, 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 the transaction with the commands

  // Execute the queries

  // If no exceptions to this point, commit

  trans.Commit();

}

catch (Exception ex)

{

  // Something bad happened - rollback

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

}

finally

{

  conn.Close();

}

Figure 2. Wrapping multiple queries within a transaction is as simple as calling BeginTransaction on the connection to start it, and committing or rolling it back at the appropriate points.

 

So if it is that simple, we are done, right? Well, sort of. The trick comes in properly handling the exception in your catch block and understanding the meaning of the exceptions that get thrown. If you are calling stored procedures that don't explicitly manage their own transactions, you shouldn't run into any difficulty. If an error is raised anywhere in the execution of any of the individual queries in your code, it will raise a SqlException, which will be caught in the catch block in the code in Figure 2. The code in the catch block will call Rollback and none of the updates that occurred within the scope 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 calling manage their own transactions? Don't those calls to COMMIT TRANSACTION within the stored procedure mean that the changes will be persisted to the database when that statement is hit in calls that succeed? The answer is no, and the reason is the way nested transactions work in SQL Server. When you begin a transaction in your code, you are really just beginning a SQL Server transaction scoped to the connection that will span any calls made against that connection until Commit or Rollback is called - by someone. That someone can be your code, it could be code in a stored procedure, or it could be SQL server itself if an error occurs.

 

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

 

The only additional hitch to be aware of that trips some people up is that when the rollback occurs, a SqlException will be raised with the 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 that the stored proc was entered with one transaction in progress, but when the stored 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 in code, so you can just consistently call Rollback in your exception handler, and not have to do it conditionally based on the contained information in the exception.

 

The only additional thing you might want to do is strip out the error info about the transaction count if you are going to do logging of the exception somewhere, because the message is a little confusing if you don't understand the nature of these nested transactions. If you manually roll back 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 the SqlException that gets raised will have the error message above. If you raise an error in addition to rolling back as shown in Figure 1, the error you raise will be the one that is the top level exception, and the one about the transaction count will be the inner exception, but the top level exception message will still have the statement above appended. If you want to get each error separately, iterate through the Errors collection as I described in my article Gain Insight into SQL Errors. The SQL error number for the transaction count error is number 266, so you can screen against that if you want to prevent pushing it out to any logging mechanism you are using for your exceptions.

 

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

 

The code accompanying this article is available for download.

 

Brian Noyes is a software architect with IDesign, Inc. (http://www.idesign.net), a .NET focused architecture and design consulting firm. Brian specializes in designing and building data-driven distributed Windows and Web applications. He has over 12 years of experience in programming, engineering, and project management, and is a contributing editor and writer for C#PRO, asp.netPRO, and other publications. Contact him at brian.noyes@idesign.net.