Extending Lightweight Transactions in SqlClient

MSDN Blog Postings » Extending Lightweight Transactions in SqlClient

MSDN Blog Postings » Extending Lightweight Transactions in SqlClient — Wed, 26 Mar 2008 07:43:11 GMT

PingBack from http://msdnrss.thecoderblogs.com/2008/03/26/extending-lightweight-transactions-in-sqlclient/

re: Extending Lightweight Transactions in SqlClient

Nikolay Vasilyev — Wed, 26 Mar 2008 17:20:20 GMT

There are several ways to handle this well known issue with transaction "auto-promotion" on second connection being opened. Like e.g. the one suggested by you with a ConnectionScope class (and the other developers like me create our own envelope classes to bind a TransactionScope instance with a SqlConnection instance).

Should this new approach be somehow more convenient than ConnectionScope? Or is this just an alternative approach suitable for SQL Server 2008 only?

re: Extending Lightweight Transactions in SqlClient

alazela — Wed, 26 Mar 2008 20:43:56 GMT

I'd think a bit of both. It's more convenient for a range of scenarios, but it is only suitable for use with SQL Server 2008. The scope-like pattern gives you more explicit control over which connection you are executing against and can guarantee you won't promote. But it does requires more rigor and writing more code (although that can generally be encapsulated).

This extension will automatically kick in, even in places you did not (or possibly can not) add scope-like pattern support. Apps using the Visual Studio designer-generated data access patterns, apps using the DataAdapter.Fill()/Update() pattern without opening the connection first, and similar scenarios will gain the benefits.

re: Extending Lightweight Transactions in SqlClient

Dennis van der Stelt — Sat, 29 Mar 2008 23:51:53 GMT

AWESOME!!!!!!!!!!

This was also a very good explanation. Thanks. Too bad it wasn't added to SQL2005 as Nikolay says, but it's in .NET 2.0 so it won't be changed easily. If it ain't broke, don't fix it, huh?! ;-)

I've been waiting for this feature by the way. I've already updated the blogpost! :)

http://bloggingabout.net/blogs/dennis/archive/2007/06/28/system-transactions-still-not-working.aspx

SqlClient, System.Transactions, SQL Server 2008, and MARS

Other Blogs we recommend (SSQA.net) — Sun, 06 Apr 2008 02:07:05 GMT

It appears that there are some changes in .NET 3.5 System.Transactions (or System.Transactions.dll and

re: Extending Lightweight Transactions in SqlClient

Josh Berke — Thu, 01 May 2008 02:52:48 GMT

This is a great change in my opinion we went through the hastle of designing a fairly robust TransactionalContext that operated simillarly to both your ConnectionScope and to the System.Transactions TransactionScope. All to avoid promotion.

I've just found your own ConnectionScope implementation, and I have one question / concern with it. I've read a lot about avoiding ThreadStatic variables when working in ASP.Net. Would you recommend your ConnectionScope in such a situation since it stores the Connection object in TLS? And if not are there any other alternatives?

Thanks in advance.

Josh

Big Improvements in System.Data and SQL Server for Lightweight Transactions Support

Florin Lazar — Sat, 03 May 2008 02:07:47 GMT

Great news! The new updates added to System.Data and SQL Server 2008 finally allow multiple Open/Close

Lightweight Transactions in SQL Server 2008

Contagious Curiosity — Sun, 04 May 2008 08:57:35 GMT

Good read which explains updates to SqlClient's System.Transactions in SQL Server 2008 which results

re: Extending Lightweight Transactions in SqlClient

alazela — Fri, 09 May 2008 06:04:37 GMT

Josh, I believe the issue with ThreadStatic fields and ASP.Net is due to lifetime management issues outside the scope of the creating code. The TransactionScope/ConnectionScope model avoids those issues by only using the ThreadStatic for nested call scopes, and removing their owned objects when control returns to the originating level.

re: Extending Lightweight Transactions in SqlClient

Martina — Mon, 18 Aug 2008 08:49:11 GMT

We’ve done some work with SqlClient’s System.

テーブルアダプタと TransactionScope の組み合わせ

とあるコンサルタントのつぶやき — Fri, 23 Jan 2009 12:19:09 GMT

さて、Silverlight 2 や WCF などの最新テクノロジの話ばっかりここまで書いてきたので、たまには地味（けれどもめちゃめちゃ重要）な話をひとつ書いてみたりします。結論を先に書くと、以下の通りです。

re: Extending Lightweight Transactions in SqlClient

JX — Sun, 13 Sep 2009 08:08:31 GMT

Thanks for the good article. But I still have a few questions,

1. "Resetting closes cursors, changes set options back to defaults, switches the database context back to the one from the connection string AND… rolls back any outstanding local transactions"

here, you used "rolls back", but later, you state: "When you close it’s outer connection, this inner connection is set aside pending the rollback or commit request", so I guess the first sentence is not accurate and exact.

2. What will happen to the inner part of a closed connection in a transaction? will it just sleep there waiting for a final commit or rollback? or can already used by other threads? or it depends on whether the transaction is promoted later to a dtc one, if it's promoted, then it is no longer needed by the transaction, can be used by other threads, if not promoted to dtc transaction, then it will stay waiting and be not usalbe for other threads/transactions.

The answer to question 2 will have an effect on coding pattern, if the inner part of a closed connection in a transaction is not needed any more, then closing it at the earlest possible time will release it for others to use, and will be a better coding pattern, if otherwise, then closing it later or after transaction commit or rollback will be a better coding pattern.