Performance Guideline: Use Promotable Transactions when Working with SQL Server 2005

  • Article

Here's the next .NET Framework 2.0 performance guideline for review from Prashant Bansode, Bhavin Raichura, Girisha Gadikere and Claudio Caldato. 

Use Promotable Transactions when Working with SQL Server 2005

Applies to

  • .NET 2.0

What to Do
Use new .Net 2.0 System.Transactions API for controlling transactions in managed code when working with SQL Server 2005

Why
System.Transactions API gives flexibility to shift between local database server transactions and distributed database server transactions. The Systems.Transactions API, when used with SQL Server 2005, uses the Promotable Transactions feature through the Lightweight Transactions Manager. It does not create a distributed transaction when not required, resulting in improved performance.

Note If System.Transactions API is used to manage local transactions on SQL Server 2000, the local transaction is automatically promoted to a distributed transaction managed by MSDTC. SQL Server 2000 does not support Promotable Transactions.

When
If it is required to control transactions in managed code while working with SQL Server 2005, use Systems.Transactions API for improving performance and flexibility.

This guideline should not be used when working with SQL Server 2000.

How
The following information is for using "Promotable Transactions".

While using the System.Transaction API is to define Transaction Scope using TransactionScope Class, it defines the boundary for the required transactions.

 ...
 using (TransactionScope scope = new TransactionScope())
 {
    ...
    // Open Connection and Execute Statements
    ...
    scope.Complete();
 }
 ...

Within Transaction Scope block use normal ADO.NET code for executing the statements using Connection, Command and Execute methods. If the transaction is successful, invoke TransactionScope.Complete method. If the transaction is unsuccessful, the transaction will be automatically rolled back as it will not execute TransactionScope.Complete in the program flow.

Problem Example
A web application for Online Shopping, provides a user interface to purchase items. Once the items are purchased, the item entry should be added for billing in a Billing database table in a SQL server 2005 database. At the same time, the stock of the item should be reduced by the number of units sold in an Item Quantity database table. The entire operation needs to be performed in single transaction to maintain data integrity.

The application follows a traditional approach of using SqlTransaction API which enforces only local transactions. If distributed database transactions are required, the code has to be changed and compiled again. This breaks the principle of flexibility and agility in the design. The following code illustrates the problem, which forces local transactions and compromises flexibility to change it to distributed transactions:

 ...
 using (SqlConnection conn = new SqlConnection(dbConnStr))
 {
     conn.Open();

     SqlCommand cmd = conn.CreateCommand();
     SqlTransaction trans;

     // Start a local transaction.
     trans = conn.BeginTransaction("NewTransaction");

     cmd.Connection = conn;
     cmd.Transaction = transaction;

     try
     {
          cmd.CommandText = "Insert Statement...";
          cmd.ExecuteNonQuery();
          cmd.CommandText = "Update Statement...";
          cmd.ExecuteNonQuery();

          // Attempt to commit the transaction.
          trans.Commit();
      }
      catch (Exception ex)
      {
            // Attempt to roll back the transaction.
            try
            {
                trans.Rollback();
            }
            catch (Exception ex2)
            {
                // handle any errors that may have occurred            
            }
        }
 }
 ...

Instead, if the new System.Transactions API is used, it supports distributed transactions also.

Solution Example
A web application for Online Shopping, provides a user interface to purchase items. Once the items are purchased, the item entry should be added for billing in the Billing database table on SQL server 2005. At the same time, the stock of the item should be reduced by the number of units sold in the Item Quantity database table. The entire operation needs to be performed in single transaction to maintain data integrity. The new System.Transactions API is used to provide flexibility without compromise on performance. System.Transactions API has a feature of Promotable Transactions when used with SQL Server 2005. It determines the need of using distributed transactions or local transactions at runtime for improved performance:

 ...
 using (TransactionScope scope = new TransactionScope())
 {
    using (SqlConnection conn = new SqlConnection(dbConnStr))
    {
        SqlCommand cmd1 = conn.CreateCommand();
        cmd1.CommandText = "Insert Statement....";

        SqlCommand cmd2 = conn.CreateCommand();
        cmd2.CommandText = "Update Statement....";

        conn.Open();
        cmd1.ExecuteNonQuery();

        cmd2.ExecuteNonQuery():
        conn.Close();
    }
     scope.Complete();
 }
 ...

Additional Resources