Click here to Skip to main content
12,547,591 members (53,737 online)
Click here to Skip to main content
Add your own
alternative version


36 bookmarked

LocalTranGuard - a TransactionScope Fix/Wrapper for LocalTransactions

, 18 Apr 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Supporting LocalTransactions only with TransactionScope by avoiding escalation to MSDTC.


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:

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
                object localTransConn = 
                if (localTransConn == null)
                    return new SqlConnection(ConnectionString);
                else return (SqlConnection)localTransConn;

        public static FbConnection GetFbConnection
                object localTransConn = 
                if (localTransConn == null)
                    return new FbConnection(ConnectionString);
                else return (FbConnection)localTransConn;

        public static IDbConnection GetIDbConnection
                object localTransConn = 

                if (localTransConn == null)
                    if (IsServer)
                        return GetSqlConnection;
                        return GetFbConnection;
                    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

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

            // init connection

        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)
                               "LocalTransaction"), null);

        public void Complete()

        private void Dispose(bool disposing)
            if (disposing)
                // finish connection

                // finish transaction


        public void Dispose()


Screenshot - example.jpg

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
Slovakia Slovakia
Since 1999 I work in IT. Worked 2-3 yrs with Borland Builder C++. Since .NET appeared, I program in C#, ASP.NET.

You may know the current technologies, but still there's a lot of experience to gain. IT's evolving all the time.

From 2006 I'm a MCP. Now I'm focusing on technologies like: NHibernate, NSpring...

You may also be interested in...


Comments and Discussions

GeneralThread sharing Pin
JeroenMX19-Jun-09 8:14
memberJeroenMX19-Jun-09 8:14 
GeneralSimple Fix for Mulitple Connections Pin
Majid Entezam18-Dec-08 14:21
memberMajid Entezam18-Dec-08 14:21 
GeneralRe: Simple Fix for Mulitple Connections Pin
Chris Richner17-Feb-09 2:55
memberChris Richner17-Feb-09 2:55 
Generalwhat-a-fix !!!!! Pin
RedSunBeer1-Apr-08 23:01
memberRedSunBeer1-Apr-08 23:01 
GeneralRe: what-a-fix !!!!! Pin
balazs_hideghety5-Apr-08 9:46
memberbalazs_hideghety5-Apr-08 9:46 
GeneralTransactionScope not rolling back Pin
RedSunBeer22-Apr-08 1:17
memberRedSunBeer22-Apr-08 1:17 
GeneralRe: TransactionScope not rolling back Pin
balazs_hideghety1-May-08 20:37
memberbalazs_hideghety1-May-08 20:37 
Questionmutiple connections Pin
ssveys13-Sep-07 10:35
memberssveys13-Sep-07 10:35 
AnswerRe: mutiple connections Pin
balazs_hideghety15-Sep-07 22:36
memberbalazs_hideghety15-Sep-07 22:36 
GeneralRe: mutiple connections Pin
Jozef Benikovsky26-Sep-11 3:55
memberJozef Benikovsky26-Sep-11 3:55 
GeneralThanks! Pin
cpigriks13-Apr-07 10:02
membercpigriks13-Apr-07 10:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161018.1 | Last Updated 18 Apr 2007
Article Copyright 2007 by balazs_hideghety
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid