Friday, April 10, 2009

How Dirty are your Reads?

by Andrew Kelly via Andrew Kelly on 4/10/2009 11:08:11 PM

As a SQL Server consultant I get to see a lot of different sets of code written by all sorts of database developers from all over the world. And I have to say that one thing that is clear is most developers make abundant use of the NOLOCK query hint or the READ UNCOMMITTED isolation level. Now don’t get me wrong, I am NOT trying to say this is bad and people should not do that. There is a place and time for all the features and hints SQL Server has to offer and these can be quite handy indeed. The question is “Are these methods overused or even abused” by the average shop these days? I had a recent conversation with someone close to the SQL Server dev team who stated that some of the members of the dev team would be surprised to hear that.  Personally I am surprised to hear that they would be surprised to hear that :).  So I ask the rest of the known universe out there what you think. Are dirty reads abused more than they should be? Technically if they weren’t doing anything wrong at all it wouldn’t be abuse now would it?  But I think you get the point :).   

But before you answer that question let me talk a little about what the dangers of dirty reads using either NOLOCK hints or setting the isolation level to READ UNCOMMITTED are.  None of this information is new and lots of people have already written about the pitfalls many times. Itzik Ben-Gan for one, publically speaks on this subject often.  But I find developers on a regular basis that fail to realize all of the implications. Lets touch on the more common ones here:

Dirty Reads: 

This sounds like an obvious one but many people simply don’t understand that using NOLOCK as a hint doesn’t just prevent you from blocking other users, it allows you to potentially read dirty data. You never know when or if you have read dirty data so unless the data is read-only or static so you may be making decisions based on incorrect values. After all this is the main purpose of locking in the first place right.

Read Errors:

What happens if you are in the process of reading a row or page that someone deletes or reading a page that is in the process of splitting?  You can and most likely will get an error. This is a fatal error and most applications are not set to properly handle these types of errors.

Reading Rows Twice:

If you are in the process of doing an IAM scan and read a page that gets split after you just read it you can actually read up to half the rows from that page again since the split page will go to the end of the IAM chain.

Missing Rows:

This is the opposite of the scenario above. You can actually miss rows if a page is moved back in the IAM chain before you read it.

So the bottom line is that if your application depends on accuracy and data integrity then you probably shouldn’t be using this isolation level for that part of the applications queries. Yes as I mentioned earlier there are certainly good uses for this and it can speed up certain queries and reduce the number of locks generated. But as with any technology there are always two sides to consider as nothing is for free.  Are you writing an app that is using NOLOCK and calculating my checking account balance?  I certainly hope not. Unless you want to credit me with some extra cash in the process :).  But if your app doesn’t take into account all of the things we just discussed I say you are abusing it. If it costs you accuracy / integrity or stability when it wasn’t intended then yes you must seek help as you are an abuser :).  So what do you think, is the world full of abusers?