Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

re: Whidbey ADO.NET Delegated Transactions with System.Transactions & Yukon

Sumeet Kumar — Tue, 13 Jul 2004 11:17:00 GMT

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

re: Whidbey ADO.NET Delegated Transactions with System.Transactions & Yukon

Bill — Tue, 13 Jul 2004 13:18:00 GMT

Very Cool!

re: Whidbey ADO.NET Delegated Transactions with System.Transactions & Yukon

Angel — Tue, 13 Jul 2004 16:13:00 GMT

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"

re: Whidbey ADO.NET Delegated Transactions with System.Transactions & Yukon

Angel — Tue, 13 Jul 2004 16:32:00 GMT

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

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Sumeet Kumar — Sat, 17 Jul 2004 04:33:00 GMT

Angel,

Thanks.

I will try it out and let you know.

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Sumeet Kumar — Sat, 17 Jul 2004 04:57:00 GMT

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.

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Mon, 19 Jul 2004 18:11:00 GMT

Summet,

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

Angel

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Sumeet Kumar — Tue, 20 Jul 2004 14:35:00 GMT

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.
***EndOfSession***
"

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

Sumeet

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Wed, 21 Jul 2004 00:38:00 GMT

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

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Sumeet Kumar — Sun, 25 Jul 2004 08:17:00 GMT

Angel,

Success!

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=localhost\SQlExpress; 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?

Thanks

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Sun, 25 Jul 2004 19:03:00 GMT

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

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Sun, 25 Jul 2004 19:05:00 GMT

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

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Sumeet Kumar — Tue, 27 Jul 2004 05:30:00 GMT

Angel,

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?

cmd.Cancel()
cmd.Dispose()

cn.Dispose

or should I replace the 3 lines with:
cn.Close

Thanks a lot.

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Tue, 27 Jul 2004 17:29:00 GMT

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.

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

pk — Wed, 04 Aug 2004 14:56:00 GMT

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?

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

Angel — Wed, 04 Aug 2004 16:52:00 GMT

PK,

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.

Manejo de transacciones en ADO.NET 2.0

Tecnocrata Blog — Thu, 16 Sep 2004 17:16:00 GMT

Manejo de transacciones en ADO.NET 2.0

Tecnocrata Blog — Thu, 16 Sep 2004 17:17:00 GMT

Transaction Processing in ADO.NET 2.0

waterboy — Wed, 03 Nov 2004 02:25:00 GMT

Ping Back来自：blog.csdn.net

Lightweight Promotable Transactions, SqlDataReader and Commitment.

Data Access blog — Sat, 15 Apr 2006 00:50:57 GMT

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

re: Whidbey ADO.NET Promotable Transactions with System.Transactions & Yukon

rape stories — Tue, 20 Jun 2006 02:14:27 GMT

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

Luggage locks » Transaction

Luggage locks » Transaction — Tue, 06 Nov 2007 14:05:33 GMT

PingBack from http://www.luggagelocks.net/transaction/11/

Blog about Bogart » Blog Archive » Lightweight Promotable Transactions, SqlDataReader and Commitment.

Sun, 27 Jan 2008 14:51:14 GMT

PingBack from http://bogartfin.breastbondagenewsank.com/2008/01/27/lightweight-promotable-transactions-sqldatareader-and-commitment/