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.
Comments
- Anonymous
July 12, 2004
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?
Thanks - Anonymous
July 12, 2004
Very Cool! - Anonymous
July 13, 2004
Sumeet,
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.
http://lab.msdn.microsoft.com/express/sql/
I am sorry but I have not heard any official word on when the beta will be released.
Bill,
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" - Anonymous
July 13, 2004
Sumeet,
Looking throught the Sql 2005 Express documentation it sounds as if DTC is included but not configured by default.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
"There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. " - Anonymous
July 16, 2004
Angel,
Thanks.
I will try it out and let you know. - Anonymous
July 16, 2004
Angel,
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. - Anonymous
July 19, 2004
Summet,
I am sorry to hear that, Visual Studio 2005 has Sql Express as one of the install options, did you try that?
Angel - Anonymous
July 19, 2004
The comment has been removed - Anonymous
July 20, 2004
Sumeet,
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.
Sorry,
Angel - Anonymous
July 24, 2004
The comment has been removed - Anonymous
July 25, 2004
Sumeet,
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,
Angel - Anonymous
July 25, 2004
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 - Anonymous
July 26, 2004
The comment has been removed - Anonymous
July 27, 2004
Sumeet.,
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. - Anonymous
August 03, 2004
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? - Anonymous
August 04, 2004
The comment has been removed - Anonymous
November 02, 2004
Ping Back来自：blog.csdn.net - Anonymous
April 14, 2006
Here's a small issue you may need to watch out for when using a System.Transactions transaction with... - Anonymous
June 19, 2006
Your article is prety nice. It's a pity that i didn't see it more later. - Anonymous
November 05, 2007
PingBack from http://www.luggagelocks.net/transaction/11/ - Anonymous
January 26, 2008
PingBack from http://bogartfin.breastbondagenewsank.com/2008/01/27/lightweight-promotable-transactions-sqldatareader-and-commitment/