27 Sep

Brett W. Green's On the Contrary

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…

View original post 47 more words

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

%d bloggers like this: