Common Gotchas when using TransactionScope and MS DTC

views:

649

answers:

+2 Q:

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.

+1 A:

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.

StephaneT 2009-12-16 14:33:56

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?

Triynko 2010-05-22 18:04:05

See http://msdn.microsoft.com/en-us/library/ms172070%28VS.90%29.aspxI 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.

StephaneT 2010-05-25 13:19:54

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

mamu 2010-07-15 13:43:06

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

erikkallen 2009-12-16 15:18:33

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: http://stackoverflow.com/questions/2884863/under-what-circumstances-is-an-sqlconnection-automatically-enlisted-in-an-ambient/2886326#2886326). 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.

Triynko 2010-05-22 18:28:54

Also note that if you change any of your objects during the transaction they will not be rolled back unless you add code to handle this.

See http://stackoverflow.com/questions/1075839/transactionscope-and-rolling-back-object-state

trevdev 2010-01-13 21:00:27

What? If you're talking about database objects, then that's not true. If you're issuing commands on a connection enlisted in the transaction scope, and you don't commit it, then when the scope is disposed, the changes are rolled back... just like any other transaction. If you're talking about non-database objects, like files and application variables, then yeah... because they have nothing to do with the transaction.

Triynko 2010-05-22 18:34:20

ansaurus

tags:

views:

answers:

Common Gotchas when using TransactionScope and MS DTC

related questions