Adrian Anttila

Implementing a Custom Transaction Manager

Introduction

With the 2.0 release of the .NET Framework, Microsoft introduced the System.Transactions namespace, which allows you to create and participate in a transaction (local or distributed) with one or multiple participants. The advantage of using System.Transactions over System.EnterpriseServices is that System.Transactions will keep the transaction lightweight (local) until resources are enlisted in the transaction that require it to be promoted to a more heavyweight version (distributed). This allows the TransactionScope to operate within a database transaction without having to engage the Microsoft Distributed Transaction Coordinator (MSDTC). The end result is that transactions that don't need the MSDTC won't acquire more resources than necessary, which means faster transactions and fewer system resources.

While System.Transactions contains some pretty helpful functionality, there are situations where the automatic elevation works as intended, but is not desireable. One such situation involves a (local) transaction using multiple, non-concurrent connections to a single database; for example, two different insert statements issued against the database through code in a serialized fashion. This situation could easily present itself when the data access is performed through a data layer. The SqlConnection class is smart enough to enlist in a transaction automatically, if one is present, but the problem is that when a second connection is opened and it enlists itself with the current transaction, the transaction is promoted to a distributed transaction that leverages the MSDTC. The only solution to this scenario is to open a connection, start a transaction, and pass the corresponding SqlConnection and SqlTransaction instances to each method that requires database access.

It seems that having to create and manage references to the database connection and transaction are the only way to go, but what if there is another way? Luckily, there is an alternative. However, it involves some slightly advanced concepts involving thread local storage and a custom data access implementation.

Background

Each application that runs within your operating system (Windows, Mac OS, etc) is executed within a process. Opening Task Manager in Windows (by right-clicking on the task bar) lists all of your currently running processes.

Within each process, the application can have one or more points of execution, which can be running concurrently. Each point of execution is called a thread. On hyperthreaded, multi-CPU, or multi-core PCs and servers, two or more threads can be executed at the same time. On single-CPU or single-core PCs and servers, only one thread can be executed at a time. In either scenario, threads can be interrupted, paused, or preempted (usually because of priority) by other threads. When a thread is put on "hold" by the operating system and a different thread begins or continues execution, the current active thread is said to have received a timeslice. A timeslice is usually a brief period of time (milliseconds) in which a thread is being executed by a CPU.

While a thread is executing, it is allocated storage specific to the thread. This storage is called thread local storage (TLS). When thread execution finishes, or stops, it is deallocated along with its TLS.

Using the code

Let's walk through an example, where we insert and select data from the AdventureWorks.Person.ContactType table. In order to implement the transaction support, there are a few classes that we'll need:

• TransactionBase - Provides functionality for all transaction classes
• LocalTransaction - Provides functionality for the database transaction
• SqlClientUtility - Provides functionality for executing queries against a SQL Server database
• ContactType - Provides fields and methods for accessing data found in the AdventureWorks.Person.ContactType table
• Program - A console application used to run everything

Note: If you want to skip ahead to see what the end result looks like, click here.

The TransactionBase class has 3 abstract methods: Commit, Rollback, and Dispose. These abstract methods insure that any classes deriving from TransactionBase will support the necessary functionality for transactions. It implements the IDisposable pattern so that it can be used in conjunction with C#'s using keyword.

The LocalTransaction class provides an implementation of the TransactionBase class that supports local SQL Server transactions. By default, the IsolationLevel is ReadCommitted, and the connection string name is "ConnectionString". The class has properties for the SqlConnection and SqlTransaction instances used in the transaction. Lastly, LocalTransaction stores an instance of itself in thread local storage, and exposes a static property that provides access to the instance. Using thread local storage in this manner allows other classes on the same thread to access the current transaction by calling LocalTransaction.Current. We'll see an example of this a little later.

Thread.GetNamedDataSlot returns a reference to a portion of the local thread storage. Using Thread.GetNamedDataSlot in conjunction with Thread.SetData is how you items are placed in thread local storage.

Thread.SetData(Thread.GetNamedDataSlot("LocalTransaction"), this);

To access items found in thread local storage, call Thread.GetData and specify the data slot with Thread.GetNamedDataSlot.

public static LocalTransaction Current {
    get { return (LocalTransaction) Thread.GetData(Thread.GetNamedDataSlot("LocalTransaction")); }
}

The SqlClientUtility class provides functionality for executing stored procedures within a SQL Server database. While it contains quite a few functions, we'll just focus on the functionality that checks for a current transaction, and ignore the rest. Something to note is that if a connection and transaction aren't specified, and there is no local transaction, a new connection is created so the query can be exeucted. This allows support for both transactional and non-transactional database access.

if (connection == null) {
    if (LocalTransaction.Current != null) {
        connection = LocalTransaction.Current.Connection;
    }
}

if (transaction == null) {
    if (LocalTransaction.Current != null) {
        transaction = LocalTransaction.Current.Transaction;
    }
}

if (connection == null) {
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (connection = new SqlConnection(connectionString)) {
        connection.Open();
        // Do something here using the newly created SqlConnection
    }
} else {
    // Do the same something found above, but with the previously existing SqlConnection and SqlTransaction
}

Because of this implementation, we're free to overload all SqlClientUtility functions so we can just specify a stored procedure name, and optionally, any required parameters. Here's what a call would look like:

SqlParameter[] parameters = new SqlParameter[] {
    new SqlParameter("@Name", name),
    new SqlParameter("@ModifiedDate", modifiedDate)
};
contactTypeID = Convert.ToInt32(SqlClientUtility.ExecuteScalar("ContactTypeInsert", parameters));

Notice that a SqlConnection and SqlTransaction weren't specified. If this block exists within a LocalTransaction block, it will automatically use the existing connection and transaction.

Finally, to put it all together, the Program class creates a ContactType, starts a transaction, saves it to the database, and then retrieves it. To make sure that the transaction is working correctly, we'll roll the transaction back, which should prevent our activity from being committed to the database.

using (LocalTransaction localTransaction = new LocalTransaction()) {
    ContactType newContactType = new ContactType();
    newContactType.Name = "Test Contact Type";
    newContactType.Save();
    
    ContactType existingContactType = ContactType.Get(newContactType.ContactTypeID);
    
    localTransaction.Rollback();
}

Points of Interest

• Thread local storage is the only to get a reference to the current connection/transaction without passing a reference.
• System.Transactions is a nice addition to the .NET Framework, but doesn't work optimally when multiple connections are made to the same database.
• The LocalTransaction class doesn't allow multiple connections within the same transaction, but it does provide a means to reuse the transaction's SqlConnection and SqlTransaction references.
• Any data access code using SqlClientUtility doesn't have to specify if it should be in a transaction or not, it will just participate automatically.