Whidbey ADO.NET 2.0 System.Transactions Distributed Transactions. TransactionScope

Let’s take a look at how the new System.Transactions Distributed Transactions work with ADO.NET 2.0. I am going to start with the best and easiest way to use distributed transactions in managed code, the TransactionScope. The following code is (or should be) bullet proof, without even realizing it you have bypassed most of the hidden problems that you can run into.

            using(TransactionScope transactionscope1 = new TransactionScope()) {

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

            sqlconnection1.Open(); //this autoenlists in the transaction scope.

                                    //do your work here.

                        }//SqlConnection using statement completed

                        // 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.

Let’s analize this line by line:

using(TransactionScope

This is CRUCIAL, relying on the using construct to create your scope makes everything so simple that I can’t imagine not doing it. You can easily see your scope and what it is affecting, that is nice. The truly important reason for the using statement though is that you _need_ to guarantee that this scope is going to be Disposed. The way the transaction scope is designed it will Commit or Rollback on TransactionScope.Dispose, leaving this to the GC can be tremendously expensive since System.Transactions Distributed Transactions have the same 1 minute timeout default. Locking multiple databases for up to a minute is going to kill scalability in your application. I say kill but I really mean devastate, destroy, shatter and make you pull your hair out… you get the idea. No, it is not enough to call TransactionScope.Dispose manually in your code unless you place it in the finally block of a try finally block and all of your work is done in the try. If an exception in your code throws out of your code before Dispose is called you are going to hate this feature.

new TransactionScope()

It is interesting to see what the overloads are for the TransactionScope constructor… WOA 14 overloads. Man I am glad I don’t own testing this object J. I am not even going to try going through this list I am just going to note that the default no parameter constructor is equivalent to the TransactionScope(TransactionScopeOption.Required). For those of you familiar with distributed transaction terminology, yes, this means that the Scope requires a transaction. Our scope will run in the scope of an existing distributed transaction if one exists, if no transaction exists the Scope will start one. Yes, this means that you can nest TransactionScopes! Most (but not all) of the other TransactionScopeOptions will be familiar to people familiar with Distributed Transactions, I will revisit this at a later time in this blog. Take a look at the overload that takes in a TransactionOption, this allows you to set an IsolationLevel.

using (SqlConnection

The exact same comment as for using TransactionScope, nothing but a “using” or a try finally guarantees the SqlConnection closes. You really don’t want your connection to leak here.

sqlconnection1.Open()

Magic, we call connection open and our connection automatically enlists on the scope, what is going on here? Take a look at the System.Transactions.Transaction.Current static property, this is set to null by default, but check it after you create a new TransactionScope and you will see that it gets set to a LightweightTransaction. Whenever we open a new SqlClient or Oracle Client connection with Enlist=true (the default) on the connection string we will check this static Transaction.Current property, if this property is set our Connection will automatically enlist into this LightweightTransaction. If you are connecting to Sql Server 2005 the transaction will continue being lightweight after Open, connecting to any other backend (Sql Server 2000, Sql Server 7, Oracle) will result in immediate promotion to a full Distributed Transaction. You can track these transactions in the awesome TransactionStatistics view of the Admin->Component Services utility.

//do your work here.

Pretty self explanatory, the connection is open, the scope is running, you are using expensive resources. I would recommend doing only Updates, Inserts and Deletes here, doing selects (which may be unfortunately necessary) will lock the database(s) unnecessarily. Any non database work should be done outside of the scope. By default System.Transactions have a 1 minute timeout, you can play with the TransactionScope constructor overloads to change this.

Important: What happens if an exception happens here? Well unless you handle it yourself your code will throw. First the SqlConnection Dispose will happen since this is guaranteed by the using (SqlConnection, then TransactionScope.Dispose will be called since this is guaranteed by the using(Transactionscope. On TransactionScope Dispose we check the Consistent property (false by default), in this case it is set to false (the throw happens in //do your work here, and Consistent=true never gets called) and the Rollback is _guaranteed_. It’s all good <g>

}//SqlConnection using statement completed

This guarantees that SqlConnection.Dispose will be called here, it is important to note that this is being done _before_ we commit or rollback the TransactionScope. This is not _necesary_, but it is highly recommended. You sidestep a lot of problems by closing the connection here and letting us baby sit it for you until the Distributed Transaction completes. We take the connection and place it in a special subpool for connections currently enlisted in a distributed transaction, we listen to the distributed transaction completed event and then return the connection to the available connection pool. If you don’t close the connection you cannot reuse it until the distributed transaction has completed (this is _not_ right after TransactionScope.Dispose!), you could listen to the Distributed Transaction completed event but then you get into a race condition with our connection clean up code that gets triggered on this event, this could get ugly.

transactionscope1.Consistent = true;

This tells the TransactionScope to Commit on TransactionScope.Dispose.

}// 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.

Nothing new to add here. We are done.

Rambling out

Standard Disclaimer. This post is provided “AS IS” and confers no rights. Anything posted here is strictly my opinion. There are almost certainly going to be a number of errors on this post.