10

How can I reliably check that MSDTC has promoted a transaction to a distributed transaction?

This is when using TransactionScope in .net.

Currently a co-worker is testing this by shutting down the coordinator on his machine - if an exception is thrown this is taken as evidence that an attempt to promote the transaction has occurred. Is this a valid test?

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • MSDTC is a pretty solid product. Are you sure you need to test this? – Steven Mar 16 '10 at 20:55
  • 2
    @Steven - I need to check whether transactions are being promoted or not. The situation is that transactions are not supposed to be promoted, but my co-worker believed that they are being promoted. How can we be sure? How do we check that a transaction was or was not promoted? – Oded Mar 16 '10 at 22:36
  • @Oded: If transactions shouldn't be promoted, why are you using the `TransactionScope`? – Steven Mar 17 '10 at 07:46
  • 1
    @Steven - transactions should happen, and several calls to the same database under the same connection can be wrapped in one transaction. However, such call (to one DB with the same connection) should not be promoted to _distributed_. I expect only transaction that _span_ databases to become distributed. – Oded Mar 17 '10 at 08:11
  • Okay, you're calling multiple databases on the same server in a single transaction. This would indeed be hard to achieve without a `TransactionScope` when you have no control over (generated) SQL (as with LINQ to SQL and Entity Framework). – Steven Mar 17 '10 at 08:18
  • 1
    @Steven - My problem is that I am calling the same database with the same connection string within a single transaction and the transactions is apparently getting promoted to distributed even though it shouldn't. How do I check if it has or hasn't? – Oded Mar 17 '10 at 08:50

1 Answers1

15

I think your test is OK although you should ensure that you are getting a DTC exception and not some other exception.

Some other things you could do:

  • You could also run SQL Profiler and under Transactions trace DTCTransaction.

  • In terms of code, you could handle the DistributedTransactionStarted event and log a message when a distributed transaction is started.

  • Or you could just add log messages to log the System.Transactions.Transaction.Current. TransactionInformation.DistributedIdentifier before the end of the transaction. If the value is Guid.Empty {00000000-0000-0000-0000-000000000000} then it is not a distributed transaction otherwise the transaction has been promoted to a distributed transaction.

You said you were using SQL Server 2008. What version of .NET are you using? Is it 3.5? If you are using SQL Server 2008 and .NET 3.5 then you should be able to open multiple connections (using the same connection string) to the same database in the same transaction without escalating to a distributed transaction. For this to work you need to close the first connection before opening a second connection.

If it appears that all the conditions are met and the transactions are still escalating, I would:

  • double check the SQL Server compatibility level
  • check the connection string to see if pooling is disabled
  • check to see that two connections are not opened at the same time in one transaction
  • find out if all transactions are being promoted or only in certain scenarios

UPDATE: The Distributed Transaction Coordinator(MSDTC) and Transaction FAQ pulls together a great list of MSDTC resources.

Randy Levy
  • 22,566
  • 4
  • 68
  • 94
  • Thanks for the detailed answer - where did you acquire such knowledge? Can you recommend any resources on MSTDC? – Oded Mar 20 '10 at 17:34
  • 1
    MSDTC is great when it is working. When it's not working is when you learn the most about it. :) In general, MSDN has the best MSDTC resources. For troubleshooting http://support.microsoft.com/kb/306843 has usually been the goto article but this one, http://msdn.microsoft.com/en-us/library/aa561924%28BTS.10%29.aspx , is very good as well. – Randy Levy Mar 22 '10 at 15:57