Detect whether a query is in a distributed transaction

Question

I need a reliable way to determine, from within a SQL Server query, whether the query is running inside a distributed transaction.¹ It doesn't matter whether the distributed transaction was created externally or with a BEGIN DISTRIBUTED TRANSACTION statement — either way, I need to know about it.

I don't see a specific SQL Server function or stored procedure claiming to provide this information. There are some dynamic-management views whose documentation claims will provide this information, but the information is unreliable. For example, sys.dm_tran_session_transactions has the column is_local:

1 = Local transaction.

0 = Distributed transaction or an enlisted bound session transaction.

So, test it, making use of SAVE TRANSACTION, which is unsupported in a distributed transaction and will cause an error.²

This query is not in a distributed transaction and works as expected, selecting a value of 1 for is_local:

BEGIN TRANSACTION

SELECT s.is_local
FROM   sys.dm_tran_session_transactions s

SAVE TRANSACTION Error

ROLLBACK TRANSACTION

But, if we replace BEGIN TRANSACTION with BEGIN DISTRIBUTED TRANSACTION, is_local is still 1, but we get the error "Cannot use SAVE TRANSACTION within a distributed transaction." So, we cannot rely on the is_local value.

How about sys.dm_tran_active_transactions? Its transaction_type column is described:

Type of transaction.

1 = Read/write transaction

2 = Read-only transaction

3 = System transaction

4 = Distributed transaction

We'll also need a way to identify the current transaction, which sys.dm_tran_current_transaction provides. So, let's test again:

BEGIN TRANSACTION

SELECT a.transaction_type
FROM   sys.dm_tran_current_transaction c
INNER JOIN sys.dm_tran_active_transactions a ON c.transaction_id = a.transaction_id

SAVE TRANSACTION Error

ROLLBACK TRANSACTION

For this non-distributed transaction, we get a value of 1, though 2 might also be possible. But, replace BEGIN TRANSACTION with BEGIN DISTRIBUTED TRANSACTION again, and we get the same value for transaction_type, but this time with the error from SAVE TRANSACTION. So, we cannot rely on transaction_type either.

Just to ensure that the issue isn't actual enlistment in the distributed transaction, I have also tried using TransactionScope from C# code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Transactions;
using IsolationLevel = System.Transactions.IsolationLevel;

namespace TransactionTroubleshooting
{
    class Program
    {
        private const string ConnectionString = "Server=.;Database=master;Trusted_Connection=True;";

        // Use C# 7.1 or later.
        public static async Task Main(string[] args)
        {
            try
            {
                await RunOuterTransaction();
            }
            catch (Exception e)
            {
                var current = e;
                while (current != null)
                {
                    Console.WriteLine(current.Message);
                    Console.WriteLine();
                    Console.WriteLine(current.StackTrace);
                    Console.WriteLine();
                    current = current.InnerException;
                }
            }
            finally
            {
                Console.WriteLine("Press a key...");
                Console.ReadKey();
            }   
        }

        private static async Task RunOuterTransaction()
        {
            using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew,
                new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted},
                TransactionScopeAsyncFlowOption.Enabled))
            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"
SELECT a.transaction_type
FROM   sys.dm_tran_current_transaction c
INNER JOIN sys.dm_tran_active_transactions a ON c.transaction_id = a.transaction_id
";
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine("Outer transaction_type is {0}", reader["transaction_type"]);
                        }
                    }
                }

                await RunInnerTransaction();
                transaction.Complete();
            }
        }

        private static async Task RunInnerTransaction()
        {
            // We need Required, not RequiresNew, to get the distributed transaction.
            using (var transaction = new TransactionScope(TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
                TransactionScopeAsyncFlowOption.Enabled))
            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"
SELECT a.transaction_type
FROM   sys.dm_tran_current_transaction c
INNER JOIN sys.dm_tran_active_transactions a ON c.transaction_id = a.transaction_id

-- Because this query is in a distributed transaction, if you want to throw, uncomment:
-- SAVE TRANSACTION Error
";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine("Inner transaction_type is {0}", reader["transaction_type"]);
                        }
                    }
                }

                transaction.Complete();
            }
        }
    }
}

Results:

Uncommenting SAVE TRANSACTION does the same thing with an added exception, as expected, indicating a distributed transaction. is_local from earlier can be tested similarly. Once again, neither is_local nor transaction_type reliably indicates a distributed transaction.

I have been unable to find another documented way to try detecting a distributed transaction within SQL. Is it possible? If so, how?

¹ _{This question is superficially related to .net detect distributed transaction, but I need the detection from SQL rather than .NET.}

² _{I need to detect the distributed transaction without causing an error, so I can't just put SAVE TRANSACTION in my query and wait for the error.}

Answer 1

The best solution I have found so far is to check a different field on the sys.dm_tran_active_transactions view. Documentation describes column transaction_uow:

Transaction unit of work (UOW) identifier for distributed transactions. MS DTC uses the UOW identifier to work with the distributed transaction.

For every case I have found, when we are in a distributed transaction, transaction_uow is non-null; otherwise, transaction_uow is null. The following SQL demonstrates:

BEGIN TRANSACTION

SELECT IIF(a.transaction_uow IS NULL, N'Not Distributed', N'Distributed') AS [Distributed?]
FROM   sys.dm_tran_current_transaction c
INNER JOIN sys.dm_tran_active_transactions a ON c.transaction_id = a.transaction_id

ROLLBACK TRANSACTION

BEGIN DISTRIBUTED TRANSACTION

SELECT IIF(a.transaction_uow IS NULL, N'Not Distributed', N'Distributed') AS [Distributed?]
FROM   sys.dm_tran_current_transaction c
INNER JOIN sys.dm_tran_active_transactions a ON c.transaction_id = a.transaction_id

ROLLBACK TRANSACTION

Results:

Behavior is the same when modifying the C# code in the question to test for the distributed transaction.