Monday, September 18, 2006

Lightweight Promotable Transactions and Connection Pooling

We're moving an existing system from a SQL 2000 backend to SQL 2005. The system uses a custom persistence framework and System.Transactions.TransactionScope for all transactions. The framework was developed with the best practices for connection pooling in mind. Basically, connections are cheap; open them when you need them and close them when you're done. This results in a connection for each type being fetched during a request. For example, a Customer with an Address would require 2 connections, one to fetch the Customer and one to fetch the dependent Address.

When SQL 2005 enters the picture, however, we have a problem. TransactionScope and SQL 2005 are smart enough to realize that not all transactions are distributed, so they start out as local ADO transactions and are supposed to be seamlessly promoted when necessary. There are 2 problems we've run into so far. The first is that it's way too easy to trigger a promotion. Although I guess it makes sense, opening a second connection to the same data source always triggers a promotion. The second problem arises if the first connection is busy (say, reading a Customer object from a DataReader) when the second connection is open. This causes an immediate failure since the first connection is blocked, preventing a promotion.

Although you can work around the blocked connection problem with a new SQL 2005 feature, Multiple Active Result Sets (MARS), it still seems a bit gross. I may end up rewriting bits of the persistence framework to properly share a single connection, queuing the objects to be loaded and then working through them rather than having multiple connections with multiple DataReaders going at once.

No comments: