Data Points

Revisiting System.Transactions

John Papa

Code download available at:DataPoints2006_11.exe(172 KB)

Contents

In a Nutshell
Current Transactions and Enlistment
Transactional Settings
Disposal
Lightweight Transactions
Nesting
Wrapping It Up

The System.Transactions namespace of the Microsoft .NET Framework makes handling transactions much simpler than previous techniques. Some time ago I wrote a Data Points column about how System.Transactions worked with Beta 1 of the Microsoft® .NET Framework 2.0 and SQL Server™ 2005. Of course, during the release process some features were added and some were dropped; several of the TransactionScopeOptions changed.

Since then, I've received many questions about System.Transactions and have decided it's time to revisit it. So let's look at how it works now, how to use it, when it makes sense, and when it does not. Along the way you'll see how to take advantage of .NET constructs to make your use of the namespace more efficient. I'll also demonstrate best practices with transactions. All of the samples can be found in the download at the MSDN®Magazine Web site.

In a Nutshell

Let's begin with how to turn two database commands into a transaction by building a wrapper around them. It's easy. Just reference the System.Transactions.dll, wrap the code you want to be transactional within a using statement that creates a TransactionScope, and invoke the Complete method at the end of the transaction.

Figure 1 shows a transaction being created and wrapping itself around multiple database queries. If either of the SqlCommand objects throws an exception, control of flow will jump out of the TransactionScope's using statement block and the TransactionScope will dispose of itself and roll back the transaction. Because the code employs the using statements, the SqlConnection and the TransactionScope objects will be disposed of automatically. So, by adding only a few lines of code, you now have a transactional model that handles exceptions, cleans up after itself, and manages the committing or rolling back of the commands.

Figure 1 A Simple Transaction

// Create the TransactionScope using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn2005 = new SqlConnection(someSql2005)) { SqlCommand cmd = new SqlCommand(sqlUpdate, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } using (SqlConnection cn2005 = new SqlConnection(anotherSql2005)) { SqlCommand cmd = new SqlCommand(sqlDelete, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } // Tell the transaction scope that the transaction is in // a consistent state and can be committed ts.Complete(); // When the end of the scope is reached, the transaction is // completed, committed, and disposed. }

The example that you see in Figure 1 has a lot of moving parts. TransactionScope encompasses all resource manager connections that automatically enlist in transactions. You can also set different options on the TransactionScope, although the example in Figure 1 uses defaults.

Current Transactions and Enlistment

At the heart of System.Transactions is the TransactionScope class. When it is instantiated, it creates a current transaction (also known as an ambient transaction) that any resource manager can enlist itself in. For example, if an instance of TransactionScope has been created and a connection is opened to a resource manager that defaults to automatic enlistment in transactions, the connection will join the transaction scope.

You can examine whether or not a transaction scope exists at any point in your code by looking at the System.Transactions.Transaction.Current property. If this property is null, then no current transaction exists. When a resource manager opens its connection to its resource, it looks to see if a transaction exists. If the resource manager has been set to automatically enlist itself in a current transaction, then it will join the transaction. One of the attributes of the SQL Server connection string is auto-enlist. By default, auto-enlist is set to true, so any active transaction will be joined. You can change the default by explicitly adding auto-enlist=false to the connection string, like so:

Server=(local)\SQL2005;Database=Northwind; Integrated Security=SSPI;auto-enlist=false

This is the magic of System.Transactions. I did not change any of the ADO.NET code in Figure 1, yet it still takes advantage of the TransactionScope. All I had to do was create a TransactionScope and the SqlConnection object enlisted in the active transaction when the connection was opened.

Transactional Settings

If you want to deviate from the default settings of the TransactionScope class, you can set the isolation level and the transaction's timeout period on the TransactionScope object by creating a TransactionOptions object. The TransactionOptions class has an IsolationLevel property, which can change the default isolation level of serializable to another level such as ReadCommitted or even the new Snapshot level introduced with SQL Server 2005. (Keep in mind that the isolation level is merely a suggestion. Most database engines will try to use the suggested isolation level, but may opt for another.) The TransactionOptions class also has a TimeOut property that can be used to deviate from the default of one minute.

The sample in Figure 1 used the default TransactionScope object with its default constructor. This sets the isolation level to serializable, the transaction's timeout period to one minute, and its TransactionScopeOptions setting to Required. If you want to alter these settings, there are seven additional overloaded constructors for the TransactionScope that you can use. In Figure 2, I have listed the TransactionScopeOptions enumerators settings. These enumerators allow you to control how nested transactions will respond to each another. The code in Figure 3 actually changes the settings.

Figure 3 Changing Transactional Settings

// Create the TransactionOptions object TransactionOptions tOpt = new TransactionOptions(); // Set the Isolation Level tOpt.IsolationLevel = IsolationLevel.ReadCommitted; // Set the timeout to be 2 minutes // Uses the (hours, minutes, seconds) constructor // Default is 60 seconds tOpt.Timeout = new TimeSpan(0, 2, 0); string cnString = ConfigurationManager.ConnectionStrings[ "sql2005DBServer"].ConnectionString); // Create the TransactionScope with the RequiresNew transaction // setting and the TransactionOptions object I just created using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tOpt)) { using (SqlConnection cn2005 = new SqlConnection(cnString) { SqlCommand cmd = new SqlCommand(updateSql1, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } ts.Complete(); }

Figure 2 TransactionScopeOptions Enumerators

Disposal

The key to using System.Transactions is understanding how and when the transaction ends. If a TransactionScope object is not properly disposed of, the transaction will remain open until either the object has been collected by the garbage collector or the timeout period has elapsed. One danger in leaving the transaction open is that it will lock the resource manager's resources while it is active. To better understand this, take a look at the following code sample:

TransactionScope ts = new TransactionScope(); SqlConnection cn2005 = new SqlConnection(cnString); SqlCommand cmd = new SqlCommand(updateSql1, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); cn2005.Close(); ts.Complete();

This code will create an instance of the TransactionScope object, and when the SqlConnection is opened it will join the transaction. If all goes well, the command will execute, the connection will close, the transaction will complete, and it will be disposed of. If an exception is thrown, the control of flow will skip over the closing of the SqlConnection and the disposal of the TransactionScope, causing the transaction to remain open longer than desired. The key is to make sure the TransactionScope is disposed of properly so the transaction either commits or rolls back quickly. There are two easy ways to handle this: a try/catch/finally block or the using statement. You could declare the objects outside of the try/catch/finally block, add the code to a try block to create the objects and execute the command, and put the disposal of the TransactionScope and the SqlConnection in the finally block. This will ensure that the transaction is closed out in a timely manner.

I often prefer the using statement as it implicitly creates a try/catch block under the covers for you. The using statement guarantees that the TransactionScope will be disposed of even if an exception is thrown in the middle of the code block. It makes sure that the Dispose method is called on the TransactionScope whenever the block is exited. This is important because just prior to disposal of the TransactionScope, the transaction is completed. When the transaction is completed, the TransactionScope evaluates whether or not the Complete method has been called. If it has, then the transaction is committed; otherwise, it is rolled back. The earlier code could be rewritten as follows:

using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn2005 = new SqlConnection(cnString) { SqlCommand cmd = new SqlCommand(updateSql1, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } ts.Complete(); }

Notice that I used the using statement with both the TransactionScope and the SqlConnection object. This ensures both objects would be disposed of properly and quickly if an exception is thrown. If no exception is thrown, the objects are disposed of when the end of the using statement's code bock is reached (its last curly brace).

Lightweight Transactions

One of the greatest features of System.Transactions is its support of lightweight transactions. Lightweight transactions do not involve the Microsoft distributed transaction coordinator (DTC) unless they need to. If the transaction is local, it will remain lightweight. If the transaction becomes distributed and involves a second resource manager, then the lightweight transaction will be promoted to a fully distributed transaction and will indeed involve the DTC.

Involving the DTC, while necessary in distributed scenarios, is expensive. So if you don't strictly need it, it's best to avoid it. Fortunately, SQL Server 2005 supports lightweight transactions; previous versions of SQL Server do not (therefore, all transactions with SQL Server 2000 will always be upgraded to distributed transactions). Let's take a look at a few examples of the benefits of lightweight transactions.

In the example in Figure 4, I create a TransactionScope that executes two commands against a single SQL Server 2000 database. When the connection to SQL Server 2000 is opened, it joins the TransactionScope; because SQL Server 2000 does not support lightweight transactions, the DTC is involved—even though this is obviously not a distributed scenario (since I'm only hitting one database and it's on the same connection).

Figure 4 No Lightweight Support

using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn2000 = new SqlConnection(cnString2000)) { cn2000.Open(); SqlCommand cmd1 = new SqlCommand(updateSql1, cn2000); cmd1.ExecuteNonQuery(); SqlCommand cmd2 = new SqlCommand(updateSql2, cn2000); cmd2.ExecuteNonQuery(); } ts.Complete(); }

In the next example (shown in Figure 5) I create a TransactionScope that executes two commands against a single SQL Server 2005 database. Since SQL Server 2005 supports lightweight transactions, this will become a distributed transaction only if a second resource manager is involved in it.

Figure 5 Lightweight Support

using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn2005 = new SqlConnection(cnString2005)) { cn2005.Open(); SqlCommand cmd1 = new SqlCommand(updateSql1, cn2005); cmd1.ExecuteNonQuery(); SqlCommand cmd2 = new SqlCommand(updateSql2, cn2005); cmd2.ExecuteNonQuery(); } ts.Complete(); }

So when should you use System.Transactions? If you are going to be using a distributed transaction, System.Transactions makes a lot of sense. Also, if your resource manager supports lightweight transactions, System.Transactions is the way to go. But using System.Transactions to wrap all database commands is not necessarily a best practice.

For example, let's assume your application executes multiple commands against a database that does not support lightweight transactions. Business rules dictate that these operations need to be wrapped in a transaction to keep them atomic. If the commands within the transaction are hitting a single database, then ADO.NET transactions would be more efficient than System.Transactions since an ADO.NET transaction will not invoke the DTC in this case. If you have places in your application that truly need to be distributed, then System.Transactions is a good option.

So what resource managers does System.Transactions work with? Actually, it works with all of them, but only those resource managers that support lightweight transactions will take full advantage of promotable transactions.

Nesting

Earlier I mentioned the TransactionScopeOptions enumerator and how you can set it to be Required (the default value), RequiresNew, or Suppress. This comes into play when you have nested methods and transactions. For example, let's assume Method1 creates a TransactionScope, executes a command against a database, and then calls Method2. Method2 creates a TransactionScope of its own and executes another command against a database. There are several ways you might want to handle this. You might want Method2's transaction to join Method1's transaction or you may want Method2 to create a separate transaction of its own. This is where the TransactionScopeOptions enumerator becomes valuable. Figure 6 illustrates nested transactions.

Figure 6 Nesting Transactions

private void Method1() { using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required)) { using (SqlConnection cn2005 = new SqlConnection()) { SqlCommand cmd = new SqlCommand(updateSql1, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } Method2(); ts.Complete(); } } private void Method2() { using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew)) { using (SqlConnection cn2005 = new SqlConnection()) { SqlCommand cmd = new SqlCommand(updateSql2, cn2005); cn2005.Open(); cmd.ExecuteNonQuery(); } ts.Complete(); } }

Here the inner transaction (Method2) will create a second TransactionScope instead of joining the outer transaction (from Method1). Method2's TransactionScope was created with the RequiresNew setting, which tells the transaction to create its own scope and not join an existing one. If you wanted this transaction to join the existing one, you could have left the default setting or set the option to Required.

When transactions join a TransactionScope (because they use Required), they all must vote to Complete the transaction successfully for it to commit. If any of the transactions within the same TransactionScope do not vote to Complete by not calling ts.Complete, then when the outer TransactionScope is disposed of, it will roll back.

Wrapping It Up

It is important to get in and out of your transactions quickly since transactions lock valuable resources. Best practices dictate that you create a transaction just before you need it, open your connection immediately before you need to execute commands on them, execute your action queries, and complete and dispose of the transaction as soon as possible. You also should avoid performing any nonessential, non-database code during the transaction to avoid keeping the resources locked any longer than necessary.

One of the greatest features of the lightweight transaction is that it can determine if it needs to promote itself to a distributed transaction. As I have demonstrated, System.Transactions can be beneficial when used appropriately. The key is knowing how and when.

Send your questions and comments for John to mmdata@microsoft.com.

John Papa, C# MVP and MCSD.NET, has been working with Microsoft distributed architectures for over 10 years. He has enterprise experience architecting and developing with ASP.NET as well as Web Forms using both C# and Visual Basic .NET. John has authored or coauthored several books and speaks at industry conferences. Reach him at codebetter.com/blogs/john.papa.