Common Gotchas when using TransactionScope and MS DTC

Question

I am just starting to work with using TransactionScope, I find that there are always unexpected things I run into that take forever to debug.

I figure that having a consolidated list of these would be great for those "weird error" circumstances, plus to expand our knowledge of oddness in the platform.

Some context on how I am going to be using transaction scopes:

• web application
• multiple web servers, application servers and sql servers
• transactions will be mainly database transactions but some will be elevated to write to MSMQ.

Tamekia

Answer 1

2 things off the top of my head:

• transactions will be elevated when you use more than one connection object in the same scope, even if the connections have the same connectionstring (this is fixed in sql 2008). Read more in this thread and dbconnectionscope will solve that problem on sql 2005
• msdtc instances need to be able to see each other and need to have their security set up correctly http://support.microsoft.com/kb/899191 (allow inbound and outbound, do not require mutual authentication is usually the safest bet). Use DTCPing to troubleshoot connection issues between dtc instances as explained here: http://support.microsoft.com/kb/306843

You want transactions to be lightweight as much as possible, dtc introduces a lot of overhead. You also want transactions to be as short as possible, so introduce them only on the application servers and not on the web server. Make the hop over the network between the application servers and the database as small as possible and as fast as possible, send network traffic between web and app servers over a different connection than between app servers and db, and make the last one a screaming fast, ridiculously short connection.

If you have multiple application servers, you could consider having a single instance of msdtc running on a server (e.g. on the database or on one of the app servers) and use this remotely from all application servers instead of each one running their own, but I don't know what additional benefits this has.

Shamblin

Is it really fixed in SQL Server 2008? I'm using SQLS2008, and when I open a second connection with same connection string, the transaction gets a Distributed GUID. So... is that just on the client side, or is it really becoming a distributed transaction? – Menon

See msdn.microsoft.com/en-us/library/ms172070%28VS.90%29.aspx I haven't tested it for myself, but according to the docs there should at least be a plausible scenario where sql 2008 behaves like this. Maybe tweaking your connectionstring to explicitly control pooling could help. – Shamblin

Looks like not solved with SQL Server 2008, seeing transaction elevated to DTC with same connection string and local database – Axial

If you opens second connection before closing the first it will escalate to DTC on SQL server 2008. You have to close the first connection before opening the second one if you want to avoid DTC (I'm talking about connection with same connection string). – Serrell

Answer 2

Hopefully this will help someone one day:

If you have a TransactionScope with multiple SQL operations inside, the DTC will not be involved provided

1. you use identical connection string for each connection
2. connections are not nested.

I.e. open, do someting, close. open, do something, close.

Now for the gotcha: if you ever do this in your process (on another thread)

SqlConnection.ClearAllPools()

and that happens to come between your two operations - the DTC will be involved immediately. If you don't have the DTC running, it will throw an Exception.

Delegacy

Answer 3

If you use SQL Server and check @@trancount, it will be 0 even if you have an active TransactionScope.

Springer

It will not be zero; I just tested it. You're probably opening your connection before the transaction scope, which means your connection is not participating in the transaction at all (something I just learned, see my post here: #2885363). Either open you connection inside the transaction scope, or explicitly enlist your existing transaction in the scope by calling connection.EnlistTransaction(Transaction.Current). Run "select @@trancount" to see its non-zero. – Menon