Whidbey ado.net 2.0 Manually enlisting into System.Transaction distributed transaction.

I have gone over the TransactionScope in fair detail, it is now time to look at manual enlistment, the second way to use System.Transactions Distributed Transactions with ADO.NET. Unlike the TransactionScope example wich was complete and bullet proof this is neither. Here we still need to specify the logic for rolling back the transaction and we run the risk of leaking the transaction. I have not attempted to do this because quite frankly I am not certain that I would get it right in a generic enough way that can be copied pasted into your project.

            ICommittableTransaction icommittabletransaction1 = Transaction.Create(); // LightweightCommittableTransaction

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



                        //Do your work here




Again, let's go line by line.

ICommittableTransaction icommittabletransaction1 = Transaction.Create();

Transaction is the System.Transactions.Transaction, Create is a Static method that returns an ICommittableTransaction. It has overloads for a TransactionOptions object (this is not necessarily what you may expect by the name, another blog on this later) and for timeout (default 1minute). The Create() method is a lot more complicated than it looks, under the covers it relies on the DefaultTransactionManager to create the type of transaction that you are currently working on. By default the default transaction manager is set to be of type LightweightTransactionManager so we get a LightweightCommittableTransaction back in this case. Lightweight transactions are the true power behind the System.Transaction class, they are in memory transactions with all of the speed benefits that that entails and they can be promoted to distributed transactions with all their power. A truly astonishing feature for the Enterprise Services team. We have modified Sql Server 2005 to be able to work with these lightweight transactions… up to a point, another blog on this later.

Important: on Transaction.Create the Transaction.Current property is not set, connections will _not_ automatically enlist into this transaction. There is nothing stopping you from manually setting the static property Transaction.Current to a transaction that you have started with Create however: ex: Transaction.Current = (ITransaction) icommittabletransaction1;


No magic, but worth repeating. The connection opens but does not automatically enlist into the LightweightCommittableTransaction.


Manually enlisting the connection into the LightweightCommittableTransaction. Manually enlisting connections into distributed transactions was a new feature with the v1.1 version of the framework, to manually enlist into an Enterprise Services DTC we added the EnlistDistributedTransaction method in the connection. I bring this up because our first stumbling block in integrating System.Transactions was that we could not overload this method to handle both ContextUtil and System.Transactions. I believe (but don't quote me on that) that overloading was not possible due to language issues with vb.net, but most importantly, there are some minor behavior differences that come up due to transaction delegation. The most important of these differences is that _you cannot unenlist_ the connection. That is right, once you manually enlist into a distributed transaction you can’t enlist the connection into the null transaction or into a different transaction like you could (but I hope you did not do) with Enterprise Service transactions. There is a reason for this, I will blog on this later. In a nutshell the first connection that you open _can_ be special due to delegation and transaction promotion.


Not much to say here, we don’t have the strange commit or rollback on dispose based on a property that works so well for the TransactionScope, you need to Commit or Rollback manually.

I believe I have promised at least three more blogs on this blog alone, I imagine I am going to be talking about this feature for a while longer. I am trying to convince Sushil Chordia to blog on Notifications (mostly so I don’t have to J ) but there are a lot of other areas that I really want to talk about.

Standard Disclaimer: This post is provided “AS IS” and confers no rights, there are almost certainly going to be errors in this post.

Rambling out,