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:
    

    
    

   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.

Poor Naming Choice

It really is too bad that they named this hint NOLOCK. The semantics of read uncommitted don't actually mean "take zero locks" - they actually mean "take no shared locks." I have seen many people surprised to see rows in sys.dm_tran_locks for a query that is running under read uncommitted. Here is a quick example, where we can see Sch-S (schema stability) and other locks taken out for an object, even though NOLOCK is used (click to enlarge):

Sch-S locks showing even under read uncommitted

These aren't held for the duration of the transaction, but the locks are maintained while the query is running. What does this mean? Well, if you have lots of users running read-only queries under NOLOCK, you may still have issues "cutting in line" to make any changes to the table or its indexes, because you will be blocked by Sch-S. It is a good thing to be conscious of this issue - "NOLOCK" in this case isn't exactly working as advertised.

There are other scenarios, too. The query writer doesn't always have control over whether the objects they are referencing will obey the current session's isolation level semantics. I gave an example recently regarding the metadata helper functions like OBJECT_NAME() - a NOLOCK query can still be blocked because several of these functions enforce their own isolation level. You can also hit this if you reference your own functions or views with stronger hints, or only apply the hint to some of the tables involved in the query. To avoid the latter, and to make a later change to a better isolation level easier (more on this below), I always recommend using the session-level hint instead of the table-level WITH (NOLOCK):