Sql-server – Why are transactions running as distributed (DTCXact)

distributed-transactionsmsdtcsql serversql-server-2008-r2transaction

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask.

I repetitively run the following query against production servers:

SELECT  DTAT.transaction_id ,
    DTAT.[name] ,
    DTAT.transaction_begin_time ,
    CASE DTAT.transaction_type
      WHEN 1 THEN 'Read/write'
      WHEN 2 THEN 'Read-only'
      WHEN 3 THEN 'System'
      WHEN 4 THEN 'Distributed'
    END AS transaction_type ,
    CASE DTAT.transaction_state
      WHEN 0 THEN 'Not fully initialized'
      WHEN 1 THEN 'Initialized, not started'
      WHEN 2 THEN 'Active'
      WHEN 3 THEN 'Ended' -- only applies to read-only transactions
      WHEN 4 THEN 'Commit initiated'-- distributed transactions only
      WHEN 5 THEN 'Prepared, awaiting resolution'
      WHEN 6 THEN 'Committed'
      WHEN 7 THEN 'Rolling back'
      WHEN 8 THEN 'Rolled back'
    END AS transaction_state ,
    CASE DTAT.dtc_state
      WHEN 1 THEN 'Active'
      WHEN 2 THEN 'Prepared'
      WHEN 3 THEN 'Committed'
      WHEN 4 THEN 'Aborted'
      WHEN 5 THEN 'Recovered'
    END AS dtc_state
FROM    sys.dm_tran_active_transactions DTAT
        INNER JOIN sys.dm_tran_session_transactions DTST
                         ON DTAT.transaction_id = DTST.transaction_id
WHERE   [DTST].[is_user_transaction] = 1
ORDER BY DTAT.transaction_begin_time

It always shows results similar to this sample:

2414848764;DTCXact;2016-01-28
10:24:41.983;Distributed;Committed;Committed
2414896908;DTCXact;2016-01-28
10:26:05.847;Distributed;Committed;Committed
2414903917;DTCXact;2016-01-28
10:26:29.017;Distributed;Committed;Committed
2414918503;user_transaction;2016-01-28
10:27:06.823;Read/write;Active;NULL 2414918551;DTCXact;2016-01-28
10:27:06.973;Distributed;Committed;Committed

This result shows that most transactions run as distributed. I really don't know why at all. Some of the servers are clustered with two nodes. Others are not. Their results are similar.

Facts:

  • The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers.
  • remote trans proc in sys.configurations is set to 0.
  • I found no explicit Begin DISTRIBUTED Transaction or SET REMOTE_PROC_TRANSACTIONS usage in code
  • no definition / usage of TransactionScope in Code
  • Found a class described as follows: //COM+ Transaction mgr using "Service without Components" model built according to System.Transactions.TransactionScope (NET 2). I don't know what this means.

The asp.net web application causing those transactions usually always reads and writes data from one and the same database on one and the same SQL-Server. There MAYBE some very few exceptions, especially running during nighttime copying some data around, but I would not expect to see the MAJORITY of transactions being distributed the whole day long.

I traced the actios of a SPID that I found was running distributed transactions:

Here is a sample result of tracing: I see dozends of RPC:Completed Events in a row followed by 2 DTCTransaction Events (1st: EventSubClass="Enlisting in a DTC transaction", 2nd EventSubclass= "Propagate Transaction").
All target the same DB. No TM:Promote Tran xxx Events happen.

I found out that those queries are running on the IIS (where the Web Application is hosted) in a .net Windows Service, that uses the same database as well and executes jobs. May this 2nd application cause DTC??

I searched around the web without finding similar questions or any answers. So I wonder what could be the reason for this? Or what knowledge do I miss to understand this?

Best Answer

Have you tried profiling a single session to see if/when its transaction is promoted to distributed? For example, using a server side trace using e.g. the DTCTransaction (and maybe TM: Promote Tran xxx) event classes. You can also monitor detailed DTC activity in Windows.

I would profile all the likely looking connection, execution, and transaction-related events for a single SPID while it did some representative work. Note: profiling is best done on a test system due to overheads, using a server-side traces or the Extended Events equivalent. Do not use the Profiler UI except on a local test instance if you really have no other option.

If I were unable to see the application source code or talk to the developers about the use of transactions, this is likely how I would begin to isolate the cause.

It may also be that the application, its data access layer, or client driver is set to promote transactions when a second local database is accessed or a second connection used in the same transaction. There might even be an explicit BEGIN DISTRIBUTED TRANSACTION. Perhaps TransactionScope (or similar) is being used in ADO.NET with multiple connections. See:

There are many possibilities, but tracing/profiling will at least give you some clues about where to focus your investigations.

My guess is that it's most likely to be an unintended side-effect of the way your application developers are using .NET connections and transactions, perhaps combined with the way IIS is configured.

It does seem likely the distributed transactions are associated with your "Transaction Mgr" class in some way. There doesn't have to be an explicit TransactionScope in your code, that's just one common vector.

Stepping through the application code (and data access components) while watching the effect on DTC and SQL Server may be the best way to determine exactly where, when, and why distributed transactions are being invoked or enlisted to.

Obsolete now the question has been edited, but other causes include:

  • Linked servers with remote proc transaction promotion set true. This can cause a local transaction to be promoted to a distributed transaction.

  • The legacy configuration setting remote trans proc in sys.configurations, when used with 'remote servers'. Also see the SET REMOTE_PROC_TRANSACTIONS session option.

Related Question