Why not NOLOCK?

Kenneth Fisher, 2015-03-18

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)

 

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads