Data Points

ADO.NET and System.Transactions

John Papa

Contents

Getting Started
Local Implicit Transactions
Transaction Options
Distributed Implicit Transactions
Smart Transactions with SQL Server 2005
Alternatives
Conclusion

The Microsoft® .NET Framework versions 1.0 and 1.1 represented major changes in software development. However, one important thing that did not change much was support for distributed transactions. All of that is about to change because the .NET Framework 2.0, which includes a new namespace called System.Transactions, will offer significantly improved support for distributed transactions. This new namespace enhances transactional support for managed code and makes it possible to handle transactions without using interception or reflection and without deriving from a ServicedComponent. System.Transactions is also designed to integrate well with the upcoming release of SQL Server™ 2005 and offers automatic promotion of local lightweight transactions to fully distributed transactions.

In addition, the namespace is flexible enough to handle both implicit (automatic) and explicit (manual) transactions. The implicit transactions require less code, and I find that they cover most of the situations you may encounter. You can even modify some of the settings of the implicit transactions such as the transaction timeout period. Manual transactions are useful when you want to make modifications to a transaction's behavior.

To give you a feel for how this all works, I'll take a look at a code sample that implements an implicit local transaction using the TransactionScope object and its default settings. Then I'll walk through a code sample that modifies the default transactional settings of the implicit local transaction to show you how to modify the isolation level, the timeout period, and a transaction's context. You'll see that the real power is revealed when you design your code for implicit distributed transactions. I will demonstrate how and when the System.Transactions namespace delegates the promotion of a lightweight transaction to a fully distributed transaction and I will explain when more specific control over a transaction is required. Finally, I will walk through an example that shows how to develop explicit transactions and manually choose if and when to enlist in a transaction context. (Don't forget that this column is based on a beta version of the product and details may change.)

Getting Started

The transactional examples I will present will access SQL Server 2000 and SQL Server 2005 databases using ADO.NET and some features of the System.Transactions namespace. Keep in mind that while System.Transactions interacts well with ADO.NET, it is not part of ADO.NET nor the System.Data namespace. This separation allows it to manage transactions across different resource managers including SQL Server, Microsoft Message Queue Server (MSMQ), and Oracle.

The classes I require are in Systems.Transactions.dll, so I need to add a reference to that file from my .NET project in order to take advantage of the managed transactions functionality I'm discussing. You should assume that all of my examples also include the following using statements:

using System.Data;
using System.Data.SqlClient;
using System.Transactions;

Finally, to avoid repeating the connection strings and SQL statements, I have created a few private class fields to store these values (in a real app, these would be properties and would more likely retrieve these values from a configuration file). These fields are shared by all of the methods that I will demonstrate. The first connection string accesses a SQL Server 2005 Beta 2 Northwind database, while the second connects to a SQL Server 2000 Northwind database. There is only a single UPDATE statement that will be executed against both databases, modifying a single row's value in the Customers table. I use it merely to demonstrate the transactions:

private string sCn1 =
  "Server=MyYukonServer;Database=Northwind;Integrated Security=True";
private string sCn2 =
  "Server=MySQL2000Server;Database=Northwind;Integrated Security=True";
private string sSQL = 
  "UPDATE Customers SET City = 'New York' WHERE CustomerID = 'ALFKI'";

Local Implicit Transactions

The first transaction scenario I will walk through is the simplest—a local implicit transaction. This example, shown in Figure 1, shows a connection being made to a single database and a SQL command being executed against it, all wrapped inside of a transaction. There is no distributed transaction required here since only a single database is involved.

Figure 1 Implicit, Local Transaction

void btnImplicitLocal_Click(object sender, EventArgs e)
{
    // Create the TransactionScope
    using (TransactionScope oTranScope = new TransactionScope())
    {
        using (SqlConnection oCn1 = new SqlConnection(this.sCn1))
        {
            SqlCommand oCmd = new SqlCommand(this.sSQL, oCn1);
            oCn1.Open();
            oCmd.ExecuteNonQuery();
            oCn1.Close();
        }

        // Tell the transaction scope that the transaction is in a
        // consistent state and can be committed
        oTranScope.Consistent = true; // oTranScope.Complete() in beta 2

        // The following bracket completes, commits, and disposes 
        // the transaction
    }
}

The code in Figure 1 begins by creating an instance of the TransactionScope object from the System.Transactions namespace. It creates it within a using statement so that the TransactionScope is guaranteed to be disposed of, even if an exception is thrown in the middle of the code block. The using statement is handy here because it makes sure that the Dispose method is called on the TransactionScope whenever the block is exited. This is important because just before the TransactionScope is disposed, the transaction is completed. When the transaction is complete, the TransactionScope evaluates the Boolean value of the Consistent property (set to false by default). If the value of Consistent is true, the transaction is committed. If the value is false, then the transaction is rolled back. So by employing the using statement, the transaction is guaranteed to make sure the transaction completes, the Consistent flag is checked, and the transaction is committed or rolled back. Note that in the upcoming .NET Framework 2.0 Beta 2, the Consistent flag has been replaced by a new method, Complete, that is to be called on the TransactionScope object when the actions have been performed successfully.

The using keyword does not have to be used, but it is highly recommended. Note that Visual C++® 2005 and Visual Basic® 2005 both support a similar construct, so you should follow the same pattern when using those languages. The important thing here is to make sure that the TransactionScope is disposed, and the using statement just makes it simpler to do that. In addition, the using statement makes it really easy to see everything that is contained within the TransactionScope since it is commonly indented and between the curly braces.

When the SqlConnection.Open method is invoked in Figure 1, the connection is opened and automatically enlists in the current transaction scope. In fact, any connection opened inside of the using statement's curly braces is automatically included in the transaction scope and will participate in the same transaction.

The TransactionScope is smart enough not to create a fully distributed transaction with all of its overhead unless it is necessary. If the resource involved in the transaction is volatile, everything will stay in memory and the Microsoft distributed transaction coordinator (DTC) will not be involved. If the resource is a connection to SQL Server 2005, the DTC will not be involved, and SQL Server 2005 will handle the transaction. This is equivalent to opening an internal SQL transaction. If SQL Server 2000 is used, it will resolve to using a full DTC transaction.

In this example, the TransactionScope will first create a type of transaction called a LightweightTransaction, which is a form of a local, non-distributed transaction. A LightweightTransaction does not use the DTC service and therefore will not show up as a transaction in Component Services transaction statistics (Component Services | Computers | My Computer | Distributed Transaction Coordinator | Transaction Statistics).

To examine this important feature in action, set a breakpoint on the using statement where the TransactionScope is instantiated, then execute the next line by stepping forward once. Then, enter the following command

? System.Transactions.Transaction.Current 

in the Immediate window, which will cause the following output to be generated:

{System.Transactions.Ltm.LightweightTransaction}
    [System.Transactions.Ltm.LightweightTransaction]:
{System.Transactions.Ltm.LightweightTransaction}
    Consistent: false
    Identifier: {a611007b-25fe-4ef5-b92c-5382bbc365be}
    IsolationLevel: System.Transactions.IsolationLevel.Serializable
    Status: System.Transactions.TransactionStatus.Active
    TransactionTraceId: {System.Transactions.TransactionTraceIdentifier}

Notice that from this output you can see that this is a LightweightTransaction object whose Consistent flag is set to false (the default value). The default IsolationLevel is set to Serializable and its Identifier is displayed. The Identifier uniquely identifies the transaction from other transactions. The Status is set to Active because you are currently at a breakpoint in the middle of an active TransactionScope code block. If you examine the properties of the current transaction as it exists immediately after it steps through the line of code that sets the Consistent property, you'll see that the Consistent property's value would change to true.

It's easy to implement transactions using the TransactionScope. If an exception is thrown, the TransactionScope is disposed, the transaction is completed, the Consistent flag is still set to false, and it is rolled back automatically. Just make sure that you do not set the Consistent property to true until you're completely finished with all the actions that should be included in the transaction (in my example, this is just before the closing curly brace of the TransactionScope's using statement).

Transaction Options

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 be used to deviate from the default isolation level of serializable and employ another isolation level such as ReadCommitted. (You should keep in mind that the isolation level is merely a suggestion to the database. Most database engines will try to use the suggested level if possible.) The TransactionOptions class also has a TimeOut property which can be used to deviate from the default of one minute.

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. However, if you want to alter these settings, there are 14 additional overloaded constructors you can use. While the production release of the TransactionScopeOptions enumeration is not yet final, I have listed some of the more familiar values in Figure 2. These settings allow you to control how nested transaction scopes will respond to each other. Figure 3 shows an example of how to take the implicit local transaction from Figure 1 and modify its default settings using these techniques.

Figure 3 Implicit Local Transaction with Optional Settings

void btnImplicitLocalWithOptions_Click(object sender, EventArgs e)
{
  // Create the TransactionOptions object
  TransactionOptions oTranOpt = new TransactionOptions();

  // Set the Isolation Level
  oTranOpt.IsolationLevel =
    System.Transactions.IsolationLevel.ReadCommitted;

  // Set the timeout to be 2 minutes
  // Uses the (hours, minutes, seconds) constructor
  TimeSpan oTime = new TimeSpan(0, 2, 0);
  oTranOpt.Timeout = oTime;

  // Create the TransactionScope with the RequiresNew transaction 
  // setting and the TransactionOptions object I just created
  using (TransactionScope oTranScope = 
    new TransactionScope(TransactionScopeOption.RequiresNew, oTranOpt))
  {
    using (SqlConnection oCn1 = new SqlConnection(this.sCn1))
    {
      SqlCommand oCmd = new SqlCommand(this.sSQL, oCn1);
      oCn1.Open();
      oCmd.ExecuteNonQuery();
      oCn1.Close();
    }

    // Tell the transaction scope that the transaction is in a
    // consistent state and can be committed
    oTranScope.Consistent = true;

    // The following bracket completes and disposes the transaction
  }
}

Figure 2 Some TransactionScopeOptions

TransactionScopeOptions Description
Required If within a currently active transaction scope, this transaction scope will join it. Otherwise it will create its own transaction scope.
RequiresNew This transaction will create its own transaction scope.
Supports If within a currently active transaction scope, this transaction scope will join it. Otherwise no transaction scope will be created.
NotSupported No transaction scope will be created.

Distributed Implicit Transactions

Thus far I have shown how the TransactionScope object can be used to automatically include a single database connection in a transaction scope. Building on this ability by adding a second database connection to the transaction scope is as simple as including the database connection code in the same TransactionScope object's using statement. Figure 4 shows two SQL UPDATE statements being executed against two different databases, both inside of a single transaction scope. The first connection is made to a SQL Server 2005 version of the Northwind database. The second connection is made to a SQL Server 2000 version of Northwind.

Figure 4 Distributed Implicit Transaction

void btnImplicitDistributed_Click(object sender, EventArgs e)
{
    // Create the TransactionScope
    using (TransactionScope oTranScope = new TransactionScope())
    {
        // Open a connection to SQL Server 2005
        using (SqlConnection oCn1 = new SqlConnection(this.sCn1))
        {
            SqlCommand oCmd1 = new SqlCommand(this.sSQL, oCn1);
            oCn1.Open();
            // At this point, the connection is in the transaction scope, 
            // which is a lightweight transaction.
            oCmd1.ExecuteNonQuery();
            oCn1.Close();
        }
        // Open a connection to SQL Server 2000
        using (SqlConnection oCn2 = new SqlConnection(this.sCn2))
        {
            SqlCommand oCmd2 = new SqlCommand(this.sSQL, oCn2);
            oCn2.Open();
            // The connection is enlisted in the transaction scope, 
            // which is now promoted to a distributed transaction
            // controlled by MSDTC
            oCmd2.ExecuteNonQuery();
            oCn2.Close();
        }
        // Tell the transaction scope to commit when ready 
        oTranScope.Consistent = true;
        // The following bracket completes and disposes the transaction
    }
}

You could enhance this code by adding more connections, perhaps to Oracle or even MSMQ, all inside the same transaction scope. Or you could take this further and call a method that performs database actions.

These method calls would also be included in the transaction scope, as long as they are invoked within the transaction scope's using statement. If you called a method and it defined its own TransactionScope, depending on its TransactionScopeOptions setting (see Figure 2), it may join the current transaction scope or even start a new one. This gives you the flexibility to nest distributed transactions against different types of resource managers.

Smart Transactions with SQL Server 2005

Lightweight transactions can be promoted to full, distributed transactions when necessary, without any additional code. When the transaction scope is created, it creates a LightweightTransaction object. When a SQL Server 2005 connection is enlisted in the transaction scope, the transaction remains lightweight and is not under the control of the DTC. If a second connection is made to any database in the same transaction scope, the LightweightTransaction is promoted to a distributed transaction under the domain of the DTC.

SQL Server 2005 has been designed to work well with the System.Transactions features so that it does not use a distributed transaction and all of the overhead that comes along with it unless it requires a distributed transaction. So in the example in Figure 4, when the first connection is created against the SQL Server 2005 database, the transaction stays lightweight. But when it connects to the second database, regardless of the type, the transaction is promoted to a distributed transaction.

You can monitor the transaction being promoted if you step through the code. Step over the first connection that is opened. Then view the transaction statistics of the first database server and you will not see a distributed transaction being created. Next, step over the second connection that is being opened and go back and view the transaction statistics to see that a distributed transaction is now active.

If the first connection in the transaction scope is a SQL Server 2005 database, then the transaction is lightweight but can be delegated for promotion. When the subsequent database connections are opened, the transaction is promoted to a distributed transaction. However, if the first connection is opened against a SQL Server 2000 or Oracle database, note that the transaction won't be promoted because the resource managers for those databases do not support delegated transactions at this time. Therefore when the first connection's database does not support the delegated promotion of transactions, the transaction is created as a full, distributed transaction instead.

Alternatives

You can also create explicit transactions and not use the implicit, automatic transactions. For example, you could manually create a transaction and then tell your connections to explicitly enlist in the transaction scope. The problem with this technique is that it requires more code than the implicit examples and, more importantly, it increases the risk that the transaction will not complete properly if an exception is thrown (you have to manually code the exception handling logic). But just to show how this works, the code in Figure 5 creates a transaction, manually enlists a connection into the transaction, and then commits the transaction.

Figure 5 Explicit Transactions

// Create the Transaction (it is now lightweight)
// Note that for Beta 2 this has been changed
// to CommittableTransaction.
ICommittableTransaction oTran = Transaction.Create();

// Open a connection to SQL Server 2005
using (SqlConnection oCn1 = new SqlConnection(this.sCn1))
{
    SqlCommand oCmd1 = new SqlCommand(this.sSQL, oCn1);
    oCn1.Open();

    // The connection is not yet enlisted in the transaction scope 
    oCn1.EnlistTransaction();

    // Now the connection is enlisted in the transaction scope 
    oCmd1.ExecuteNonQuery((ITransaction)oTran);
    oCn1.Close();
}

// Tell the transaction to commit 
oTran.Commit();

Regardless of how you implement transactions, it is important to make sure that you get in and out of your transactions quickly. Transactions lock valuable resources. In general, it is a good idea to create a transaction just before you need it, open your connections, execute your action queries, and complete the transaction. You also should avoid running too much non-database code inside the transaction so that other resources aren't locked any longer than it is absolutely necessary.

These transactions also have an effect on connection pooling. When a connection is enlisted in an active transaction scope, even if the connection is closed, it is not fully released to the connection pool to be reused right away. The closed connection is still enlisted in the active transaction scope, so it remains in a special subpool until the active transaction is completed. However, once the transaction is completed, all connections will be released back to their appropriate connection pools.

Conclusion

The System.Transactions namespace can make the management of transactions quick and easy without the need to inherit from a ServicedComponent. One of the greatest features of the LightweightTransaction object is that it can determine if it needs to promote itself to a distributed transaction. The lightweight transactions are also a faster alternative to using the DTC for local transactions. The System.Transactions namespace is a step in the right direction to make it easier to handle transactions.

I want to point out that all of these features were written using the .NET Framework Beta 1 and SQL Server 2005 Beta 2. Therefore, I would expect that some changes to the interfaces or the constructors (the usual suspects) will occur before the final releases of these products. Thus you may need to modify your code once the final products are released.

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

John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive.