LocalTranGuard - a TransactionScope Fix/Wrapper for LocalTransactions

• Download source code - 25.7 KB

Introduction

I stumbled on an MS template for creating a DAL and a BLL. The code was nice and straightforward. Everything was OK with it. Unfortunately, I began to use this structure with TransactionScope.

As a lazy programmer, I like to use DataTableAdapter classes. They open and close connections for me. I "copied" the style of the VS generated code, and began to create "lightweight" DAL classes: 1 table = 1 insert, 1 update, 1 delete, and N select commands. I mostly used GetDataRowById (where PK = @PK), then modified the row, and finally did an Update(DataRow). To avoid dirty data, this style of database update needs transactions to lock data after it is read (to avoid modification or/and dirty read by another thread). So the GetDataRowById and Update must use the same transaction.

And, here comes .NET2.0 TransactionScope.

TransactionScope automatically passes a Transaction object for each IDbCommand. So, I only have to put the GetDataRowById and Update in a TransactionScope block, and that's all - I do not have to create a transaction object - it's done automatically. Before leaving the TransactionScope block, I have to call TransactionScope.Commit() if everything is OK and changes can be committed.

Nice, isn't it?

In the hell

I began to use this coding pattern described above. In a TransactionScope, I use TableAdapter.Fill() and SqlCommand.ExecuteNonQuery(). In both cases, a Connection is opened and closed.

The code worked perfectly...until it was deployed on the production server (with a separate application and a database server). From that moment, I always got an exception:

System.Transactions.TransactionManagerCommunicationException: 
Communication with the underlying transaction manager has failed.
   ---> System.Runtime.InteropServices.COMException (0x80004005): 
   Error HRESULT E_FAIL has been returned from a call to a COM component.
at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(
   UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier,
   Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, 
   ITransactionShim& transactionShim)
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(
                                          Byte[] propagationToken)
--- End of inner exception stack trace ---
at System.Transactions.TransactionInterop.
   GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCookie(
                       Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, 
                            DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Notice: Locally everything works fine. The problems arise when the DB server is on another machine - or in a real testing environment.

The research

I began to look for the reason behind this. On the forums, I only found articles which describe how to set up servers to allow MSDTC. But, my code uses the same connection string, so there was no need for MSDTC.

Finally, I' found a super article about TransactionScope. There's also an article which describes transaction escalation.

An escalation that results in the System.Transactions infrastructure transferring the ownership of the transaction to MSDTC happens when:

• At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
• At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with SQL Server 2005 does not cause a transaction to be promoted. However, whenever you open a second connection to a SQL Server 2005 database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.
• A request to "marshal" the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a transaction as a parameter, or you can try to access a remote transactional-serviced component. This serializes the transaction object, and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.

Unfortunately, escalation also happens when two connections (even with identical connection strings) are opened.

What to do?

...if you have a four man-month project, and has no time for modifications?

• Configure MSDTC (performance loss, unpredictable errors, problems at smart-client - special configuration on the client side).
• Avoid transaction escalation.

The second one is what I needed. But how do I avoid changes in the code (in DAL)?

So, I continued with my research. For you, I've created a list of interesting articles where you can get more info on the given problem. All of them are included in this article. Here's a link to the Microsoft description: Features Provided by System.Transactions.

I've tried to create my own transaction manager (TransactionScope in .NET 1.1). Actually, the sample I found uses COM+, so I kept getting errors for some reason.

The next step is to somehow avoid the escalation of a local transaction to the distributed one. I've read about 60 pages on this theme - but no real solution.

Almost what I needed

The closest solution was: Implementing a Custom Transaction Manager.

This solution took care of the IDbConnection object, avoided multiple opening and closing inside a transaction-scope, and for the given local-scope, it always returned the same connection (thus avoiding opening and closing). The whole idea was to store the IDbConnection in the thread local storage.

Still, the code provided there would result in a lot of changes to my BLL/DAL. So I had to solve my problem another way.

The solution

Part 1 - Solve TransactionScope

If a local transaction is initialized, create a connection, open it, and store it in the current thread local storage (TLS). When the DAL needs a connection and a connection exists in the TLS, return that connection. Before the transaction-scope ends, close the connection and clear the connection from TLS.

Part 2 - Solve the DAL calls outside TransactionScope

Outside the transaction scope, a new connection is created and it's not stored in the TLS. This is cool... The code is in the Fix.cs file.

As you can see, we had to create a static class (LocalConnHelper) and store the connection string there. This was necessary, because the LocalTranGuard needed to initialize connections inside its constructor (other type of behaviors would lead to more changes).

With the above design, the changes are minimal - compare OriginalDALandBLL.cs and ModifiedDALandBLL.cs. Additionally, we have to initialize the LocalConnHelper before any DB activity, but that's all.

Note: If you want, you can remove GetFbConnection and IsServer from LocalConnectionHelper. I needed these because I use Firebird and SQL DAL.

To use this code:

1. copy Fix.cs to your solution, uncomment conn.Open() in the ConnectionInit() function - I had to remove the connection opening for testing purposes.
2. Set SonnectionString in the LocalConnHelper class.

The solution also handles nested transactions!

The code

using System;
using System.Threading;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using FirebirdSql.Data.FirebirdClient;
using System.Transactions;
namespace MSDTCx.Fix
{

    /// <summary>
    /// This class returns a connection to a given database.
    /// If there's an ongoing local transaction, the connection used
    /// in the transaction will be returned;
    /// otherwise a new connection will be created.
    /// This class can be STATIC, connections are stored per thread-basis.
    /// </summary>
    public static class LocalConnHelper
    {
        public static bool IsServer;
        public static string ConnectionString;

        /*
         * NOTE: i could also return IDbConnection, but then explicit
         * casting would be necessary on some places.
         */
        public static SqlConnection GetSqlConnection
        {
            get
            {
                object localTransConn = 
                  Thread.GetData(Thread.GetNamedDataSlot("LocalTransaction"));
                if (localTransConn == null)
                    return new SqlConnection(ConnectionString);
                else return (SqlConnection)localTransConn;
            }
        }

        public static FbConnection GetFbConnection
        {
            get
            {
                object localTransConn = 
                  Thread.GetData(Thread.GetNamedDataSlot("LocalTransaction"));
                if (localTransConn == null)
                    return new FbConnection(ConnectionString);
                else return (FbConnection)localTransConn;
            }
        }

        public static IDbConnection GetIDbConnection
        {
            get
            {
                object localTransConn = 
                  Thread.GetData(Thread.GetNamedDataSlot("LocalTransaction"));


                if (localTransConn == null)
                {
                    if (IsServer)
                    {
                        return GetSqlConnection;
                    }
                    else
                    {
                        return GetFbConnection;
                    }
                }
                else
                {
                    return (IDbConnection)localTransConn;
                }
            }
        }
    }

    /// <summary>
    /// This class serves as an extension of TransactioScope.
    /// 
    /// TransactioScope will try to create MSDTC if 2 database connections
    /// (even if 100% identical) are opened inside a transaction.
    /// The mentioned side-effect occours when using DataTableAdapters
    /// which opens and closes connection, if no connection is opened previously.
    /// This side effect can be avoided if we open the database before
    /// executing a commnand in the TransactionScope block.
    /// Because we have more than 1 DAL with it's internal conection objects
    /// (and code is already tested) changing code is not recommended.
    /// 
    /// TransactionScope can be "changed" to this class.
    /// When class is initialized, a TransactionScope will be created
    /// and a connection will be opened.
    /// Any DAL (which get's it's connection through the LocalConnHelper)
    /// will use the connection created at this initialization.
    /// Upon Dispose the TransactionScope is also cleared Up.
    /// 
    /// Supported:
    /// Overloaded constructors (2), Complete method (1)
    /// </summary>
    public class LocalTranGuard : IDisposable
    {
        bool isNested;
        ConnectionState stateAtInit;
        TransactionScope transactionScope;

        public LocalTranGuard()
        {
            // create transaction-scope
            transactionScope = new TransactionScope();

            // init connection
            ConnectionInit();
        }

        public LocalTranGuard(TransactionScopeOption scopeOptions, 
                              TransactionOptions options)
        {
            // create transaction-scope
            transactionScope = new TransactionScope(scopeOptions, options);

            // init connection
            ConnectionInit();
        }

        private void ConnectionInit()
        {
            // is this nested ?
            isNested = (Thread.GetData(Thread.GetNamedDataSlot(
                               "LocalTransaction")) != null);

            // get new connection
            IDbConnection conn = LocalConnHelper.GetIDbConnection;

            // store it
            Thread.SetData(Thread.GetNamedDataSlot("LocalTransaction"), conn);

            // open it
            stateAtInit = conn.State;
            if (stateAtInit != ConnectionState.Open) conn.Open();
        }

        private void ConnectionFinish()
        {
            // close connection
            IDbConnection conn = LocalConnHelper.GetIDbConnection;
            if (conn.State == ConnectionState.Open && 
                stateAtInit != ConnectionState.Open) conn.Close();

            // remove it
            if (isNested == false)
            {
                Thread.SetData(Thread.GetNamedDataSlot(
                               "LocalTransaction"), null);
            }
        }

        public void Complete()
        {
            transactionScope.Complete();
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                // finish connection
                ConnectionFinish();

                // finish transaction
                transactionScope.Dispose();
            }
        }

        ~LocalTranGuard()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

Tests

The upper buttons are for testing transactional behaviour with the original TransactionScope and with the special (newly created) transaction scope.

The buttons TLS1 and TLS2 test the new method:

• TLS1 - testing TLS with a static helper class (quick test for strings instead of IDbConnection)
• TLS2 - testing LocalTranGuard with LocalConnHelper (full test, code should work even with sub-transactions)

Both test buttons will write information into the textbox. You'll get the ID of the thread and the data (or its hash) written to the text-box. For the same ID, the data you'll get displayed should be the same; if not, then the code is not functional :-)

Testing with the ModifiedDALandBLL is not included, but you have to modify only the 1. and 2. regions a bit - you can do it on your own.

My conclusion!?

The problem was fixed, so I'm happy - but this is not how DAL/BLL should be created :-)

It's time for someone to write more articles on O/R mapping and persistence frameworks, and some working pattern for DAL creation in .NET which uses as less coding as possible, and is extensible and easy to read/understand.

Maybe we have to try: Using NHibernate and Log4Net in ASP.NET 2.0 applications.