March 9, 2021
Hot Topics:

Nesting Transactions with a Single SQL Server Database

  • By Jani Järvinen
  • Send Email »
  • More Articles »


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))
      using (SqlCommand cmd = new SqlCommand(selectSql, conn))
         SqlDataReader reader = cmd.ExecuteReader();
         // manipulate data here...

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."

Page 1 of 2

This article was originally published on January 23, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date