TransactionScope automatically escalating to MSDTC on some machines?

Question

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.

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?

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?

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]

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

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