THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

How Dirty are your Reads?

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?

Published Friday, April 10, 2009 8:08 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bernd Eckenfels said:

What Error Codes do you typically see in the read-error case and can they also happen in other scenarios?

Bernd

April 10, 2009 10:30 PM
 

Linchi Shea said:

> 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.

Unfortunately, that is not the bottom line in reality. I find a lot of people discussing the pros and cons of NOLOCK completely miss the point because they discuss it from the perspective of a bystander. I can, and should, warn people of the danger of NOLOCK if I'm just doing a seminar or presentation because I really don't have to make a hard choice real choice.

In reality (at least the reality I have experience with), the choice of NOLOCK is inevitably made because people are between a rock and a hard place. Yes, NOLOCK may not give inaccurate results in some circumstances. But that potential problem sounds academic when not using it means you may block a business critical process. And if that does happen all hell will break loose because the only bottom line that matters is the bottom line on the balance sheet.

One can argue that perhaps deveopers should code better. But no matter how well you code with READ COMMITTED, because of the dynamic nature of T-SQL and no assurance of the stability of the SQL Server query plans, you simply can't guarantee that your well-written code won't block that critical business process that is also accessing the same set of tables. Unless you want to be fired because one day for whatever reason your code causes the crtical business code to halt, NOLOCK is a safe choice. Between that kind of choices, no one will blame you for sometimes getting inaccurate results as long as these results are less critical than that critical business process. Moreover, in a specific application, the chance of getting inaccurate results (that sound so ominous in classrooms) is either understood/accepted or rarely happen because of the nature of the processes.

So I would say there is very little abuse of NOLOCK. Rather, it is a behavior driven by the SQL Server locking model.

In a way, this parallels the discussions prior to SQL Server row level locking. Before SQL Server introduces row level locking, Oracle used to dismiss SQL Server as not fit for high concurrent applications, and Microsoft would counter that if applications are properly written, page level locking more than good enough. The reality was, and still is, that applications may not be properly written (many bigh packages are actually pretty bad written), and even if they are properly written, page level locking can still get you in deep shit. You can argue for the virtue of page level locking all you want, but you have to face your angery management when your order processing system stops processing orders from time to time or your trading systems stop processing trades for a minute or two here and there.

April 10, 2009 11:35 PM
 

Mike Walsh said:

It has often been my experience that NOLOCK hints or READ UNCOMMITTED are used far more often than is needed. Some shops just use it as a default, "For Performance" without thought of the implications. Even then when the implications are discussed it is normally the Dirty Read implication. I have to honestly say when helping battle this (through trying to understand if the hints were truly needed, what the issue is, why the blocking is occurring, can improvements in performance and transaction management shorten the footprint of a select that could block a writer, etc) I normally am arguing the Dirty Read argument.

I normally setup a demo in a powerpoint when discussing best practices to show this. Perhaps I should play more with the other three problems you mentioned, Andy.

I also agree with Linchi a bit here. Abuse is a strong word. There are plenty of cases where a shop will use them everywhere once they learn about them but they still do prove necessary at times.

Nice to see you posting again, thought you were stuck on a golf course down there for the past few months ;-)

April 11, 2009 8:44 AM
 

Linchi Shea said:

> Nice to see you posting again, thought you were stuck on a golf course down there for the past few months ;-)

Actaully I thought he was too depressed from missing all that New England snow this past winter to do anything. Now that the snow season is over (or almost over), I hope he has overcome the depression :-)

April 11, 2009 9:02 AM
 

Andrew Kelly said:

Linchi,

In your example the choice was made after weighing the options and coming to the conclusion that the risks were small enough with a dirty read that the alternative cost was too high for the business. That is hopefully a well thought out decision and not the kind of situation I was trying to describe. I was more concerned with shops that simply use NOLOCK as the default and don't weigh the costs at all.  

>>>Moreover, in a specific application, the chance of getting inaccurate results  is either understood/accepted or rarely happen because of the nature of the processes.<<<

Well maybe in the ones you work with but I can tell you it happens all the time in the rest of the world. I can remember 2 years ago while I was doing my duty at the MS booth at Teched I had close to a dozen inquiries from attendees asking why they were getting inconsistant results or these strange errors all relating to NOLOCK. Not to mention all the times I see apps on a regular basis that certainly have the potential for these as well.

One other comment I want to make regardign your post was that more people should consider one of the Snapshot Isolation levels if locking is an issue but integrity is also a factor. While Snapshot has it's own set of rules and you must realize you may be dealing with old data it is an option for a lot of apps yet never considered over NOLOCK. Maybe thats another blog entry:).

April 11, 2009 9:17 AM
 

Andrew Kelly said:

I did play golf twice this week as a matter of fact.  But since I have been down here I have only played 5 or 6 times in total including those 2 rounds. But I am enjoying the nice weather and lack of snow. Gee I miss driving in the snow and ice, NOT...

April 11, 2009 9:20 AM
 

Andrew Kelly said:

Bernd,

The most common one is listed below and is only caused due to the lack of locks being taken by the reader when data is changed.

Server: Msg 601, Level 12, State 3, Line 14

Could not continue scan with NOLOCK due to data movement.

April 11, 2009 9:29 AM
 

AGiotti "Fred" said:

HI Andrew,

Glad to see you online again, I was wondering if anything was wrong. Where I am at, PA., it has not warmed up yet, I envy you living down there.

In my shop, I review a lot of the developers code (offshore), that is all they use, the NOLOCK hint. When I ask why they use this hint its always the same response, "we don't want our code being blocked", that is right!, "our code being blocked". They don't understand, that using the hint, Shared locks are not issued and it does not honor exclusive locks. When I explain that they are reading dirty data the response is, "Really?"

So to summarize our shop is abusing the NOLOCK hint.

Take care and thanks for this post.

April 11, 2009 4:28 PM
 

Ray Ayyelos said:

I just wanted to add my two cents.  I'm sad that it seems that people only ever care about the bottom line and, in my own personal experience, only ever care about coding to that end.  In many many many solutions I've seen, all billing and accounting centric, the de-facto default thing to include in development code is WITH NOLOCK.  If it is forgotten and lock escalation occurs and we see "issues" then it is always the first question, "who didn't use NOLOCK."  Then, when developers come to me to find out how to better the situation, I tell them well, let me review the code and make suggestions.  I review the code and make suggestions. Then, the inevitable answer comes: "oh, we don't have time to make these changes and test them - we'll just NOLOCK."  Its a culture thing, developers are motivated by deadlines (in my experience), and don't care too much to press the case for developing -better- code.  So, I've often donned the mantle and had discussions with decision makers and made it something they have to sign off on -  potentially dirty financial data, or take a little more time to fix the problem.  I've won in many cases and become really good friends with all of the developers I've had a chance to work with.

April 14, 2009 5:31 PM
 

Paul White said:

NOLOCK really should have been named WITH (QUICK_AND_DIRTY) or WITH (REDUCED_ACCURACY).  NOLOCK just sounds like a great option to many people (locks == bad!)

That said, I work for a popular auction site and pretty much every select has NOLOCK, and every modification statement has ROWLOCK.  Concurrency is king - very few transactions roll back, so the argument is that the data will be committed shortly anyway (!) and if there is a discrepancy, it tends to be very short-lived and difficult for anyone to 'prove' :c)

Shocking, in a way - but that's the reality of the business.  BTW financial stuff is rather more 'transactiony and locky', for anyone worried.

I hope to convince the decision makers to gradually trial read committed snapshot instead; but there are an awful lot of NOLOCK hints to remove :c)

Cheers

Paul

April 16, 2009 6:29 AM
 

Tom said:

Are NOLOCK hints overused? They certainly are at the company I just started at (who will remain unnamed). I have watched developers write queries against development (non-production) databases and use NOLOCK an every single table without even thinking about it. In fact, NOLOCK is so ingrained in this companies "development culture", it is very common to see NOLOCK against locally scoped temp tables even though it is impossible for any other process to acquire locks on that table. This is something that needs to change.

April 18, 2009 12:09 PM
 

Chris Sherlock said:

Andrew, in relation to your comment above:

>>>> One other comment I want to make regardign your post was that more people should consider one of the Snapshot Isolation levels if locking is an issue but integrity is also a factor. While Snapshot has it's own set of rules and you must realize you may be dealing with old data it is an option for a lot of apps yet never considered over NOLOCK. Maybe thats another blog entry:).

Snapshot Isolation sounds great in theory, but the reason I don't like it is that I work for a company that sells software to many companies who run multiple databases on a single instance. Given that snapshot isolation uses the TempDB, and the TempDB is a single point of contention amongst all databases on the instance, I see that it potentially can cause bigger problems than it solves.

Of course, this leaves me asking why Microsoft decided that the TempDB should be shared between databases... does not seem sensible in a database product that is meant to be scalable.

April 19, 2009 12:42 AM
 

Andrew Kelly said:

Chris,

>>>Given that snapshot isolation uses the TempDB, and the TempDB is a single point of contention amongst all databases on the instance, I see that it potentially can cause bigger problems than it solves. <<<

While TempDB is certainly used by all the databases on one instance it does not have to be a point of contention at all if the best practices are followed. It is used by thousands of instances around the world with multiple databases without any such issues. In my experience most issues with TempDB are due to lack of best practices such as a single data file or having the data & log files on an improperly sized array. If you use tempdb that much separating it altogether is usually a good move. So while I am not saying there is not overhead associated with snapshot because their certainly is, nothing is for free. But again with proper resources and planning most people can use it effectively without any trouble.

April 19, 2009 8:55 AM
 

Barry Sirote said:

Andrew,

I think this blog post is a little misleading.  You don't say it outright, but most will likely assume that the intent is to remove  NOLOCK - which will cause the query run under the default isolation level of READ COMMITTED (although you do mention the possibility of using snapshot isolation in a comment).

The issue is that you CAN have similar inaccurate results (duplicate rows or missing rows) while using READ COMMITTED as well!

While READ COMMITTED does prevent reading uncommitted data - it will still allow for inaccurate results in queries.  

So the suggestion to just remove NOLOCK to fix these issues is not fully valid.  In fact, the post should have been the dangers of running queries using NOLOCK or READ COMMITTED.  NOLOCK should not have been singled out.  

------------------------------------------------------------------

I will reference Itzik and Tony who explain/prove the issues with READ COMMITTED:

Itzik: http://www.sqlmag.com/Articles/ArticleID/97760/97760.html

Tony: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx

April 19, 2009 10:05 AM
 

Barry Sirote said:

Ah, I see Alexander Kuznetsov made a similar blog post about this as well.  Sorry, for the redundancy then, still catching up from the Passover Holiday and did not read all the blog posts yet.

(I do think that your focus on NOLOCK instead of NOLOCK & READ UNCOMMITTED is a bit misguiding.)

April 19, 2009 10:14 AM
 

Andrew Kelly said:

Barry,

>>>You don't say it outright, but most will likely assume that the intent is to remove  NOLOCK<<<<

No that was not the intent at all. The intent was to get people to think about when they should use NOLOCK and what the consaquences may be before using it and not to just use it by "Default" or general practice. I mentioned several times where there are good and valid uses for NOLOCK.  When you change from one isolation level to another it is very important to understand fully why you are doing it and what you will gain and what you will lose. No hidden messages here this time :)

April 19, 2009 7:46 PM
 

Denis the Thief said:

I think No Lock is over used, abused and misunderstood.

But in many ways it is like Denormalization, it is not ideal. But sometimes when there is a problem, our solution is an improvment, even though it is not ideal.

July 30, 2009 11:44 AM
 

Merrill Aldrich said:

I have just given a talk with my group at work on the basics of transaction isolation, dirty reads (nolock)

July 30, 2009 7:33 PM
 

Ralph Wilson said:

I have far too often encountered the unquestioned useof NOLOCK hints.  Sometimes it is because the local "hot-shot" developer has successfully gotten past some sticking point with it and then assumed that it was "the thing to do" and then has spread the word to the rest of the team.  Sometimes it is the result of a simle, appropriate use of the NOLOCK hint becoming generalized as a solution by either a team lead or a first line manager.

Not too long ago, I got in a somewhat heated argument over the use of NOLOCK vs a covering index.  I had suggested a covering index as the solution to a dead-lock issue but the Vendor's developer had recommended the use of NOLOCK.  Of course, because the Vendor's developer was, after all, a _Vendor's_ developer, the NOLOCK was used.  Only after that still didn't solve the problem did they let me create the covering index . . . which both solved the problem and decreased the overall response time. ;-)

July 31, 2009 3:00 PM
 

Naga said:

I would have to agree with "Linchi Shea". It is really mean to call it abusing what ever perspective author has.

September 22, 2010 11:38 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement