TransactionScope and Connection Pooling in SQL Server

There is one very interesting behavior of connection pooling feature in .NET framework / SQL Server that I learned yesterday.  You can read the Connect issue here.  Interestingly enough, default setting for TransactionScope object is to use Serializable Isolation Level.

Here is a typical scenario to put the problem in perspective.

  • An application is using connection pooling and SQL Server.  Connection pooling of course greatly improves performance of connecting to SQL Server and is enabled by default in SQL Connection.
  • An application is using TransactionScope to handle transactions.
  • You fired an UPDATE operation via TransactionScope
  • Another user is firing a SELECT statement.  At that time a connection is retrieved from connection pool and happens to be the same connection that the UPDATE statement used.
  • Result – your SELECT statement is executed with Serializable isolation level, acquiring exclusive locks.  So the data you just read is not accessible to any other user until your transaction (that you do not even need) completes, likely when you are done reading your data and close the connection.

In application with a few users this is not really an issue.  Anyone who is designing an application for high availability must account for this behavior however.

2 Comments

    • You can alter your transaction opening code to always reset transaction isolation level. Or you can wrap your query code inside explicit transaction scope that set isolation level to read committed.

Leave a Reply

Your email address will not be published. Required fields are marked *