TransactionScope and Serializable

When using transaction scopes and SQL connections I recently tripped over a undesirable effect. By default when creating a new transaction scope the connection is raised to serializable. I can see the logic in this, as it provides the safest starting point and allows the developer to change the behaviour after careful consideration.

A quick explanation of serializable isolation level : Transactions occur isolated and sequential. However this is just an illusion and other transactions maybe running alongside but only if the database can maintain the fa├žade of running in an isolated manner.

I first noticed the issue when monitoring the connection pool, there were many connections that had an isolation level above what I expected, it turns out that once the isolation level was raised by enlisting in the transaction scope the isolation was not being set back to read committed.

To work around this you need to Execute “SET TRANSACTION ISOLATION LEVEL” on the connection before releasing, or turn off connection pooling (Which I wouldn’t recommend without a very good reason, establishing a database connection is an expensive operation).

Advertisements