Did you know that ADO.Net leaks isolation levels across pooled connections?

This is particularly bad if you’re trying to use Snapshot isolation in SQL Server 2005+.

So, let’s say you create an ADO.NET connection (say, from Entity Framework) and do an update… you’re fierce about your data integrity, so you set your isolation level to Serializable… or you use TransactionScope and don’t bother to specify an isolation level and you get the default of Serializable (ye gods!)

When you’re done, that connection goes back into pool.

Now you open a new connection to and try to read data from a snapshot database with a simple select statement.  That connection you just got came off the pool, but with Serializable isolation level still set!

Now, rather than the beautiful clean, non-blocking read you were hoping for, you get a blocked read… exactly the thing you hoped  snapshot isolation would prevent!

The problem, essentially, is that sp_resetconnection does not reset the isolation level.  You need to do this manually, or use different connection strings for updates/reads so you don’t grab the same connections off the pool.

More detail here:

http://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections

UPDATE (2/10/2017):

This appears to have been fixed in SQL 2014:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/916b3d8a-c464-4ad5-8901-6f845a2a3447/sql-server-2014-reseting-isolation-level?forum=sqldatabaseengine&forum=sqldatabaseengine