TansactionScope and MSDTC enlisting issues

Question

I have been researching solutions to an issue we are facing. I am using transacitonscope to wrap some looping code the save data to SQL 2005. We have had to revert to using a string and splitting in the proc, so when the string is length > 8000 I split it and this is working great on our test env which is SQL 2008R2. The issue is when we move testing to SQL 2005 environment the transaction is automatically enlisted to msdtc. As the 2005 env is different e.g. different domain and behind firewall etc I would like SQL server to handle the transaction rather than msdtc. We have traced the SQL 2005 db and can see the DTC transactions in the trace, when tracing SQL2008 it is only using SQL transactions in the trace.

The code itself is simple - using statement (TransactionScope ...), there is a loop within the using block which will probably only loop between 1 and 4 iterations at most and in the loop there is code newing up a sql connection to the same DB, and there is a implicit SQltransaction created off this connection (which is there to resolve single updates). I cannot pass the same newed up connection back to itself as the code is in a data access layer, but testing SQL2008 it must be ok with new connections to the same db (new as it pooled I believe). Each loop does the actual update call to the DB. If so if the string is 1200 char length there will be 2 calls to update the db...

Based on some SO posts e.g. SqlConnection and avoiding promotion to MSDTC

there is a setting that can be passed to the connection string Enlist=false, which from my understanding will keep the management of the transaction to sql server and will work provided you are using the same database resource - in my case the same sql 2005, with newed up connections including a connection.begintransaction in each loop - can anyone confirm this? Or is using the connection setting Enlist=false dangerous in my case?

Update: to rephase, is there any caveat with using Enlist=false in a connection string, when using a transcationscope, sql 2005, and code that looks somewhat like this (pseudo code):

using (var ts = new transactionscope)    
{ 
    loop (twice) 
    {      
         using (var conn = new SqlConnection)
         using (var tran = conn.BeginTransaction)
         {
             //do update to the db code here; 
             tran.Commit();
         }
    }

    ts.Complete();
}

Rather than trying to describe your code (your narrative is quite dense and I've got no real idea what your code looks like), maybe try to come up with a SSCCE that you can include in your question. — Damien_The_Unbeliever
You say "I am using transacitonscope to wrap some looping code" and "I would like SQL server to handle the transaction rather than msdtc"; these statements contradict each other. If you want a SQL Server transaction then do not use TransactionScope. — Polyfun
Hi @ShellShock, from what I see in the sql profiler and read transacitonscope doesn't automatically cause a transaction to enlist with msdtc. It does seem to be the case when using SQL 2005 but not when using SQL 2008 — user3086298
Hi @Damien_The_Unbeliever, I tried to add some sample code to the bottom of the post. — user3086298

usr usr · Accepted Answer · 2014-03-13T11:53:24

If you do not enlist the connections (which is possible) your TransactionScope has no effect. You don't want that.

If you enlist multiple SqlConnections you get a distributed transaction by design. This is fundamentally so. If you are lucky, both will use the same internal pool connection and avoid using MSDTC (and instead use SQL Server lightweight distributed transactions). This, however, is never guaranteed and subject to timing issues. It is a performance optimization. I hate this optimization because during testing it always happens and masks the underlying issue.

Don't open multiple connections. Reuse the same connection.

TansactionScope and MSDTC enlisting issues

1 Answers