Inadvertently Promoting a Local Transaction to a Distributed Transaction
I recently dealt with a customer issue where they were troubleshooting MSDTC, and upon hearing the explanation of exactly what they were doing, I was a bit surprised that a distributed transaction was being used.
Upon further investigation, they were unintentionally promoting a local transaction to a distributed transaction. The reason behind this that they were using the TransactionScope class to ensure the atomic nature of multiple operations. Much like we already know about transactions, if operation 1 and operation 2 happen within a transaction, they either both succeed or they both fail, either resulting in a commit or a rollback.
The transaction’s inadvertent promotion to a distributed transaction came from their implementation though. Instead of reusing the same SqlConnection object for both of the operations’ SqlCommand objects, they created different ones. Even though they were the same connection string, this caused the distributed transaction when you wouldn’t normally expect a distributed nature.
Before we dive into the actual code reproduction, I’m first going to create an Extended Events session so that we can monitor both the sql_transaction and the dtc_transaction events. Below is my session definition:
create event session DtcMonitoring
on server
add event sqlserver.dtc_transaction
(
action
(
sqlserver.server_principal_name,
sqlserver.sql_text
)
where
(
sqlserver.server_principal_name = N'stringer\administrator'
and sqlserver.client_app_name = N'DtcTesting'
)
),
add event sqlserver.sql_transaction
(
action
(
sqlserver.server_principal_name,
sqlserver.sql_text
)
where
(
sqlserver.server_principal_name = N'stringer\administrator'
and sqlserver.client_app_name = N'DtcTesting'
)
)
add target package0.event_file
(
set filename = N'\\<path to XEL file>\DtcMonitoring.xel'
)
with
(
event_retention_mode = allow_single_event_loss,
max_event_size = 0 KB,
memory_partition_mode = none,
track_causality = off,
startup_state = off
);
go
-- start the session
--
alter event session DtcMonitoring
on server
state = start;
go
I am filtering this, as I know in my repro application I am setting the Application Name portion of my connection string to “DtcTest”. This will keep the noise down and allow me to concentrate on the events I care about.
Now onto the code that reproduces this behavior. Below I have a method that utilizes two SqlConnection objects, therefore promoting a transaction to a distributed transaction. In the following code examples, I have nested the XE events that were fired corresponding to when they were fired as a result of particular code that ran.
public void PerformDistributedTransaction()
{
using (TransactionScope distrTrans = new TransactionScope())
{
SqlConnection dbConn1 = new SqlConnection(ConnectionString);
SqlConnection dbConn2 = new SqlConnection(ConnectionString);
SqlCommand sqlCmd1 = new SqlCommand();
sqlCmd1.Connection = dbConn1;
sqlCmd1.CommandText = "select * from humanresources.department;";
SqlCommand sqlCmd2 = new SqlCommand();
sqlCmd2.Connection = dbConn2;
sqlCmd2.CommandText = "select * from humanresources.employee;";
try
{
dbConn1.Open();
dbConn2.Open();
sqlCmd1.ExecuteNonQuery();
sqlCmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConn1.Dispose();
dbConn2.Dispose();
sqlCmd1.Dispose();
sqlCmd2.Dispose();
}
distrTrans.Complete();
}
}
Note: In the above code, I am being explicit with my SqlConnection.Dispose() calls, throwing them in the finally block of my try/catch. I am by no means illustrating this as best practice, but it keeps down on code clutter instead of the using block to call IDisposable.Dispose() at the end of it, allowing concentration on the lesson at hand.
I have highlighted the important parts of the code in yellow. By instantiating two SqlConnection objects even with the same connection string, and having these objects separately set as the SqlCommand.Connection properties for the individual operations, I am causing the transaction to get promoted to a distributed transaction.
This is precisely the behavior I was seeing, and the behavior that wasn’t necessary or desired. The fix to this was utilizing a single SqlConnection object for both operations SqlCommand.Connection properties. Below would be an example of how this is done:
public void PerformNonDistributedTransaction()
{
using (TransactionScope nonDistrTrans = new TransactionScope())
{
SqlConnection dbConn1 = new SqlConnection(ConnectionString);
SqlCommand sqlCmd1 = new SqlCommand();
sqlCmd1.Connection = dbConn1;
sqlCmd1.CommandText = "select * from humanresources.department;";
SqlCommand sqlCmd2 = new SqlCommand();
sqlCmd2.Connection = dbConn1;
sqlCmd2.CommandText = "select * from humanresources.employee;";
try
{
dbConn1.Open();
sqlCmd1.ExecuteNonQuery();
sqlCmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConn1.Dispose();
sqlCmd1.Dispose();
sqlCmd2.Dispose();
}
nonDistrTrans.Complete();
}
}
After this code logic runs, the logged events show us that we are no longer resorting to a distributed transaction for this operation.
In short, if you are seeing unintended distributed transactions, one place to look is how you are using your connection objects. You may also be unintentionally promoting a transaction to a distributed transaction.
Thomas Stringer – SQL Server Premier Field Engineer
Twitter: @SQLife
Comments
- Anonymous
November 05, 2014
This is a messy topic and more complex then suggested. There is supposed to be a feature called promotable transactions msdn.microsoft.com/.../ms172070(v=vs.80).aspx My understanding is a light weight transaction is supported. It depends on the SQL Server Version and meeting escalation rules. I do not want to repeat an entire thread stack overflow thread. Take a look at the issue. stackoverflow.com/.../transactionscope-automatically-escalating-to-msdtc-on-some-machines