A small detail on TransactionScope and LTM

As you probably already know, the Lightweight Transaction manager (LTM) is the first choice of TransactionScope. This means that if certain conditions are met, one or more connections inside a transaction scope will be wrapped in a single transaction –not a distributed one-.

One common scenario for this is having two connections of the same database inside a transaction scope. In this scenario we would definitely like to have the transaction promoted to a distributed transaction ONLY if you are using different connection strings. However note that the following syntax (with the same connection strings) causes the transaction to be promoted to a distributed transaction –hence the utilization of MSDTC-.

static void TestLTM()

{

using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())

{

using (SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))

{ //do some stuff

using (SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))

{ //do some other stuff }

}

ts.Complete();

}

}

So despite the fact that the connection strings are identical the transaction scope considers the second connection as a cause for promoting the transaction to distributed transaction. If you want to have LTM transactions and not promoted MSDTC transactions when using two identical connections, use the following code sample instead:

 static void TestLTM()
{
    using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
    {
        using (SqlConnection conn1 = new SqlConnection(ConfigurationManager.

 ConnectionStrings["connectionString"].ConnectionString))
        {
            //do some stuff
        }

        using (SqlConnection conn2 = new SqlConnection(ConfigurationManager.

 ConnectionStrings["connectionString"].ConnectionString))
        {
            //do some other stuff
        }

        ts.Complete();
    }
}