Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

There is a special partnership between System.Transactions and Sql Server 2005, and no, it is not the fact that we begged the Enterprise Services team to ship this feature on whidbey and it is not (only) the fact that this is the only way to get distributed transactions to work inproc in Yukon (will talk about that in another blog). What makes the relationship special is that Sql Server 2005 understand Lightweight Transactions, uses Lightweight Transactions whenever possible, optimizes the use of Lightweight Transactions and does all this as transparently as we have been able to make it.

So what is a LightweightCommittableTransaction? The long answer is that it is what you get when you call System.Transactions.Transaction.Create() with the default DefaultTransactionManager set to LightweightTransactionManager. The short answer is that it is an in memory transaction that can be promoted to a full dtc. Neither of these definitions may be what you are looking for, so I have put this one together all by my lonesome:

Question>What is a LightweightCommittableTransaction as far as ado.net is concerned?

Angel>A transaction that looks like a distributed transaction, smells like a distributed transaction and tastes like a distributed transaction. Oh yeah, it can be a lot faster btw.

Let’s see this in action, bring up the trusty Component Services Transaction Statistics

(start->control panel->administrative tools->Component Services->Component Services->Computers->MyComputer->Distributed Transaction Coordinator -> Transaction Statistics)

This tool tracks distributed transactions as they happen, let’s give it a whirl. You should already be familiar with the code below from a previous blog, I have modified it slightly to show off a transaction being delegated, then promoted.

using System;

using System.Data.SqlClient;

using System.Transactions;

    public class Repro {

 

        public static int Main(string[] args) {

            using(TransactionScope transactionscope1 = new TransactionScope()) {

                       

                        //Delegation only works against Sql Server 2005, for this example to work this connection must point to one.

                        using (SqlConnection sqlconnection1 = new SqlConnection(SqlServer2005ConnectionString)) {

                                    sqlconnection1.Open(); //The connection enlists, but does not promote

                                    //do your work 1 here.

                     }

                        Console.WriteLine("Check your Transaction Statistics Active transactions here, then press enter");

                        Console.ReadLine();

                        //This connection can point to any Backend that supports DTC. Sql Server 7, 2000, 2005 or Oracle

                        using (SqlConnection sqlconnection2 = new SqlConnection(ConnectionString2)) {

     sqlconnection2.Open(); //The connection enlists, automatically promotes the transaction.

                                    //do your work 2 here.

                        }

    Console.WriteLine("Check your Transaction Statistics Active transactions here, then press enter");

                        Console.ReadLine();

                        // Set the scope to commit by setting the following property:

                        transactionscope1.Consistent = true;

            }// when the TransactionScope is disposed it will check the Consistent property. If this is true the DTC will commit, if it is false it will roll back.

            return 1;

        }

    }

What just happened? Well if you run this code to the first ReadLine you will see that there are no Transactions Active showing! We have created a TransactionScope, we have opened a connection that enlists into this scope, but since we are connected to Sql Server 2005 and we don’t have the need for a full distributed transaction we have Delegated the promotion of the transaction. We have opened a local transaction with all of the performance implications that this implies, and all of the “work 1” will be done under this local transaction. After the first ReadLine we open a connection to a second server which could or could not be the exact same server that we are connecting to with sqlconnection1. On sqlconnection2.Open the Lightweight Transaction realizes that it no longer can remain “light” and converts into a full COM+ distributed transaction, at that point we will finally be able to see an Active transaction show in our Component Services tool.

Wait, wait! What about the local transaction that we are using against the first server? Nothing to worry about, we will promote the local transaction into the full distributed transaction, you will not even know that the first transaction was only local. Most of the time the MSDTC of the first server will own the distributed transaction from this point on. Why not all of the time? Well this depends on a heuristics feature that the Enterprise Service is working on, I really don’t know what the current state of this feature is, but the basic idea is that they will keep track of when a transaction is getting promoted and be able to promote at the optimal time to improve performance.

Bottom line, we want this feature to be completely transparent to the end user from the ado.net point of view. It should not matter whether delegation happens or not you should always get the same robust Distributed Transaction behavior you know and love, it’s just that sometimes, it will work a lot faster.

Standard Disclaimer, All information posted here is “AS IS” and confers no rights. This is not a finished article and it is very likely going to contain some errors.

Rambling out.