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”);


                        //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”);



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


Comments (23)

  1. Sumeet Kumar says:

    Looks like tresting this feature requires the SQLServer 2005 Beta 2?

    Can you give a possible date for the SQL Server 2005 Beta 2 release?


  2. Angel says:


    I believe (but have not verified) that you can test this with the current Sql Server 2005 express package. the reason I am not sure is that I heard something about DTC being left out of the express package but never heard the final word.


    I am sorry but I have not heard any official word on when the beta will be released.


    Thanks! I may have made a mistake starting with system.transactions i see no end to the things I need to blog about this feature. the next blog will be something along the lines of "If delegation is so transparent why do you have to explain so much about it"

  3. Angel says:


    Looking throught the Sql 2005 Express documentation it sounds as if DTC is included but not configured by default.


    "There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. "

  4. Sumeet Kumar says:



    I will try it out and let you know.

  5. Sumeet Kumar says:


    The SQL Express 2005 setup crashes when run on my Win 2003 machine with VS 2005 Beta 1 installed.

    Perhaps it requires the Express version of VS Products?

    Anyway, guess I will just wait for the Yukon beta 2.

  6. Angel says:


    I am sorry to hear that, Visual Studio 2005 has Sql Express as one of the install options, did you try that?


  7. Sumeet Kumar says:

    Yes, that too fails with this log:


    EndOfSession?EndOfSession?[07/20/04,20:01:50] SQL Server 2005 Express Edition Beta: [2] Component SQL Server 2005 Express Edition Beta returned an unexpected value.



    Looks like something odd about my machine setup – perhaps because I had installed and uninstalled Yukon Beta 1 on the same o/s install.


  8. Angel says:


    Definitelly sounds like a machine problem, I would be surprised if yukon beta2 will install any better, you may be better off either looking for a solution to this set up problem on the newsgroups or setting up a virtual machine to test these beta releases.



  9. Sumeet Kumar says:



    Whidbey Beta 1 and SQl Express installed fine on a new instalaltion of Windows 2003.

    I was able to run the scenario and verify that transactiosn created using SQL Express transactions created using System.Transactions.TransactionScope do not appear in ComponentServices.TransactionList unless another connection is opened with a diff connectiong string.

    However, performance with SQLExpress is lower than with SQL Server 2000, especially for opening the first connection, where it takes 5-6 times longer.

    I am using "localhost/SQLExpress" as the server. the connection string is:

    "Server=localhostSQlExpress; Integrated Security=’true’; Enlist=’true’; Connection Timeout=’90’;Connection Lifetime=’30’; Encrypt=’false’; Packet Size=’4096′; Persist Security Info=’true’; Workstation ID=’localhost’; Connection LifeTime=’0′; Connection Reset=’false’; Max Pool Size =’100′; Min Pool Size =’0′; Pooling=’true’; "

    Also the purely local transaction does not seem to affect the performance of the command.execute – speed seems to be same with SQL Express v/s SQL Server 2000.

    Any tips to wring more performance out of this?


  10. Angel says:


    Great to hear that you got it working, don’t forget to enable msdtc "net start msdtc" to use this feature. I really can’t comment on opening first connection speed, opening a connection via shared memory on the same machine should be fairly fast.

    I can comment on your connection string.

    Connection Lifetime: this does not do what you think it does. Only use this connection string keyword when you are load balancing servers.

    Packet Size: SqlClient packet size default is 8k and again I would not touch it.

    Connection Reset: Don’t turn of connection reset unless you are connecting to Sql Server 7 and you know that you are not changing the state of the connection. Reset is very close to free on Sql 2000 and 2005.

    All of the other values (except Timeout) are defaults, you would think that it does not matter to add them but we need to check each connection string keyword against your security settings, I would not add any default values to your connection string. The final connection string should look like this:

    "Server=.SQlExpress; Integrated Security=’true’; Connection Timeout=’90’ "

    Hope this helps,


  11. Angel says:

    One more thing I forgot to add. If you are using more than one connection open in your transaction scope then the second connection open will automatically promote the transaction. Distributed transactions are very expensive, specially the first time arround. You can tell that a transaction has been promoted because it will appear in the transaction statistics.

    This may be the delay that you are seeing

  12. Sumeet Kumar says:


    1) Thank you for the tips re the connection string.

    2) Also, then, one should try to ensure a connection in use in closed before a new one is opened to keep the transaction to a lightweight one.

    So if multiple commands have to be executed in parallel, (e.g. updating many rows in parallel to reduce server latency), one should use asynchronous command execution against the single open connection?

    3) After executing the sqlCommand, are these statements oK?




    or should I replace the 3 lines with:


    Thanks a lot.

  13. Angel says:


    2) No, it does not matter whether the first connection is open or closed, as soon as you open the second connection we will promote the transaction. The first connection will not really close, we place it in a subpool especially made for enlisted connections and it will continue being enlisted waiting for a commit or rollback.

    You can definitelly use MARS and Async to try to improve performance, if this means that you will not open a second connection at all it will be a definite win. If you end up promoting the transaction anyway it is probably not going to be worth the effort, and your code will suffer in terms of maintainability.

    3) I would not Cancel the command unless you really want to cancel a running command on the server. I would certainly encourage you to call Dispose on the command and on any other object that implements IDisposable. In this case Command.Dispose does not do anything meaningfull but it does not hurt. I would make sure that the cn.Close and/or cn.Dispose happens under all circumstances by placing the connection in a "using" block.

  14. pk says:

    Great stuff, but I’m still a little confused about when the automatic promotion occurs. I maybe wrong here, but currently if you open a connection with the same connection string you have a good chance or re-using the original connection. I’m assuming this "subpool" means that the specific connection can never be re-used until the transaction has completed (or whenever the clever optimized Tx stuff kicks in)…therefore any subsequent connection you open will automatically be promoted?

  15. Angel says:


    Great point! in the case discussed above we were opening two connections to two different servers, so it really did not matter whether we closed the first connection or not. Does it make a difference if we use the exact same connection string?

    The answer is still no, if you start a transactionscope, open connection close connection open connection you will force promotion. Remember, the delegated transaction is for all purposes similar to a local transaction until it needs to get promoted. It is too hard to get a local transaction to work across two connections so for delegated transactions we effectively hijack the first connection in the distributed transaction subpool and force you to create a new connection on the second open call.

    You can try this by setting the Max Pool Size, if you set it to 1 you will not be able to open the second time.

  16. waterboy says:

    Ping Back来自:blog.csdn.net

  17. Here’s a small issue you may need to watch out for when using a System.Transactions transaction with…

  18. rape stories says:

    Your article is prety nice. It’s a pity that i didn’t see it more later.