Manual ADO.NET Transactions and TLS
I have come across several business and data access layer components which have method overloads taking in SqlTransaction (or IDbTransaction) references. The need here is to facilitate ADO.NET manual transactions, where database operations scattered across several components would need to be enlisted under the same transaction. Let’s assume for now that we do not want to avail the automatic transaction services of Enterprise Services or the DTC; performance being one reason, the unavoidable need to derive from System.EnterpriseServices.ServicedComponent being another. The obvious pain in the manual transaction scheme in our case is the leakage of ADO.NET objects (SqlTransaction) into the Business layer, which ideally, shouldn’t be dealing with ADO.NET specific objects at all.
So, what could be the solution to this problem? I thought TLS (Thread Local Storage) could be put to good avail in this case. My idea involves the following steps:
1) Create a helper class (call it TransactionUtils) with BeginTransaction, Commit and RollBack methods which do the work of opening and closing connections and storing transaction (SqlTransaction, for example) objects in the current thread’s named data slot. This helper would be used by the business component which forms the root of the transaction.
2) Change the data access helper (or the DAAB) to check the current thread’s slot for a transaction object and use it if it exists (append the current transaction to to the command object). You could consider creating overloads which are ‘TLS aware’.
This is what TransactionHelper would look like:
public class TransactionHelper
{
// To ensure TransactionHelper is not instantiated. All methods are static
private TransactionHelper()
{
}
// Name of the TLS data slot.
private const string TLS_SLOT_NAME = "IDbTransactionDataSlot";
// Returns a boolean value which indicates
// whether current thread is in a transactional context
public static bool IsInTransaction()
{
return (GetTransactionFromContext() != null);
}
// Begins an ADO.NET transaction with the specified isolation level.
// Creates a TLS data slot where the transaction object would be stored
public static void BeginTransaction(IsolationLevel isolationLevel, string connectionString)
{
//To clear off the transaction slot, if in use
FreeResources(GetTransactionFromContext());
IDbConnection conn = GetConnection(connectionString);
IDbTransaction trans = conn.BeginTransaction(isolationLevel);
LocalDataStoreSlot slot = Thread.AllocateNamedDataSlot(TLS_SLOT_NAME);
Thread.SetData(slot, trans);
}
// Begins an ADO.NET transaction. Creates a TLS data slot where the
// transaction object would be stored
public static void BeginTransaction(string connectionString)
{
//To clear off the transaction slot, if in use
FreeResources(GetTransactionFromContext());
IDbConnection conn = GetConnection(connectionString);
IDbTransaction trans = conn.BeginTransaction();
LocalDataStoreSlot slot = Thread.AllocateNamedDataSlot(TLS_SLOT_NAME);
Thread.SetData(slot, trans);
}
// Commits the current transaction and frees the TLS data slot
public static void Commit()
{
IDbTransaction trans = GetTransactionFromContext();
IDbConnection conn = trans.Connection;
trans.Commit();
conn.Dispose();
FreeResources(trans);
}
///
/// Rolls back the current transaction and frees the TLS data slot
///
public static void Rollback()
{
IDbTransaction trans = GetTransactionFromContext();
IDbConnection conn = trans.Connection;
trans.Rollback();
conn.Dispose();
FreeResources(trans);
}
// Gets an IDbTransaction object from the TLS
internal static IDbTransaction GetTransactionFromContext()
{
LocalDataStoreSlot slot = Thread.GetNamedDataSlot(TLS_SLOT_NAME);
return Thread.GetData(slot) as IDbTransaction;
}
// Disposes the transaction and clears the TLS slot
private static void FreeResources(IDbTransaction trans)
{
if(trans != null)
{
trans.Dispose();
}
Thread.FreeNamedDataSlot(TLS_SLOT_NAME);
}
// Gets a connection object
private static IDbConnection GetConnection(string connectionString)
{
IDbConnection conn = SqlHelper.GetConnection(connectionString);
conn.Open();
return conn;
}
}
So, the snippet of a business component which uses the TransactionHelper would look something like:
class MyBizComp
{
public void DoSomeOperation()
{
try
{
TransactionHelper.BeginTransaction(connectionString);
// Call other biz components, they would get enlisted within
// the same transaction
TransactionHelper.Commit();
}
catch
{
TransactionHelper.Rollback();
}
finally
{
// Cleanup
}
}
}
So, we have a solution here, which is not that elegant, but atleast, sheilds the business layer from the nuances of ADO.NET. Note that the assumption here is that all the other business components eventually call those methods in the data access layer which are context (TLS) aware.
Two important things to note regarding this approach:
1) The connection is kept open (behind the scenes) during the period between the BeginTran and Commit, and therefore during the execution of all the business components that are enlisted in a single transaction. So, it is imperative to keep transaction bracket as small as possible to improve concurrency.
2) This methodology does not work in a multithreaded scenario, where different components in the call chain happen to execute on different threads. Obviously, because TLS is thread specific.
Another similar implementation you may want to look into is the transaction helper in Juval Lowy’s site, IDesign.Net, where you have a helper class which does something similar, but wraps Enterprise Services.
Come .NET 2.0, System.Transactions provides an elegant way to “scope” a transaction in the business layer. Not so surprisingly, the current transaction (Ambient transaction) is persisted in the TLS. System.Transactions is one of the coolest new things in .NET 2.0 and will be the subject of another post altogether.