Extending Lightweight Transactions in SqlClient

We’ve done some work with SqlClient’s System.Transaction support for Sql Server 2008. To explain what we did, and why, I’m going setup some background first, which may also be useful for understanding the existing behavior.

System.Transactions offers a simple, intuitive model for controlling transactions in your .Net code, right? I generally agree with that statement, but there are some devious details, specifically with regard to distributed transactions. They require special setup on the client. They are slow. And probably a few other issues depending on whom you ask.

With version 2.0, SqlClient allowed lightweight transactions when connecting to Sql Server 2005, alleviating the problem for a group of scenarios. But this introduced a new issue – promotion. Promotion is a good thing, as long as you are happy with distributed transactions, but it can be confusing as to when and why it happens.

So why does the transaction promote? A lightweight transaction is an agreement between SqlClient and the transaction, where SqlClient manages transacting the work on the transaction’s behalf.  SqlClient starts a local server transaction when enlisting into the transaction, and the transaction sends a request to rollback or commit the work at the end. When a second resource tries to enlist in the connection, the local server transaction cannot be used with it, so the transaction promotes. Ah, you say, that second request may be going to the same server, and with connection pooling I should be able to open another connection and use the same transaction!

To understand why this doesn’t work, let’s take a look inside connection pooling. A connection consists of two parts: the public instance that your code interacts with (the outer connection) and a hidden connection that represents an actual server connection (the inner connection). When you “Open()” the outer connection, it looks for a free inner connection from the pool that is associated with the transaction (or creates a new one if it cannot find one). When you “Close()” the outer connection, it returns the inner connection to the pool. The server doesn’t distinguish between uses of the inner connection so only one outer connection can be used with any one inner connection at a time, and the inner connection must be reset to prevent state from showing up on subsequent uses. 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. Resetting this way provides a known starting state every time you open a connection, which would lead to nasty random behavior in many cases, but it also prevents re-using the original inner connection with the lightweight transaction until said transaction completes. When you close it’s outer connection, this inner connection is set aside pending the rollback or commit request. So the second time you open any outer connection, including the original, the pool is empty, and a new inner connection is created. A quick example:

You open outer connection “A”. The pool has no free appropriate connection, so inner connection “z” is set up and enlisted in the transaction, establishing a lightweight transaction. You now close “A”, which sets aside “z” to wait for the transaction to end. Next you open outer connection “B” (you could also open “A” again and get the same results). “B” looks for a free inner connection in the pool attached to the transaction, doesn’t find one, creates inner connection “y” and tries to enlist it in the transaction. The transaction, now finding two different resources trying to enlist, must promote (resources in general, and sql connections in particular, cannot share local transactions). Finally you end the transaction, which sends the commit or rollback across “z”, disconnects it from the transaction and returns it to the pool.

So this brings us to the extensions we added for Sql Server 2008 support. On the server, we added a new connection reset mode that does not roll back local transactions. This allows SqlClient to return the inner connection to the pool to be reused. In our example, when you open “B”, it will finds “z” waiting in the pool, associated with the transaction where “A” put it when you closed “A”. “B” appropriates and resets “z” (with the transaction-preserving reset) and happily continues working. Neither System.Transaction nor the server are aware that the application sees “z” as two separate connections. As far as they are concerned, there is only one connection, working on a single local transaction and no promotion is necessary.

There are still limitations with this approach.  If you try to open a second outer connection BEFORE closing the first one, there won’t be a free connection in the pool, so a second inner connection will have to be enlisted (“A” is using “z” so “B” must again obtain “y”). Ditto if you open a connection with pooling turned off or a slightly different connection string, since neither case will find the original inner connection, even if it is sitting idle in the pool.

The new capability is already in SqlClient v2.0 SP1 (e.g. Visual Studio 2008, Orcas, etc), but requires the changes in Sql Server 2008 to work (CTP5 or later). It is used automatically whenever it can be, so you don’t need to make changes to your code if you are already using pooling and enlisting (both default behaviors). When used against Sql Server 2005, the pooling behavior reverts to that of SqlClient v2.0 RTM.

Alazel Acheson
Developer, ADO.NET

Comments

• Anonymous
  March 25, 2008
  PingBack from http://msdnrss.thecoderblogs.com/2008/03/26/extending-lightweight-transactions-in-sqlclient/

• Anonymous
  March 26, 2008
  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?

• Anonymous
  March 26, 2008
  The comment has been removed

• Anonymous
  March 29, 2008
  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

• Anonymous
  April 05, 2008
  It appears that there are some changes in .NET 3.5 System.Transactions (or System.Transactions.dll and

• Anonymous
  April 30, 2008
  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

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

• Anonymous
  May 03, 2008
  Good read which explains updates to SqlClient's System.Transactions in SQL Server 2008 which results

• Anonymous
  May 08, 2008
  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.

• Anonymous
  August 17, 2008
  We’ve done some work with SqlClient’s System.

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

• Anonymous
  September 12, 2009
  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.