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.

Answer 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.

Answer 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?

Answer 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?

Answer 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]

Answer 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();
        }
    }

Answer 6

TransactionScope always escalates to DTC transaction, if you use access more than 1 connection inside. The only way the code above can work with DTC disabled is if by a huge chance you get the same connection from the connection pool both times.

"Trouble is, on half of our developers machines, we can run with MSDTC disabled." Are you sure sure it's disabled ;)

Answer 7

Make sure your connectionString does not set pooling to false. This will result in a new connection for each new SqlConnection in the TransactionScope and escalates it to DTC.