TransactionScope automatically escalating to MSDTC on some machines?

Question

In our project we're using TransactionScope's to ensure our data access layer performs it's actions in a transaction. We're aiming to not require the MSDTC service to be enabled on our end-user's machines.

Trouble is, on half of our developers machines, we can run with MSDTC disabled. The other half must have it enabled or they get the "MSDTC on [SERVER] is unavailable" error message.

It's really got me scratching my head and has me seriously considering rolling back to a home-spun TransactionScope-like solution based on ADO.NET transaction objects. It's seemingly insane - the same code that works (and does not escalate) on half of our developer's does escalate on the other developer's.

I was hoping for a better answer to Trace why a transaction is escalated to DTC but unfortunately it doesn't.

Here's a sample bit of code that will cause the trouble, on the machines that try to escalate, it tries to escalate on the second connection.Open() (and yes, there is no other connection open at the time.)

using (TransactionScope transactionScope = new TransactionScope() {
   using (SqlConnection connection = new SqlConnection(_ConStr)) {
      using (SqlCommand command = connection.CreateCommand()) {
         // prep the command
         connection.Open();
         using (SqlDataReader reader = command.ExecuteReader()) {
            // use the reader
            connection.Close();
         }
      }
   }

   // Do other stuff here that may or may not involve enlisting 
   // in the ambient transaction

   using (SqlConnection connection = new SqlConnection(_ConStr)) {
      using (SqlCommand command = connection.CreateCommand()) {
         // prep the command
         connection.Open();  // Throws "MSDTC on [SERVER] is unavailable" on some...

         // gets here on only half of the developer machines.
      }
      connection.Close();
   }

   transactionScope.Complete();
}

We've really dug in and tried to figure this out. Here's some info on the machines that it works on:

• Dev 1: Windows 7 x64 SQL2008
• Dev 2: Windows 7 x86 SQL2008
• Dev 3: Windows 7 x64 SQL2005 SQL2008

Developers it doesn't work on:

• Dev 4: Windows 7 x64, SQL2008 SQL2005
• Dev 5: Windows Vista x86, SQL2005
• Dev 6: Windows XP X86, SQL2005
• My Home PC : Windows Vista Home Premium, x86, SQL2005

I should add that all machines, in an effort to hunt down the problem, have been fully patched with everything that's available from Microsoft Update.

Update 1:

• http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/a5462509-8d6d-4828-aefa-a197456081d3/ describes a similar problem...back in 2006!
• http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28VS.80%29.aspx - read that code sample, it clearly demonstrates a nested-second connection (to a second SQL server, actually) which will escalate to DTC. We are not doing this in our code - we're not using different SQL servers, nor different connection strings, nor do we have nested secondary connections opening - there should not be escalation to DTC.
• http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx (from 2005) talks about how escalation to DTC will always happen when connecting to SQL2000. We're using SQL2005/2008
• http://msdn.microsoft.com/en-us/library/ms229978.aspx MSDN on transaction escalation.

That MSDN transaction-escalation page states that the following conditions will cause a transaction to escalate to DTC:

1. At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
2. 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.
3. 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.

We're not experiencing #3. #2 is not happening because there is only ever one connection at a time, and it's also to a single 'durable resource'. Is there any way that #1 could be happening? Some SQL2005/8 configuration that causes it to not support single-phase notifications?

Update 2:

Re-investigated, personally, everyone's SQL Server versions - "Dev 3" actually has SQL2008, and "Dev 4" is actually SQL2005. That'll teach me to never trust my coworkers again. ;) Because of this change in data, I'm pretty sure we've found our problem. Our SQL2008 developers weren't experiencing the problem because SQL2008 has copious amounts of awesome included that SQL2005 does not have.

It also tells me that because we're going to be supporting SQL2005 that we can't use TransactionScope like we have been, and if we want to use TransactionScope we're going to need to be passing a single SqlConnection object around...which seems problematic in situations where the SqlConnection can't easily be passed around...it just smells of global-SqlConnection instance. Pew!

Update 3

Just to clarify up here in the question:

SQL2008:

• Allows multiple connections within a single TransactionScope (as demonstrated in the above sample code.)
• Caveat #1: If those multiple SqlConnections are nested, that is, two or more SqlConnections are opened at the same time, TransactionScope will immediately escalate to DTC.
• Caveat #2: If an additional SqlConnection is opened to a different 'durable resource' (ie: a different SQL Server,) it will immediately escalate to DTC

SQL2005:

• Does not allow multiple connections within a single TransactionScope, period. It will escalate when/if a second SqlConnection is opened.

Update 4

In the interest of making this question even more of a mess useful, and just for more clarity's sake, here's how you can get SQL2005 to escalate to DTC with a single SqlConnection:

using (TransactionScope transactionScope = new TransactionScope()) {
   using (SqlConnection connection = new SqlConnection(connectionString)) {
      connection.Open();
      connection.Close();
      connection.Open(); // escalates to DTC
   }
}

This just seems broken to me, but I guess I can understand if every call to SqlConnection.Open() is grabbing from the connection pool.

"Why might this happen, though?" Well, if you use a SqlTableAdapter against that connection before it's opened, the SqlTableAdapter will open and close the connection, effectively finishing the transaction for you because you now can't re-open it.

So, basically, in order to successfully use TransactionScope with SQL2005 you need to have some sort of global connection object that remains open from the point of the first TransactionScope is instantiated until it's no longer needed. Besides the code-smell of a global connection object, opening the connection first and closing it last is at odds against the logic of opening a connection as late as possible and closing it as soon as possible.

Solution 1

SQL Server 2008 can use multiple SQLConnections in one TransactionScope without escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.

I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?

The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.

Solution 2

The result of my research on the topic:

See Avoid unwanted Escalation to Distributed Transactions

I am still investigating Oracle’s escalation behavior: Do transactions spanning multiple connections to same DB escalate to DTC?

Solution 3

That code will cause an escalation when connecting to 2005.

Check the documentation on MSDN - http://msdn.microsoft.com/en-us/library/ms172070.aspx

Promotable Transactions in SQL Server 2008

In version 2.0 of the .NET Framework and SQL Server 2005, opening a second connection inside a TransactionScope would automatically promote the transaction to a full distributed transaction, even if both connections were using identical connection strings. In this case, a distributed transaction adds unnecessary overhead that decreases performance.

Starting with SQL Server 2008 and version 3.5 of the .NET Framework, local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed. This requires no changes to your code if you are already using connection pooling and enlisting in transactions.

I can't explain why Dev 3: Windows 7 x64, SQL2005 succeeds and Dev 4: Windows 7 x64 fails. Are you sure that is not the other way round?

Solution 4

I don't know why this answer was deleted but this seems to have some relevant information.

answered Aug 4 '10 at 17:42 Eduardo

1. Set Enlist=false on connection string to avoid auto enlistment on transaction.

2. Manually enlist connection as participants in transaction scope. [original article outdated] or do this: How to prevent automatic MSDTC promotion [archive.is]

Solution 5

I'm not too sure if nested connection is the issue. I'm calling a local instance of SQL server and it doesn't generate the DTC??

    public void DoWork2()
    {
        using (TransactionScope ts2 = new TransactionScope())
        {
            using (SqlConnection conn1 = new SqlConnection("Data Source=Iftikhar-PC;Initial Catalog=LogDB;Integrated Security=SSPI;"))
            {
                SqlCommand cmd = new SqlCommand("Insert into Log values(newid(),'" + "Dowork2()" + "','Info',getDate())");
                cmd.Connection = conn1;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();

                using (SqlConnection conn2 = new SqlConnection("Data Source=Iftikhar-PC;Initial Catalog=LogDB;Integrated Security=SSPI;Connection Timeout=100"))
                {
                    cmd = new SqlCommand("Insert into Log values(newid(),'" + "Dowork2()" + "','Info',getDate())");
                    cmd.Connection = conn2;
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }

            ts2.Complete();
        }
    }

View more solutions

Comments

[RichardOD]

Can you expand the "Do other stuff here that may or may not involve in the ambient transaction". Surely what is in there greatly affects how the code behaves?

[Joe]

"#2 is not happening because there is only ever one connection at a time" - #2 doesn't say that the second connection needs to be open at the same time, just that it needs to be enlisted in the same transaction.

[Oran Dennison]

Thank you so much for reporting back with Update 4 showing how escalation can occur with just a single SqlConnection. This is exactly what I was running into despite carefully ensuring that only a single SqlConnection is used. It's nice to know it's the computer that's crazy and not me. :-)

[brumScouse]

In terms of connection pooling, if we have multiple connections (and nested if necessary) if we are opening and closing one at a time are we utilisng 1 real conncetion pool resource or 1 per connection, I am trying to rationalise this in order to determine whether or not to have appropriately scoped "enlistable" conneection (which I would like to avoid)

[axeman]

I stumbled across this question will trying to find an reason for an exception. I'm including the exception details so that others may find it.Network access for Distributed Transaction Manager (MSDTC) has been disabled. with inner exception The transaction manager has disabled its support for remote/network transactions 0x8004D024 Software was working at many sites but not at just a few. Turns out they were running SQL2005 and as this question points out, they had to use MSDTC.

[Mathias F]

I had the error when connecting to SQLServer 2008 and using "pooling=false" in the ConnectionString. The whole afternoon my colleagues heard me mumbling "But I tripple checked: I have 2008"

[PreguntonCojoneroCabrón]

TransactionScope uses the LTM - Lightweight Transaction Manager in .Net. Only if you open more than one connection in the same transaction or go between databases, should TransactionScope promote the transaction to the 2PC-based TX-manager, DTC. For MS SQL Server 2008 and above, the DTC will become involved only if you are opening connections to different DBs. OR if you are opening connections in the same transactions from multiple threads EXCEPT if you are using DependentTransaction which is what you should enlist in your global transaction if you want to do threading.

[PreguntonCojoneroCabrón]

Nested connections under the same transaction scope will promote to a distributed transaction. From SQL server 2008 and above multiple (not nesting) connections under the same transaction scope will not promote to a distributed transaciton.

[Yoopergeek]

Yes, the details are correct, and is anyone actually looking at the code? There are two connections within the transaction scope, however, there is only ever one connection instanced and opened at a single moment in time. Also, no, DTC is not running on the machines that are working.

[Joe]

"however, there is only ever one connection instanced and opened at a single moment in time" - why is that relevant? With SQL2005, if you open more than one connection within a transaction scope, you will escalate whether or not they remain open simultaneously. Which is logical if you think about it.

[Yoopergeek]

You and hwiechers now have me second-guessing and I'm anxious to get into work Monday and inspect their individual machines more closely and make sure the SQL Server versions are as previously reported.

[Yoopergeek]

You and hwiechers are right. I have egg all over my face. Thank you for hitting me with the clue-stick. :) Because you were first, you get the answer. I'd like to add one point of clarification, though - SQL2008 allows multiple connections to be opened, but not at the same time. There still can only be one single connection open at any given time or TransactionScope will escalate to DTC.

[Baig]

Thanks for sharing your research. It really helped. One more quick query. Whats the difference between TransactionScope() and sqlConnection.BeginTransaction()?

[Yoopergeek]

Which edition of SQL Server you're using? I wonder if @Peter Meinl's answer needs to be updated to reflect any changes made in 2008R2, and/or Denali.

[Iftikhar Ali]

I'm using SQL Server 2008 R2.

[Yoopergeek]

I wonder if 2008 R2 is better-behaved? @hwiechers answer also makes me wonder if the version of the Framework you're compiling against is preventing escalation. Finally, I wonder if it being a local R2 instance makes any difference. I wish I had the time/resources to investigate how this has changed with the release of 2008 R2 and SQL Server 2012.

[Paul Zahra]

Not sure if nested connection is the issue? lol... well blooming remove it then!, why on earth do people nest using statements when not absolutely necessary, I'll never know.

[Evgeniy Berezovsky]

@Yoopergeek I could verify that your "not at the same time" is important and edited @Joe 's answer accordingly. Monitoring the TCP connections while testing showed that the old TCP connection will get reused when connections are not used at the same time, and thus the TransactionScope can make do with a single COMMIT on the server side, which would make escalation superfluous.

[Frédéric]

According to this feature request, ODAC 12C should now behave as SQL 2008, not promoting to distributed when using consecutive connections to the same data source.

[Kiquenet]

msdn.microsoft.com/en-us/library/ms172153%28v=VS.80%29.aspx not found, Visual Studio 2005 Retired documentation

[Pennidren]

"if you are already using connection pooling" I am so glad you quoted this detail. I am one of the few very unfortunate developers that does not (cannot) use pooling, and I was struggling to understand why DTC was always occurring despite SQL Server 2008+ without nested connections within a TransactionScope. Even in otherwise detailed analyses of DTC promotion, the pooling requirement was never mentioned. THANK YOU!

[Merin Nakarmi]

This error was happening in SQL Server 2016. Adding enlist=false did solve my problem.

[Patrick Szalapski]

Enlist=false just makes all TransactionScopes useless, right? Any rollbacks will be ineffective.