.NET TransactionScope and MSDTC

Question

In my code there are transactions to handle:

using (var scope = new TransactionScope())
{
  repo1.SaveSomething();
  repo2.SaveAnythingElse();
  scope.Complete();
}

Inside repo1 and repo2 functions create their own db context with using, and dispose them, the transactions worked like a charm.

Now I add another code like this, and it begins to drop an exception:

The underlying provider failed on Open. (EntityFramework) 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. (System.Transactions) The transaction manager has disabled its support for remote/network transactions.

I read that when connections are opened inside transaction despite of the same sql server same db - it needs the MSDTC component to handle it. I changed the code to the following:

using (var scope = new TransactionScope(TransactionScopeOption.Required, 
new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }))
{
   ....
   scope.Complete();
}

And now the exception disappears.

My questions:

• why the transactions used in the code earlier never drop the exception?
• why the new code drops it?
• after the change why it drops no more?

Easy questions I think :) Any help would be appreciated!

Physiotherapy

What database? Transaction promotion is provider-dependent behavior. – Grimy

MS-Sql server, Compatibility level SQL Server 2012 (110). – Physiotherapy

Answer 1

1) You absolutely should use ReadCommitted over the default Serializable for you TransactionScope, but that's unrelated to your issue, see here.

2) When you have an active TransactionScope, any time you open a SqlConnection it will be enlisted in that Transaction. If there are no other resources participating in the Transaction, SqlClient will begin a local, or "lightweight" transaction. This does not involve MSTDC; it's just a normal SQL Server transaction started on the open SqlConnection.

If you close that SqlConnection (or Dispose an EF DbContext that contains it), the connection is returned to the connection pool. But it's segregated from the other pooled connections, and just hangs out until the Transaction is Completed or Rolled back.

If you open a new SqlConnection within the same TransactionScope, with exactly the same ConnectionString, instead of getting a new connection the connection pool just gives you back the existing connection that is already enlisted in the Transaction.

If you open a new SqlConnection within the same TransactionScope with a different ConnectionString, or when there is not a connection in the connection pool already enlisted in the Transaction, then you will get a new SqlConnection and it will be enlisted in the Transaction. But since there's already another SqlConnection enlisted in the Transaction, this will require MSTDC to create a real Distributed Transaction. This is called "promotion"; your "lightweight transaction" is "promoted" to a "distributed transaction".

So with that background, audit your connection lifetime and ConnectionString usage to see why you are triggering promotion here.

In other words, with proper ConnectionString usage and connection lifetime management you should be able to run this code:

using (var scope = new TransactionScope())
{
  repo1.SaveSomething();
  repo2.SaveAnythingElse();
  scope.Complete();
}

Without triggering a distributed transaction.

Grimy

Thanks. I will review my code. So this means the "ReadCommitted" does not count. Then - it was the only change in my code - why the exceptions won't drops anymore? – Physiotherapy

"...or when there is not a connection in the connection pool already enlisted in the Transaction..." - paragraph 5. Surely this would be the case when you create your first connection after creating the transaction? And we know that that will not be promoted. – Stephanestephani

Promotion only happens when you enlist a second connection. – Grimy

Ok, so the bit I quoted applies if you've opened one, and then you open a second one without closing the first, right? Incidentally, this has been the most useful thing I've read about TransactionScope in two whole days. It's hard to find decent material that talks about the details, so thank you. – Stephanestephani