Skip navigation

The System.Transactions Namespace

Manage Distributed Transactions and Ensure Data Stays in Sync across Multiple Databases

asp:Feature

LANGUAGES: C# | VB.NET

ASP.NET VERSIONS: 2.0

 

The System.Transactions Namespace

Manage Distributed Transactions and Ensure Data Stays in Sync across Multiple Databases

 

By Jack Whitney

 

A transaction that needs to run across more than one database connection is a distributed transaction. In a distributed transaction, all the databases must commit their updates on behalf of the transaction, or none of them should. It is not practical for an application to coordinate a distributed transaction by itself. The Distributed Transaction Coordinator (DTC) service provides distributed transaction coordination capabilities to applications.

 

In the .NET Framework version 1.x, developers could access the capabilities of the DTC by using .NET Enterprise Services, which is the service technology used to access the COM+ services with managed .NET components. The .NET Framework version 2.0 includes a transaction framework, accessible through the System.Transactions namespace, which provides a significantly improved way to access the capabilities of the DTC. Windows Communication Foundation (WCF) in the .NET Framework version 3.0 includes a transactional programming model that accesses the capabilities of the DTC; however, it can only be used by WCF services.

 

TransactionScope

The System.Transactions framework is fully integrated with ADO.NET. There are no changes to how you use ADO.NET in order to implement a distributed transaction. To create a transaction you instantiate a TransactionScope object. Internally, in its constructor, the TransactionScope object creates a transaction and assigns it to the System.Transactions.Transaction.Current property. This transaction is called the current or ambient transaction. TransactionScope is a disposable object and the transaction ends when the Dispose method is called. Any ADO.NET connections opened after the TransactionScope object is instantiated and before it is disposed will automatically be part of the transaction. This works when a single database connection is opened or when multiple database connections are opened. If the TransactionScope s Complete method is called before the Dispose method is called, the transaction will be committed; otherwise, it will be rolled back.

 

The example in Figure 1 employs a using statement to ensure that the TransactionScope s Dispose method is called. Failure to commit or roll back pending transactions can seriously hurt performance. If you do not use a using statement, you should perform all work in a try block and explicitly call the Dispose method in the finally block.

 

using (TransactionScope ts = new TransactionScope()

{

 // Connect to first database

 using (SqlConnection cn = new SqlConnection(

   inventoryConnString))

 {

   SqlCommand cmd = new SqlCommand(sqlUpdate, cn);

   cn.Open();

   cmd.ExecuteNonQuery();

 }

 // Connect to second database

 using (SqlConnection cn = new SqlConnection(

   ordersConnString))

 {

   SqlCommand cmd = new SqlCommand(sqlInsert, cn);

   cn.Open();

   cmd.ExecuteNonQuery();

 }

 ts.Complete();

}

Figure 1: A distributed transaction.

 

It is good practice to put the Complete method call as the last statement in a using block. This way if an exception is thrown, the Dispose method will be called without the Complete method being called, and the transaction will be rolled back. An exception will be thrown if the Complete method is called more than once in the same transaction scope. This was done by design to discourage developers from doing additional database updates after calling the Complete method.

 

Nested TransactionScope Objects

TransactionScope objects can be nested both directly and indirectly through method calls. Various constructors of the TransactionScope class take a TransactionScopeOption enumeration as a parameter, which is used to define whether the scope takes part in the ambient transaction, creates a new transaction, or is not part of any transaction. The TransactionScopeOption enumeration defines the three values shown in Figure 2.

 

Value

Description

Required

If there is an ambient transaction, the scope will become part of that transaction. If there is no ambient transaction, the scope will create a new transaction.

RequiresNew

The scope will create a new transaction.

Suppress

The scope will not be part of any transaction. The System.Transactions.Transaction.Current property will equal null.

Figure 2: TransactionScopeOption enumeration values.

 

TransactionScopeOption.Required is the value used when you call one of the TransactionScope constructors that doesn t take a TransactionScopeOption parameter.

 

In a transaction that contains nested TransactionScope objects, the Complete method must be called for all of the TransactionScope objects in order for the transaction to be committed. The top-most scope is called the root scope. The transaction ends when the root scope is disposed. Listing One shows a distributed transaction with nested TransactionScope objects that adds an order to an Orders database if the quantity of stock on hand is successfully updated in an Inventory database. The download code accompanying this article includes a simple application that executes this transaction against two sample databases (see end of article for download details).

 

TransactionOptions

By default, the TransactionScope object creates a transaction with an isolation level of serializable and a timeout period of 60 seconds. Various constructors of the TransactionScope class take a TransactionOptions structure as a parameter. The TransactionOptions structure contains the two public properties shown in Figure 3.

 

Properties

Description

IsolationLevel

An IsolationLevel enumeration that specifies the isolation

level for the transaction.

Timeout

A TimeSpan value that specifies the timeout period for the

transaction.

Figure 3: The public TransactionOptions properties.

 

In a transaction that contains nested TransactionScope objects, all scopes must specify the same isolation level; otherwise, an exception will be thrown. If all nested TransactionScope objects in a transaction do not specify the same timeout period, the shortest timeout period will be used (see Figure 4).

 

TransactionOptions tOpt = new TransactionOptions();

tOpt.IsolationLevel =

 System.Transactions.IsolationLevel.RepeatableRead;

// Set the timeout to 30 seconds

tOpt.Timeout = new TimeSpan(0, 0, 30);

using (TransactionScope ts = new TransactionScope(

 TransactionScopeOption.Required, tOpt))

{

   using (SqlConnection cn = new SqlConnection(

     inventoryConnString))

   {

     SqlCommand cmd = new SqlCommand(sqlUpdate, cn);

     cn.Open();

     cmd.ExecuteNonQuery();

   }

   ts.Complete();

}

Figure 4: Create a transaction with a specified isolation level and timeout period.

 

Nested Native Database Transactions

Do not call stored procedures that create native database transactions when using System.Transactions to manage a distributed transaction, as this would bypass the distributed transaction management provided by System.Transactions and would lead to inconsistent results (for example, stored procedures that use the T-SQL BEGIN TRANSACTION and COMMIT TRANSACTION statements). ADO.NET transactions support calling stored procedures that create native database transactions, so be careful using stored procedures that were written to be used with ADO.NET transactions.

 

Using System.Transactions in ASP.NET

System.Transactions supports partially trusted callers and is marked with the AllowPartiallyTrustedCallers attribute. DistributedTransactionPermission is demanded when the transaction is coordinated by the DTC. To enable distributed transaction support in an ASP.NET partial-trust environment, you must grant the DistributedTransactionPermission.

 

Performance

Given the ease and intuitiveness of managing transactions with System.Transactions, you may be asking why not use System.Transactions to manage all database transactions, including those that only access a single database. There is a performance consideration. If the database does not support transaction promotion, all transactions, including transactions that only access a single database, will be handled as if they were distributed transactions under the coordination of the DTC. A transaction coordinated by the DTC will not perform as well as an ADO.NET transaction or native database transaction. The only database that currently supports transaction promotion is SQL Server 2005. In the case of SQL Server 2005, the first database connection that is opened will enlist the support of the Lightweight Transaction Manager (LTM). When and if a second database connection is opened, the transaction will be promoted to a distributed transaction and coordination will be switched to the DTC. When the LTM is used, the transaction performs as well as a native database transaction.

 

Conclusion

.NET Enterprise Services in the .NET Framework version 2.0 implements its transaction feature internally using System.Transactions. You can implement distributed transactions using either .NET Enterprise Services or System.Transactions with essentially the same performance; however, if you only need the transaction feature in .NET Enterprise Services, System.Transactions is the way to go. If you are developing WCF services using the .NET Framework version 3.0, you should consider using the transactional programming model provided by WCF. Even if you are developing WCF services, there may be times when you will use System.Transactions, such as when developing .NET objects that are called downstream by a WCF service, because downstream objects cannot take advantage of the transactional programming model provided by WCF.

 

Sample code accompanying this article is available for download.

 

Jack Whitney is a software developer and .NET enthusiast. He welcomes your e-mails at mailto:[email protected].

 

Begin Listing One A distributed transaction with nested TransactionScope objects

public bool AddOrder()

{

 bool status = false;

 using (TransactionScope ts = new TransactionScope(

   TransactionScopeOption.RequiresNew))

 {

    // Create the order header

   int orderId = AddOrderHeader(DateTime.Now);

   // Add 3 line items to the order

   if (orderId > 0 && AddOrderLine(orderId, "OSC-1", 8)

     && AddOrderLine(orderId, "OSC-2", 4)

     && AddOrderLine(orderId, "OSC-3", 3))

   {

     status = true;

     ts.Complete();

   }

 }

 return status;

}

public int AddOrderHeader(DateTime orderDate)

{

 int orderId = 0;

 using (TransactionScope ts = new TransactionScope())

 {

   string sql = "INSERT INTO OrderHeader(OrderDate) "

     + "VALUES (@orderDate); SELECT @@IDENTITY;";

   string ordersConnString =

     ConfigurationManager.ConnectionStrings[

     "OrdersConnString"].ConnectionString;

   using (SqlConnection cn = new SqlConnection(

     ordersConnString))

   {

     SqlCommand cmd = new SqlCommand(sql, cn);

     cmd.Parameters.Add(new SqlParameter("@OrderDate",

       SqlDbType.DateTime)).Value = orderDate;

     cn.Open();

     orderId = Convert.ToInt32(cmd.ExecuteScalar());

     ts.Complete();

   }

 }

 return orderId;

}

public bool AddOrderLine(int orderId, string itemId,

 int quantity)

{

 bool status = false;

 using (TransactionScope ts = new TransactionScope())

 {

   string sql = "INSERT INTO OrderLine(OrderId, ItemId, "

     + "Quantity) VALUES (@OrderId, @ItemId, @Quantity)";

   string ordersConnString =

     ConfigurationManager.ConnectionStrings[

     "OrdersConnString"].ConnectionString;

   // True if enough stock is on hand

   if (UpdateInventory(itemId, quantity))

   {

     using (SqlConnection cn = new SqlConnection(

       ordersConnString))

     {

       SqlCommand cmd = new SqlCommand(sql, cn);

       cmd.Parameters.Add(new SqlParameter("@OrderId",

         SqlDbType.Int)).Value = orderId;

       cmd.Parameters.Add(new SqlParameter("@ItemId",

         SqlDbType.VarChar)).Value = itemId;

       cmd.Parameters.Add(new SqlParameter("@Quantity",

         SqlDbType.Int)).Value = quantity;

       cn.Open();

       cmd.ExecuteNonQuery();

       status = true;

       ts.Complete();

     }

   }

 }

 return status;

}

public bool UpdateInventory(string itemId, int quantity)

{

 bool status = false;

 using (TransactionScope ts = new TransactionScope())

 {

   // Update the quantity if enough stock is on hand

   string sql = "UPDATE Inventory "

     + "SET Quantity = Quantity - @Quantity "

     + "WHERE ItemId = @ItemId and "

     + "Quantity - @Quantity >= 0";

   string inventoryConnString =

     ConfigurationManager.ConnectionStrings[

     "InventoryConnString"].ConnectionString;

   int rowsReturned = 0;

   using (SqlConnection cn = new SqlConnection(

     inventoryConnString))

   {

     SqlCommand cmd = new SqlCommand(sql, cn);

     cmd.Parameters.Add(new SqlParameter("@Quantity",

       SqlDbType.Int)).Value = quantity;

     cmd.Parameters.Add(new SqlParameter("@ItemId",

       SqlDbType.VarChar)).Value = itemId;

     cn.Open();

     rowsReturned = cmd.ExecuteNonQuery();

   }

   if (rowsReturned == 1)

   {

     status = true;

     // The quantity was updated, so can commit

     ts.Complete();

   }

 }

 return status;

}

End Listing One

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish