Common Gotchas when using TransactionScope and MS DTC

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.

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.

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.

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