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.

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, NOLOCK, sql statements, T-SQL, Transaction Isolation Levels