Why is my MSDTC transaction not correctly rolling back on my localhost environment?

Question

I am currently trying to write a simple C# application against SQL Server 2016 to use Microsoft Distributed Transactions (MSDTC). I am trying to get it working on my local machine (Windows 10 Pro) before I attempt to test it on a server across a network.

The .NET solution consists of a console application project and three separate webapi projects. The console application calls each of the webapi projects, where each webapi project then writes a simple record to the database. This seems to work fine. What I would then like to do is to have the third webapi project generate an exception and for all previous records to be rolled back as part of the distributed transaction. However, my problem is that the first two projects are committing their records to the database and not rolling back.

The way that I have set it up (which I believe it correct) is for each project to use System.Transactions to enlist in using DTC.

The console application acts as the Root Transaction Manager and initiates the transactions by performing something similar to:

using (TransactionScope scope = new TransactionScope())
{
    var orderId = Guid.NewGuid();
    ProcessSales(orderId);
    ProcessBilling(orderId);
    ProcessShipping(orderId);

    scope.Complete();
}

Each Process...() method above uses HttpClient to make a call to its corresponding webapi project.

Each webapi project should then enlist in the root transaction by using another transaction:

using (TransactionScope scope = new TransactionScope())
{
    string connectionString = @"Server=MyServerNameHere;Database=MyDatabaseNameHere;Trusted_Connection=True;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "INSERT INTO dbo.Shipping (ShippingId, OrderId, Created) VALUES (@ShippingId, @OrderId, GetDate())";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            ...
            command.ExecuteNonQuery();
        }
    }

    scope.Complete();
}

The third webapi project has throw new Exception(); just after initializing the TransactionScope.

To setup MSDTC on my machine I have followed the article described here and have performed the following:

1. Checked that the "Distributed Transaction Coordinator" service is running in Administrative Tools -> Services.

2. Have set up Windows Firewall to allow Distributed Transactions:

   Control Panel -> Windows Firewall -> Allow an app or feature through Windows Firewall -> Added "Distribution Transaction Coordinator" to the list.

3. Enabled network transactions in Component Services:

   Component Services -> Exapand Computers -> Expand MyComputer -> Expand Distributed Transaction Coordinator -> Right click 'Local DTC' and select properties -> Select the Security tab

   These are the current settings:

I have also been looking at some of the troubleshooting information described here. One of the steps in this article describes downloading a DTCTool and running RMClient.exe. I have done this and it has generated the following log:

Error(RpcStatus=1753): at RPCuser.cpp (Line:116) RPC Server procedure LogOn failure (1753)

DEBUG TIP::This error means that the Client was able to query the Endpoint Mapper at port 135 on the target machine specified but either was not able to contact the WinRM server. Check for :

1. Firewall blocking higher ports - Get a netmon trace between the client and server and analyze the traffic for this behavior
2. If Windows firewall is enabled on server the check if WinRM.exe is in the exception list
3. In Cluster environment check if the cluster name is resolving to the node where MSDTC/WinRM is running
4. In cluster environment do a DTCPing test between the client and MSDTC cluster name to see if that works Client failed to log on server

I'm not sure if this log is relevant or not but I'm at bit stuck at this point. I temporarily turned off the firewall and it still didn't work so points 1 and 2 shouldn't be a problem. And I'm not using a cluster environment so points 3 and 4 should not be a problem.

I'm not sure if I'm going about setting up MSDTC correctly in my application. I've just put together what bits and pieces I can find about it from across the internet. Any help with this would be very much appreciated.

Thank you

Answer 1

The link provided by user Pankaj Kapare was very helpful in resolving this issue. Basically, I was not actually passing the transaction initialised in the console application (root transaction manager) to any of the web api applications (which should have been obvious). Therefore, each web api application was initialising its own transaction rather than enlisting in the existing transaction.

To summarise the link, the main transaction initialised in the console application can be retrieved as a token using TransactionInterop and passed to the webapi as part of a request header or cookie:

if (Transaction.Current != null) 
{ 
    var token = TransactionInterop.GetTransmitterPropagationToken(Transaction.Current); 
    request.Headers.Add("TransactionToken", Convert.ToBase64String(token));                 
}

In the web api server application the transaction token can be retrieved and used to enroll in the main transaction. The article suggests the use of an action filter which works very well:

public class EnlistToDistributedTransactionActionFilter : ActionFilterAttribute 
{ 
    private const string TransactionId = "TransactionToken"; 

    /// <summary> 
    /// Retrieve a transaction propagation token, create a transaction scope and promote  
    /// the current transaction to a distributed transaction. 
    /// </summary> 
    /// <param name="actionContext">The action context.</param> 
    public override void OnActionExecuting(HttpActionContext actionContext) 
    { 
        if (actionContext.Request.Headers.Contains(TransactionId)) 
        { 
            var values = actionContext.Request.Headers.GetValues(TransactionId); 
            if (values != null && values.Any()) 
            { 
                byte[] transactionToken = Convert.FromBase64String(values.FirstOrDefault()); 
                var transaction = TransactionInterop.GetTransactionFromTransmitterPropagationToken(transactionToken); 

                var transactionScope = new TransactionScope(transaction); 

                actionContext.Request.Properties.Add(TransactionId, transactionScope); 
            } 
        } 
    } 

    /// <summary> 
    /// Rollback or commit transaction. 
    /// </summary> 
    /// <param name="actionExecutedContext">The action executed context.</param> 
    public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext) 
    { 
        if (actionExecutedContext.Request.Properties.Keys.Contains(TransactionId)) 
        { 
            var transactionScope = actionExecutedContext.Request.Properties[TransactionId] as TransactionScope; 

            if (transactionScope != null) 
            { 
                if (actionExecutedContext.Exception != null) 
                { 
                    Transaction.Current.Rollback(); 
                } 
                else 
                { 
                    transactionScope.Complete(); 
                } 

                transactionScope.Dispose(); 
                actionExecutedContext.Request.Properties[TransactionId] = null; 
            } 
        } 
    } 
}