1

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();
}
Community
  • 1
  • 1
user3086298
  • 290
  • 1
  • 4
  • 14
  • 1
    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](http://www.sscce.org/) that you can include in your question. – Damien_The_Unbeliever Mar 13 '14 at 11:08
  • 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 Mar 13 '14 at 11:21
  • 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 Mar 13 '14 at 11:28
  • Hi @Damien_The_Unbeliever, I tried to add some sample code to the bottom of the post. – user3086298 Mar 13 '14 at 11:28
  • Hi @Damien_The_Unbeliever, thanks for the auto format.... – user3086298 Mar 13 '14 at 11:51

1 Answers1

1

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • this was my gut feeling... :( – user3086298 Mar 13 '14 at 12:07
  • will reusing the same connection with Enlist=false guarantee it will not use msdtc? – user3086298 Mar 13 '14 at 13:05
  • With Enlist=false it will never use MSDTC, but also not use transactions at all. Makes no sense.; As I said: Reuse the same connection. I said all of this. What is unclear about it? – usr Mar 13 '14 at 13:16
  • why wouldnt it then always use SQL Server lightweight distributed transactions as you have guaranteed it is using the same connection? or does enlist=false also stop the SQL Server lightweight distributed transaction? I have tested using transaction scope, enlist=false - it didnt use msdtc and I failed the second loop - it does rollback as expected. – user3086298 Mar 13 '14 at 13:30
  • I like your original answer, but I would like to not use MSDTC, so first step is to use the same connection, but will this guarantee that the transaction will not be enlisted when using a transaction scope? – user3086298 Mar 13 '14 at 13:41
  • Enlisting means "registering with System.Transaction". It does not mean "promote to distributed transaction". What you want is: one connection, opened once, one TransactionScope, Enlist=true. – usr Mar 13 '14 at 13:43
  • When Enlist=false I'd expect the loop iterations in your example to be independent. If you rollback the 2nd, the 1st stays committed. – usr Mar 13 '14 at 13:44
  • I tested with enlist=false and failed the second loop and the first tran didnt commit - that's the weird thing... – user3086298 Mar 13 '14 at 16:17
  • @user3086298 I'd be interested to investigate this for you. Can you post an executable self-contained repro? Maybe create a table in tempdb and write to that (`CREATE TABLE tempdb.dbo.test ...`). – usr Mar 13 '14 at 16:21
  • my assumption is that enlist=false still runs the transaction 'scope' if it can run it via SQL Server lightweight distributed transactions. enlist=false will still use msdtc if not on the same connection and thus does not render the transaction scope void? – user3086298 Mar 13 '14 at 16:24
  • 2
    I posted last evening but its not on here - this blog cleared it up for me, certainly for SQL2005 @usr response holds water, there are some nuances with SQL2008 that allow for a non enlisted transaction on different connections but withing a transactionscope to rollback :( here is the blog : http://blogs.msdn.com/b/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx , Im marking usr as correct as this is the expected behavior and often the correct one! It was also learning about transactions vs promotion... – user3086298 Mar 14 '14 at 07:59