Why not NOLOCK?
10March 18, 2015 by Kenneth Fisher
Most senior DBAs I’ve met shudder when they hear NOLOCK. Ever wonder why? For the same reason they shudder at shrink, MAXDOP of 1 and even occasionally at UDFs (user defined functions). Because frequently we see cargo cults develop around these technologies. Cases where a group of IT professionals (developers and/or DBAs) have decided that NOLOCK (or whatever) made something go faster/work better and now use it everywhere without really understanding why it worked in the first place.
Recently I posted Tales of a DBA fed up with NOLOCK and I got a fair number of comments (at least for me). And don’t get me wrong, I love comments, and I appreciated every single one. What they did was make me realize that I want to clarify my stance on NOLOCK.
I wrote my posts in part as practice for the various technologies I used (ddl triggers and policy based management) and in part as catharsis for an obnoxious situation. I wouldn’t put either in production for exactly the opposite reason as the Cargo Cults. NOLOCK has it’s uses and shouldn’t be completely restricted, just like it shouldn’t be used everywhere.
It’s a useful setting, under the right circumstances.
So what is NOLOCK?
A number of DBAs and developers don’t realize that NOLOCK is basically an alias. It is a query hint that causes SQL to use the READ UNCOMMITTED isolation level for the table (or query) specified. Which begs the question? What is the READ UNCOMMITTED isolation level? Read uncommitted isolation level allows for dirty reads. And what does that mean? (Every notice how trying to define a word tends to lead to trying to define the words you used and then you have to define those words and so on.) So a dirty read is where SQL Server will allow a page to be read even if there is an exclusive lock on it. And vise-versa, it doesn’t take shared locks that block updates.
Note: Because NOLOCK and READ UNCOMMITTED are the same thing putting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of a piece of code and putting NOLOCK on every table reference in that piece of code is redundant. And repetitive. And annoys the &*#$ out of me.
So now that we know what NOLOCK is what are some common misconceptions?
Common misunderstandings about NOLOCK
NOLOCK doesn’t take locks.
READ UNCOMMITTED/NOLOCK (I’m just going to say NOLOCK from here on.) does take locks. This is the biggest misunderstanding I see. If nothing else a shared schema lock will be taken on the table. So some blocking will occur (schema changes, truncates etc.)
NOLOCK makes queries go faster.
No again. It looks like a query goes faster because there is reduced blocking. If you run that query on a server with no other users it’s going to run just as quickly whether or not you include NOLOCK. It can aid with concurrency issues but nothing else. So no magic fast button, sorry.
The only bad data I’ll see is what hasn’t been committed yet.
Another huge misunderstanding. Aaron Bertrand (b/t) did a great summary in his post Bad Habits Nolock Everywhere.
In it he mentioned not only the possibility of seeing data that hasn’t been committed, but seeing duplicate rows, or missing existing rows. (He also mentions index corruption and read errors although those don’t really fit under the heading of bad data.)
Additional reading on the problems of NOLOCK (to name a very very few)
- Aaron Bertrand (b/t) – Should I use nolock on #temp tables?
- SQL Server Customer Advisory Team – Previously committed rows might be missed if NOLOCK hint is used
- Jason Strate (b/t) – The side effect of nolock.
- Andrew Kelley (b) – How dirty are your reads.
- Kendra Little (b/t) – There something about NOLOCK webcast video
So what’s NOLOCK good for?
Earlier I said that NOLOCK has it’s uses but all I’ve discussed so far is the problems. In fact Kalen Delaney (b/t) said they should have called NOOCK I_DONT_CARE_ABOUT_MY_DATA_JUST_GIVE_ME_ANYTHING. So what’s it good for? Personally I use NOLOCK if I need to pull an approximate number of rows inserted so far during a bulk insert. I don’t need information that’s exactly correct and I don’t want to interfere with the load. That’s really the key. NOLOCK is great, if and ONLY if, you don’t need exact information and don’t want to interfere with what else is going on.
Summary
So NOLOCK is useful, but not all the time. It has some very significant issues and should not .. I repeat should NOT be used all the time. But that doesn’t mean it’s evil either. It has it’s uses and can be very handy at times. I would categorize NOLOCK as one of those Sr level tools. It’s a tool that you really need to understand before you start making use of it.
SQL Studies 
re: putting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of a piece of code and putting NOLOCK on every table reference in that piece of code is redundant. And repetitive. And annoys the &*#$ out of me.
You may want to reconsider that position. My application has one use case where “I don’t care about my data just give me anything…for now” is critical. We put both SET TRAN ISO LEVEL RU *and* NOLOCK in that single given procedure. It’s a belt and suspenders thing. When we just used RU at the top of the proc then people would start adding JOINs to important tables that couldn’t tolerate the “give me anything…now” paradigm. They basically “forgot” that proc’s purpose. Conversely, when we put NOLOCK everywhere invariably some idiot would add a JOIN to a table that could tolerate the “give me anything…now” paradigm…but they’d forget the NOLOCK. Then we tried huge flowerbox comments, but no one reads those.
So we opted for belt and suspenders. But again, that was a *very* specific use case. Yeah, I know, it just adds ugly to ugly, but it works.
All right. I’ll give you that. And part of the point of the whole post is that there are use cases for almost anything. I’ve found that in some cases it may look bad, and annoy the crud out of me, but still be the best way to go about it. I stand by my “redundant and repetitive …” statement, but allow that in your case the human factor makes it necessary.
A follow up question to the “NOLOCK doesn’t take locks” idea. It is clear that NOLOCK still takes a schema lock. However, if, as in the case of a reporting databse, after the nightly ETL completes there will be 0 (zero) updates/inserts, and thus 0 (zero) chance of dirty reads, is there a benefit of saved server resources or processing produced by not having to take the other locks? If so, what kind of situation (server, database, table/dataset) would need to exist where freeing those resources using NOLOCK would be noticable?
At a certain point (and I’m not sure what that is off the top of my head) SQL will switch from row/page locks to table locks anyway so I guess if you had a lot of concurrent reads being done that came close but didn’t hit a sufficient number of locks to cause lock escalation then you might save a few resources (but probably not much) by using NOLOCK. Of course also remember in the situation you are talking about that you are only taking shared locks (selects) and no exclusive locks so you won’t resolve any blocking issues by moving to NOLOCK.
[…] take or honor certain types of locks, making it much less likely to be blocked (or deadlocked). This does not mean you should use it to avoid blocking! For all of these examples I’m going to assume the default transaction isolation level READ […]
[…] is just part of life I’m afraid. Because we have locks (and yes we have to have them, and no, NOLOCK doesn’t avoid them) we will have blocking. Typically it’s going to be very brief and you won’t even notice […]
[…] I told you not to use NOLOCK even though I use it sometimes. […]
[…] level of the session and/or you can change the isolation level for the specific view. So if you are using NOLOCK then the whole query will actually use […]
When a dynamic query is built in an SP, the ISOLATION LEVEL of the SP is not used for the execution of the child process.
This is part of the reason that I despise using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED… it’s a one-size-fits-all solution that does not even pretend to consider the needs of the individual queries being performed within the SP.
Stick with the (nolock) hints on the individual tables within the individual queries so that you are actively choosing to accept dirty reads where you know that they are safe.
Good point. Not that I would agree that NOLOCK should be in many (if any) production queries.