• Download Code.zip - 25.7 KB

TransactionScope - unwanted behaviour

Introduction

I've stumbled on a MS template of creating DAL and BLL (http://www.asp.net/learn/dataaccess/default.aspx?tabid=63). The code was nice, straigthforward. Everything was OK with it. Unfortunately I began tu use this structure with TransactionScope.

As a lazy programmer, i like to use DataTableAdapter classes. They open and close connections for me. I've "copied" the style of the VS generated codes 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, the mentioned style of database update needs transaction to lock data after it was red (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 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 commited.

Nice, isn't it?

In the HELL

So I began to use the coding pattern descibed above. In a TransactionScope I've used TableAdapter.Fill() and a SqlCommand.ExecuteNonQuery(). In both cases a Connection was opened and closed.

The code workedperfeclty...until it was deployed on the production server (with separate application and database server). From that moment, i've 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 work's everything fine, problems arise when DB server is on an other machine - or in real testing enviroment.

The RESEARCH

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

Finally i've found a super article about TransactionScope - http://www.code-magazine.com/articleprint.aspx?quickid=0605031&printmode=true
There's also an article which describes transaction escalation - http://msdn2.microsoft.com/en-us/library/ms229978.aspx

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.

UNFORTUNATELLY escalation also happens, when 2 connections (even with identical connection string) are opened.

WHAT TO DO?

...if you have 4 man-month project, have no time for modifications?

• configure MSDTC (performance loss, unpredictabe errors, problems at smart-client - special configuration on the client side)
• avoid transaction escalation

Possibility b) is what I needed. But HOW TO AVOID CHANGES IN CODE (in DAL)?

So i've continued with my research. For you, i've created a list of interresant www pages, where you can get more info on the given problem. All of them is included in this article. Here's additionally a link for the microsoft description:
http://msdn2.microsoft.com/en-us/library/0abf6ykb.aspx

I've tried to create own transaction manager (ref.: http://www.codeproject.com/cs/database/transactionScope_in_1_1.asp). Actually the sample i've found used again COM+, so i've got errors for some reasons.

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

ALMOST what I needed

The most closest solution was: http://blogs.vertigosoftware.com/aanttila/archive/2006/07/19/Implementing_a_Custom_Transaction_Manager.aspx.

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 (so 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 in my BLL/DAL. So i had to solve my problem in an 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 threads local storage (TLS). When the DAL needs a connection and a connection in TLS exists, return that connection. Before transaction-scope ends, close connection and clear connection from TLS.

Part 2) Solve DAL-calls outside TransactionScope

Outside transaction scope a new connection is created and it's not stored in the TLS.

So this sounds cool... The code is in the Fix.cs file.

As you see, we had to create a static class (LocalConnHelper) and store connection string there. This was necessary, because the LocalTranGuard needed to inicialize conections inside it's constructor (other type of behavious would leed us to more changes).

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

NOTE:If you wan't you can remove GetFbConnection and IsServer from the LocalConnectionHelper. I needed it because we used Firebird and SQL DAL.

When you wan't to USE this code:

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

The solution handles also 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 tests the new method:

• TLS1 - testing TLS with 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 informations into the textbox. You'll get the ID of the thread and the data (or it's hash) written to the text-box. For the same ID the Data you'l get displayed should be the same if not then the code is not functional :-)

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

SO 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, pro and contras on O/R mapping and persistence frameworks. 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: http://www.beansoftware.com/ASP.NET-Tutorials/NHibernate-Log4Net.aspx