Bad habits : Putting NOLOCK everywhere

[See an index of all bad habits / best practices posts]

I went through the archive above, and was quite surprised to discover that I have never blogged explicitly about NOLOCK. Out on the forums, I typically refer to this hint as a "magic, pixie-dust turbo button." It may seem to make some queries faster, but at a significant cost that must be weighed. I'm not saying don't ever use the read uncommitted isolation level for any query; the "habit" I am talking about here is not that of using NOLOCK in an isolated scenario where the effects are known and deemed inconsequential, but rather, that of slapping NOLOCK on every table mentioned in every query in the entire workload.

 

NOLOCK Effects

What are the effects you need to worry about? Well, others, like Andrew Kelly, have described this before, Paul White digs quite deep into it, these search results are quite telling, and Kendra Little even has a video about it... but I'll point them out anyway:

1. "Dirty read" - this is the one most people are aware of; you can read data that has not been committed, and could be rolled back some time after you've read it - meaning you've read data that never technically existed.
    
2. Missing rows - because of the way an allocation scan works, other transactions could move data you haven't read yet to an earlier location in the chain that you've already read, or add a new page behind the scan, meaning you won't see it at all.
    
3. Reading rows twice - similarly, data that you've already read could be moved to a later location in the chain, meaning you will read it twice.
    
4. Reading multiple versions of the same row - when using READ UNCOMMITTED, you can get a version of a row that never existed; for example, where you see some columns that have been changed by concurrent users, but you don't see their changes reflected in all columns. This can even happen within a single column (see a great example from Paul White).
    
5. Index corruption - surely you are not using NOLOCK in INSERT/UPDATE/DELETE statements, but if you are, you should be aware that this syntax is deprecated and that it can cause corruption, even in SQL Server 2014 RTM - see this tip for more information. Note that you should check for the hint in any views that you are trying to update, too.
    
6. Read error - because the underlying data could be moved or deleted during your read, you could see this error:  

   Msg 601, Level 12, State 1
   Could not continue scan with NOLOCK due to data movement.

      

   Back in SQL Server 2000, this bug was supposedly fixed (see KB #815008), but only under a trace flag (9134) - and several customers have reported that it can still happen, even with the flag, and even in SQL Server 2014.

Maybe these effects are okay to you. Maybe you're using NOLOCK in scenarios where 100% accuracy isn't crucial (a rough ballpark of the number of messages posted to your forum today, a monitoring tool collecting aggregate metrics from DMVs) or where it can't really hurt (pulling data from a static auxiliary table like a numbers or calendar table). But maybe you aren't aware of the potential issues listed above, or don't believe they can happen to you. Let me assure you: they can. Not convinced? There's more! Please read on.

 

还有其他内容