[MSDTC] How to Setup Microsoft Distributed Transaction Coordinator

Ever faced a situation where you need to make changes to a record in 2 different SQL Server databases that are located in two different instances in different servers and need to ensure the changes made to the both of the databases are successful before commit? Before you can do so, you need to configure Microsoft Distributed Transaction Coordinator (MSDTC) and this post will guide you on how to do so.

This post will not go into what is MSDTC, but if you're curious and want to know what it is all about, do check out the following links.
https://msdn.microsoft.com/en-us/library/aa940166(v=winembedded.5).aspx
https://blogs.msdn.microsoft.com/florinlazar/2004/03/04/what-is-msdtc-and-why-do-i-need-to-care-about-it/

There are few things that you need to know before configuring MSDTC.

• The MSDTC need to be configured on all servers that are involved in the transaction.
• If you're using transaction in C# / VB with TransactionScope and requires to make changes to 2 databases located in two different instances in different servers, in this case, MSDTC has to be configured on server that launch the application & the 2 database servers. (Total 3 Servers)
• If you're using transaction in trigger or stored procedure that make changes to its own database and another database located in another server, in this case, MSDTC has to be configured on the 2 database servers.
• Firewall clearance for all servers that configure MSDTC.

The following steps will only shows the minimum settings required for the distributed transaction to work. If you need or requires the other settings, do not hesitate to make changes accordingly.
1) Open up run window, type mmc and click OK button.

2) Once the MMC console launched, select File which is located at the top menu, click Add/Remove Snap-in...

3) On the Add or Remove Snap-ins window, under Available snap-ins list box, look for Component Services, select it and click Add > button or double click it. It will then appear under Selected snap-ins list box. Once you are done, click OK button.

4) Back to the MMC console window. On the left pane, expand Component Services node, Expand Computers node, right click on My Computer and click Properties.

5) On the My Computer Properties window, select MSDTC tab, tick Use local coordinator and click OK button.

6) Back to the MMC console window, further expand My Computer node, expand Distributed Transaction Coordinator, right click on Local DTC and click Properties.

7) On the Local DTC Properties window, select Security Tab.
Tick Network DTC Access.
Tick Allow Inbound,
Tick Allow Outbound (This is not required for servers that which did not initiate the transaction). Once you are done, click OK button.

Note: The following figure shows the minimal settings required. If you requires the other settings, do not hesitate to change it.

The following steps guide you on how to allow MSDTC to pass through firewall

1) Open up firewall window and select Allow an app or feature through Windows Firewall located at the left pane.

2) Look for Distributed Transaction Coordinator, tick private / public / both depending on whether your transaction is in private / public / in both network. Click OK button.

That is all for the configuration part. Now, to verify whether your MSDTC has been configured successfully, you can test it out based on one of the following.

1) Connect to one of the database server with SQL Server Management Studio, Write a BEGIN TRANSACTION statement to make data change to current database and another database in different server with linked server.

2) Use the TransactionScope Class in C# / VB to make changes to 2 different database servers.

What is Race Condition C# & VB

You might be wondering what is race condition or do you even need to care of what it does to your application? Well yes, you do in fact need to know what it is as it may lead to your application not performing what you intended it to be.

A race condition is a situation where 2 or more processes / threads is accessing the same resource at the same time, in which lead to undesired result to occur.

Let's use the following code to illustrate how race condition occurred. Assume that you want to increase the _value by 1 each time the IncreaseValue() is called.

[C#]

private static int _value = 0;

private static int GetValue()

{

    return _value;

}

private static int SetValue(int value)

{

    value += 1;

    return value;

}

public void IncreaseValue()

{

    var value = GetValue();

    _value = SetValue(value);

}

[VB]

Private Shared _value As Integer = 0

Private Function GetValue() As Integer

    Return _value

End Function

Private Function SetValue(ByVal value As Integer) As Integer

    value = value + 1

    Return value

End Function

Public Sub IncreaseValue()

    Dim value As Integer = GetValue()

    _value = SetValue(value)

End Sub

The following table shows how the code execute if you are running the above code in a single thread environment. In this case, race condition will not occur.

Step	Thread 1	Value in _value
1	Call IncreaseValue() method	0
2	Call GetValue() and value obtained is 0	0
3	Call SetValue() and value obtained is 1 and assign 1 to _value	1
4	Exit IncreaseValue() method.	1
5	Call IncreaseValue() method.	1
6	Call GetValue() and value obtained is 1	1
7	Call SetValue() and value obtained is 2 and assign 2 to _value	2
8	Exit IncreaseValue() method.	2

But imagine that there are more than 1 threads accessing the static variable _value at the same time.

Step	Thread 1	Step	Thread 2	Value in _value
1	Call IncreraseValue() method	2	Call IncreaseValue() method	0
3	Call GetValue() and value obtained is 0	4	Call GetValue() and value obtained is 0	0
5	Call SetValue() and value obtained is 1 and assign 1 to _value	6	Call SetValue() and value obtained is 1 and assign 1 to _value	1
7	Exit IncreaseValue() method	8	Exit IncreaseValue() method	1

The final result supposed to be 2, but due to the original value obtained by both of the thread is 0, both of them will end up having the same final value of 1 and assign it back to _value. So this is how race condition occurred.

To handle the race condition, you can rely on any one of the synchronization primitives as shown here https://msdn.microsoft.com/en-us/library/ms228964(v=vs.110).aspx.

You may refer to my previous blog posts on how to use each of the synchronization primitives.
Mutex: http://jaryl-lan.blogspot.com/2015/08/thread-synchronization-with-mutex.html
SpinLock: http://jaryl-lan.blogspot.com/2015/08/thread-synchronization-with-spinlock.html
Semaphore: http://jaryl-lan.blogspot.com/2015/11/thread-synchronization-with-semaphore.html

For this example, we will use lock keyword.

[C#]

private static int _value = 0;

private object _lock = new object();

private static int GetValue()

{

    return _value;

}

private static int SetValue(int value)

{

    value += 1;

    return value;

}

public void IncreaseValue()

{

    lock (_lock)

    {

        var value = GetValue();

        _value = SetValue(value);

    }

}

[VB]

Private Shared _value As Integer = 0

Private _lock As Object = New Object()

Private Function GetValue() As Integer

    Return _value

End Function

Private Function SetValue(ByVal value As Integer) As Integer

    value = value + 1

    Return value

End Function

Public Sub IncreaseValue()

    SyncLock _lock

        Dim value As Integer = GetValue()

        _value = SetValue(value)

    End SyncLock

End Sub

At any one time, only 1 thread can access the code in lock statement. So the subsequent threads have to wait for the current thread in the lock statement to complete its execution and exit from lock statement block. Thus, race condition did not occur. The following table shows how the code executes with lock keyword.

Step	Thread 1	Step	Thread 2	Value in _value
1	Call IncreraseValue() method	2	Call IncreaseValue() method	0
3	Execute lock keyword	4	Execute lock keyword.	0
5	Call GetValue() and value obtained is 0			0
6	Call SetValue() and value obtained is 1 and assign 1 to _value			1
7	Exit from lock statement	8	Call GetValue() and value obtained is 1	1
9	Exit IncreaseValue() method	10	Call SetValue() and value obtained is 2 and assign 2 to _value	2
		11	Exit from lock statement	2
		12	Exit IncreaseValue() method	2

It is not a must to use synchronization primitives to handle race condition. There are many other ways to handle race condition based on different kind of situations or scenarios.

So for example if you want to assign a value to a variable or instantiate an object and assign it to a variable if it is null, you can rely on lazy loading or static initialization.

If is SQL Server related, you can rely on one of the following.
Hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
sp_getapplock: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql
sp_releaseapplock: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-releaseapplock-transact-sql

You may also be in a situation where you just need to revise your application design without relying on the above listed technologies to handle race condition.