Why are transactions running as distributed (DTCXact)?

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. The

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:

• Using System.Transactions

• System.Transactions Integration with SQL Server

• Distributed Transactions (.NET Framework)

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.

StackExchange Database Administrators Q#127579, answer score: 5