Two db contexts under TransactionScope fails

Question

I am stuck using two db connections with entity framework contexts under a single transaction.

I am trying to use two db contexts under one transaction scope. I get "MSTDC not available". I read it's not an EF problem it's TDC which does not allow two connections.

Is there any answer for this problem?

Answer 1

This happens because the framework thinks that you are trying to have a transaction span multiple databases. This is called a distributed transaction.

To use distributed transactions, you need a transaction coordinator. In your case, the coordinator is the Microsoft Distributed Transaction Coordinator, which runs as a Widows Service on your server. You will need to make sure that this service is running:

Starting the service should solve your immediate issue.

Two-phase commit

From a purely theoretical point of view, distributed transactions are an impossibility* - that is, disparate systems cannot coordinate their actions in such a way that they can be absolutely certain that they either all commit or all roll back.

However, using a transaction coordinator, you get pretty darn close (and 'close enough' for any conceivable purpose). When using a distributed transaction, each party in the transaction will try to make the required changes and report back to the coordinator whether all went well or not. If all parties report success, the coordinator will tell all parties to commit. However, if one or more parties report a failure, the coordinator will tell all parties to roll back their changes. This is the "Two-phase commit protocol".

Watch out

It obviously takes time for the coordinator to communicate with the different parties of the transaction. Thus, using distributed transactions can hamper performance. Moreover, you may experience blocking and deadlocking among your transactions, and MSDTC obviously complicates your infrastructure.

Thus, before you turn on the Distributed Transaction Coordinator service and forge ahead with your project, you should first take a long, hard look at your architecture and convince yourself that you really need to use multiple contexts.

If you do need multiple contexts, you should investigate whether you can prevent transactions from being escalated to distributed transactions.

Further reading

You may want to read:

• MSDN: "Managing Connections and Transactions" (specifically on EF)
• Blog: "Avoid unwanted escalation to distributed transactions" (a bit dated, though)

***** See for example: Reasoning About Knowledge

Answer 2

You should run MSDTC (Distributed Transaction Coordinator) system service.