Yesterday I found what can easily be named THE bug of the year. Even I can named it the biggest bug in the last 4 years. It affects nearly all of our past projects with different degrees of impact.
It is documented in MSDN as a feature in a small note in the description of the SqlConnection.BeginTransaction .NET method (see references below).
Let me explain the context first so you really understand the bug and how to fix it.
Imagine you have a web application created using ASP.NET, and a SQL Server data storage.
To construct a web page, your ASP.NET code will query 3 times data on the SQL server.
The 1st and 2nd queries are executed inside a transaction which runs under a non default isolation level READ UNCOMMITTED.
The last query is executed alone, completely separated from the previous ones. This query is using the with(READPAST) option in one of its statement.
If you run your application, you'll see a nice .NET error page telling you that READPAST can not be used in this transaction isolation level (it does not tell you which).
So first you debug your application and you check in which isolation level you are. This is easily done using this query:
select
transaction_isolation_level,
last_request_start_time,
*
from sys.dm_exec_sessions
And you'll find what ? That instead of being the default READ COMMITED isolation level, the query is executed under READ UNCOMMITTED !
Have you guessed why already ? Well it is because of the "feature" of the transaction handling combined with the feature of the SQL connections pooling implemented in ADO.NET.
Connection pooling makes .NET reuse existing SQL connections which is an efficient way to use this network resource.
In our case, a connection is created by the first query and is used by the whole transaction. When closed by code, the connection is not closed but moved to the connections pool (with a TTL so it won't last forever if not used).
For the third query the code creates a new connection, which the library converts to a 'get the opened connection from my connections pool'.
Now read the note from MSDN :
After a transaction is committed or rolled back, the isolation level of
the transaction persists for all subsequent commands that are in
autocommit mode (the SQL Server default). This can produce unexpected
results, such as an isolation level of REPEATABLE READ persisting and
locking other users out of a row. To reset the isolation level to the
default (READ COMMITTED), execute the Transact-SQL SET TRANSACTION
ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection..::.BeginTransaction followed immediately by SqlTransaction..::.Commit.
For more information on SQL Server isolation levels, see "Isolation
Levels in the Database Engine" in SQL Server 2005 Books Online.
What happens here is that the first connection is returned to the pool but the transaction isolation level is not reset to its default value. So our 3rd query will execute and fail unexpectedly because the with(READPAST) option is not compatible with an isolation level equals to read uncommited.
How to fix this ?
Before closing your connection, after a transaction where you know you change the default isolation level, just execute this code wich resets it to the default READ COMMITED or wathever you set to be the default:
myConnection.BeginTransaction().Commit();
References:
SqlConnection.BeginTransaction .NET method and the note explaining the "feature".
.NET SQL connection pooling (ADO.NET 2.0)