287
votes

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:

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.

7
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?RichardOD
"#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.Joe
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. :-)Oran Dennison
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)brumScouse
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.PreguntonCojoneroCabrón

7 Answers

73
votes

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.

61
votes
33
votes

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?

11
votes

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]

2
votes

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

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 ;)

0
votes

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.