ADO.Net Leaks Isolation levels across pooled connections

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s