Unwanted promotion using TransactionScope

Question

I have a system written in C#.net 2.0,sql 2005 where the business layer and Data access layer are already created and uses MSDTC transactions. The connection creation and opening,closing etc are written at the data access layer each method.

I am moving to a new system which re-uses the old business and data access layers and also provides new functionalty in its own business layer and data access layer.

I want every transation to happen at LTM and noting to be promoted to MSDTC.

If I write using TransactionScope at business layer with calling multiple DAC methods inside, the transaction gets promoted to DTC. WIth new classes, I can handle this by making the connection open and close in the business layer before the transaction scope begin, but what can I do for the old written classes. Is there a way in .Net 4.0 to handle transation scope being promoted? I am using WCF for new services and .net 4.0 for new system.

Ladislav Mrnka Ladislav Mrnka · Accepted Answer · 2011-05-01T08:28:50

A newly opened connection by default joins a current transaction. If the transaction runs on more then one connection (resource) it is automatically promoted to MSDTC. You can force SQL connection to not join the current transaction by specifying Enlist=false in the connection string.

If each old class uses its own connection you should use MSDTC because otherwise multiple data changes executed by your business logic will not be in one transaction. Example:

using (var scope = new TransactionScope())
{
    DalA.DoSomething();  
    DalB.DoSomething();
    scope.Complete();
}

Both DalA and DalB use their own connection. If you use Enlist=false they will not be in transaction and if DalB.DoSomething fails it will not roll back DalA.DoSomething.

The correct approach is either use MSDTC or rewrite your old classes to use one connection. Local transaction works only on one resource (connection).

Unwanted promotion using TransactionScope

1 Answers