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