MSDTC issue with transactions in ADO.NET Entity Framework

in our current project we are using ADO.NET Entity Framework as data layer for the application. There are some tasks which require to run in a transaction because there's a lot of work to do in the database. I am using a TransactionScope to surround those tasks.

using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    // Do something...
    transactionScope.Complete();
}

The problem is as soon as i am using an TransactionScope an exception occurs:

System.Data.EntityException: The underlying provider failed on Open. ---> 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.

It seems that this error has to do something with the MSDTC (Microsoft Distributed Transaction Coordinator). When I change the security configuration of MSDTC another exception is thro开发者_如何学Pythonwn:

System.Data.EntityException: The underlying provider failed on Open. ---> System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

However MSDTC is configured, the TransactionScope will cause an error. Does somebody know whats going wrong here?

---

By default MSDTC has network access disabled. To get it working you should go to

Control Panel-> Administrative Tools->Component Services->Component Serivces->Computes->My computer->Right click->Properties->MSDTC->Security Configuration

and check following checkboxes Network DTC Access, Allow Inbound, Allow Outbound. Authentification should be chosen according to you environment. You might also want to take a look at DTCPing tool to debug distributed transactions. To give you a shortcut - you may need to modify you registry:

HKLM\Software\Policies\Microsoft\Windows NT\RPCRestrictRemoteClients=0 HKLM\Software\Policies\Microsoft\Windows NT\RPCEnableAuthEpResolution=1

to get everything up and running.

---

Yes, it works using Supress, because you are telling it to supress or ignore the ambient transaction and create a new local transaction. Since the transaction is local it is not a distributed transaction so its not using MSDTC, but you probably shouldn't use Suppress and should use Required instead.

---

This means it is suppressing any Transaction that might be in effect currently when you enter your code block, so any updates your code makes will not rollback if the outer "ambient" transaction decides to rollback.

---

This is the article we used in resolving our own, similar issue:

Troubleshooting Problems with MSDTC

This is basically an addendum to Nikolay R's answer. He already covered some of the suggestions listed in the article.

Note: The article is part of the Biztalk documentation, but it can apply to anything using MSDTC.

---

"If you are using Entity Framework with Transactions, Entity Framework automatically opens and closes a connection with each database call. So when using transactions, you are attempting to spread a transaction out over multiple connections. This elevates to MSDTC."

You can pass in your database context to callee class or function in your transaction.

Maybe this is your answer: MSSQL Error 'The underlying provider failed on Open'

---

Supressing the transaction is usefull if you want to run some code that might fail, but you don't want to abort the transaction because of that fail.

The question you need to ask yourself is the following: Are you accessing more than 1 durable resource in your transactionScope? I mean, do you open connections to more than 1 DB?

This is an important question as the transaction will be escalated towards DTC if you access more than 1 durable resource.

At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with does not cause a transaction to be promoted. However, whenever you open a second connection to a 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. Source: MSDN

If that's the case, you can solve your problem by nesting your transactionscopes correctly, example:

//Create rootScope
using(TransactionScope rootScope = new TransactionScope()) 
{ 
    using(TransactionScope scope2 = new 
    TransactionScope(TransactionScopeOption.Required)) 
    {
         //Do work on DB1
         ...

         //Complete this ambient transaction
         scope2.Complete();
    } 

    using(TransactionScope scope3 = new 
    TransactionScope(TransactionScopeOption.Required)) 
    {
         //Do work on DB2
         ...

         //Complete this ambient transaction
         scope3.Complete();
    } 

    //Complete rootScope
    //The whole transaction will only be committed if you call 
    //Complete on the rootScope
    rootScope.Complete();

}

You can find more info about TransactionScopes, how nesting works,... on MSDN.

I hope this answer can help people in the future.

---

If Distributed Transaction Coordinator service is not started, Entity framework can not connect to database. Open and start the Distributed Transaction Coordinator

Services -> Distributed Transaction Coordinator

---

Hmm, it seems to work when i change the TransactionScopeOption to "Suppress":

using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
{
    ...
}

Does everyone know why?