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

  • Článek

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.

Comments

  • Anonymous
    July 07, 2004
    Excellent example Angel. This is one feature that I'm still in shock over. Played with it briiefly when I first found out about it and a little more since then - you guys deserve serious credit for doing this AND making it so darned easy.
  • Anonymous
    July 07, 2004
    Bill,
    Thanks for the comments and the link, now its my turn to link to one of your posts where you "threaten" to come out with a System.Transaction example.

    http://msmvps.com/williamryan/archive/2004/07/01/9385.aspx

    The real reason why I am going into this feature so in depth is because we are very short on documentation for this in beta 1. I am desperately looking for people who will give this a whirl in beta1 timeframe.

    Angel
  • Anonymous
    July 07, 2004
    Funny you should mention that. I'm a little behind b/c I thought I'd have it monday, but I have a few things just about ready to go. Was looking to post to MSDN but the InfoPath template doesn't have ADO.NET 2.0 available. I'll drop you an email tomorrow with the shell of two of the things I'm working on. You're right, this is way too cool to not have a ton of good examples done with it ;-)
  • Anonymous
    July 07, 2004
    The comment has been removed
  • Anonymous
    July 11, 2004
    One question ....

    You mentioned that ...

    System.Transactions.Transaction.Current

    ... is a static property. So does this imply that all threads in the current AppDomain would be sharing the same Transaction Context? Or is it based on the current thread?


    If it is thread-based...is there a way to enlist other threads into the same context such that their own call to System.Transactions.Transaction.Current would return the same transaction context?

    Basically just trying to understand the coupling aspects either it enforces, or what one might need to consider when building with this model.

    Joe
  • Anonymous
    July 12, 2004
    Joe,
    Great question, I had to go to the source to track this down. System.Transaction.Current lives in thread local storage (TLS) so it is Scoped per thread. I am still looking at what the best way to have multiple threads participate in the same Transaction, but as a worst case scenario you can always manually enlist connections into a specific System.Transactions Transaction using the connections EnlistTransaction method.

    hope this helps,
    Angel
  • Anonymous
    July 12, 2004
    Ok, some more information on using transactions in multiple threads. The main idea is that you can clone the transaction and pass the cloned transaction to another thread. You can rollback on any cloned transaction, but you can only commit on the original transaction, this bypasses all of the possible multi-threaded issues. I will have to get all of this information in a blog some time.
  • Anonymous
    December 06, 2004
    System.Transactions and SqlConnection.EnlistTransaction and System.Transactions.Transaction.Create .. oh it goes .. on ..
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/662032-ado-connection-still-connecting-after/2
  • Anonymous
    June 08, 2009
    PingBack from http://menopausereliefsite.info/story.php?id=1094
  • Anonymous
    June 09, 2009
    PingBack from http://insomniacuresite.info/story.php?id=100
  • Anonymous
    June 09, 2009
    PingBack from http://hairgrowthproducts.info/story.php?id=260