Microsoft & .NET.NETNesting Transactions with a Single SQL Server Database

Nesting Transactions with a Single SQL Server Database

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

Many applications developed for the corporate world deals with SQL databases, and proper transaction handling is a central requirement for performance and robust data storage. When you are using C# to access data in Microsoft SQL Server databases, you are using transactions either implicitly or explicitly.

Unless your application is a very simple one, chances are you are using the System.Transactions namespace and its classes—especially TransactionScope—to help you control your transactions. However, have you ever thought of what is going on inside these classes? In addition, what would happen if you nested these transactions?

In this article, you will see a concrete example of transaction scope nesting when using a single SQL Server database. However, before doing so, a quick recap on transaction scopes and their use is in order.

A Simple Transaction Example

If you are using ADO.NET 2.0, code similar to the following probably looks familiar:

string connectionString = "Data Source=...";
string selectSql = "SELECT ...";

using (TransactionScope scope = new TransactionScope())
{
   using (SqlConnection conn =
      new SqlConnection(connectionString))
   {
      conn.Open();
      using (SqlCommand cmd = new SqlCommand(selectSql, conn))
      {
         SqlDataReader reader = cmd.ExecuteReader();
         // manipulate data here...
      }
   }
   scope.Complete();
}

In this code, C#’s using statement is utilized to get cleaner code. In fact, the using statement is a perfect match to the TransactionScope class. This is because you as a developer need to make sure the Dispose method of the class is called immediately after you are done with the scope. Because the using statement guarantees that the object given will be disposed even in the case of an exception, it is an uncomplicated solution to the common problem.

Of course, sometimes the code to manipulate your query results can be complex; therefore, you are tempted to move the code into a method of its own (Visual Studio’s Extract Method refactoring works wonders here). Alternatively, you might wish to make your method more generic, thus save coding time later.

But, how about transactions in these situations? If you are writing generic code, chances are your new method will be called from many places. How can you be sure you are in fact inside a transaction? By making sure your method uses a transaction scope on its own, of course! Then the obvious question becomes, what happens if my transactions are nested?

Nested Transaction Scopes Just Work

The beauty of .NET is that, often, things just work. Even though this wouldn’t always be the case, when it comes to nested transactions, they work in a way most of us would expect them to work. Shortly put, if you do not do anything special, a nested transaction joins the current ambient transaction, if any.

When your application first starts, there is no ambient transaction. When you create a TransactionScope object, the transaction represented by this object becomes the ambient transaction. With the default constructor (no parameters given), the next transaction scope created joins this ambient transaction. You can always get the current ambient transaction by using the static Transaction.Current property.

When you call the inner scope’s Complete method, the transaction is not yet committed to the database because there is still an outer transaction to be committed. Only when the outer transaction scope is marked complete (committed) will the SQL Server database data also be committed. At this point, no ambient transaction exists anymore.

If any of the scopes choose to indicate failure by simply disposing the scope object and not calling Complete, the whole transaction is rolled back once the outermost scope finishes. If the inner transaction indicated failure, you also will get a TransactionAbortedException exception when you call Complete on the outer transaction. Shortly put, all scopes must call Complete for the transaction to succeed.

Transaction Options

If you want to have additional control over your transactions, you can always specify a TransactionScopeOption setting when constructing your scope object. With these options, you are able to specify whether your nested transactions should join the ambient transaction, create a completely new transaction, or even suppress the ambient transaction.

Because these options have already been discussed in detail in previous articles, it is not necessary to go into detail here. Shortly put, by specifying either Required or leaving the TransactionScope constructor without parameters leads to the same results. RequiresNew indicates that you would need a completely new transaction. However, in the case of a single database, this has no real effect because the connection strings would point to the same server.

A Transaction Testing Application

Often, a test harness application is the best way to explorer a technology interactively. The sample application accompanying this article is shown in Figure 1.

Figure 1: The sample application “Nested Transactions Test.”

To run the sample application, you will need a connection to an SQL Server instance, such as a local SQL Server Express installation. In addition, you will need to create a test table called TEST with the following SQL statement:

CREATE TABLE [dbo].[Test] (
   [Name] [nvarchar](50) NULL)

Even though the table is trivial, it is sufficient to test the most important button of the application, the “Multi-Test” button. As the name of the button suggests, it tests multiple nested transactions. With the two check boxes below the button, you are able to indicate which transaction scopes (if any) should be marked complete.

Here is the code the button executes:

TransactionScope outerScope = new TransactionScope();
using (outerScope)
{
   string sql = "INSERT INTO [dbo].[test] ([name])
      VALUES ('AAAA')";
   RunSql(sql);
   TransactionScope innerScope = new TransactionScope();
   using (innerScope)
   {
      sql = "INSERT INTO [dbo].[test] ([name]) VALUES ('BBBB')";
      RunSql(sql);
      if (innerCheckBox.Checked) innerScope.Complete();
   }
   if (outerCheckBox.Checked) outerScope.Complete();
}

Here, two nested transaction scopes are created, and a simple INSERT statement is executed by both scopes. Depending on the check box states, those scopes are marked complete or simply disposed.

In addition to these functions, the sample application lets you do manual testing and transaction monitoring. With the buttons in the “Manual Testing” group, you are able to test transaction nesting interactively against the Northwind sample database. Beware, though, because not being able to complete and/or dispose a scope object can cause trouble like lingering transactions!

Reading SQL Server Transaction Data

The final thing the sample application is able to conduct is real-time monitoring of active transactions. To do this, the sample application can read SQL Server’s Tempdb database, which contains system views that hold the data. For example, to get a list of currently active transactions, the following SQL statement can be used:

SELECT [transaction_id], [database_id],
   [database_transaction_state]
FROM [tempdb].[sys].[dm_tran_database_transactions]

This is exactly what the “Transaction Monitoring” part of the example application does. Note that you also could use, for example, Windows’ Performance Monitor (perfmon.exe) to monitor active transactions. The proper counter is the Transactions counter under the given instance of SQL Server you wish to monitor.

Conclusion

In this article, you saw how nested transactions work when working with a single SQL Server database. As confirmed by the sample application, nested transactions with default options work in a simple, yet intuitive way. If the transaction is to succeed, all participating transactions must call Complete.

The transactional support can of course do much more than work with single databases. In fact, it supports even distributed transactions, intelligent escalation, config-file enabled tracing, and more. But, these are topics for another article.

Happy transactional programming!

Download the Code

You can download the code that accompanies this article here.

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP and has written dozens of magazine articles and published two books about software development. He is a group leader of a Finnish software development expert group at ITpro.fi. His frequently updated blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking his name at the top of the article.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories