September 24, 2009 by Mike Hillwig

A few years ago, I was working on a big project doing some report writing. This particular client had a rule that every report have an underlying stored procedure. That was one of those things that sounds great in theory, but the practical applications are less impressive.

Most of the reports were scheduled to be run at 4:00 AM, and occasionally, the reports would fight with each other, causing the occasional deadlock. Someone found a reference to WITH (NOLOCK) in the books online. Before too long, this locking hint became pretty standard in the FROM clause of most stored procedures. Since the reports were being run when the system had no updates going on, this was perfectly acceptable.

And then…

Someone wrote a process that imported data from a file and then updated a table with that data. This particular individual was pretty good with Crystal, but she wasn’t quite so versed in SQL. Because she’d been writing stored procedures to use WITH (NOLOCK) on every other SQL statement, she used it in an update statement as well.

DBAs are known to drop the F-bomb once in a while, but what I heard from this particular DBA that day was legendary. He was not impressed. The statement looked something like this:

UPDATE tablename
SET column = value
FROM tablename WITH (NOLOCK)

Fortunately, this particular DBA was on top of his game and shot it down before it got approved by the business to be put into production.

Locking hints can be a very powerful tool. They can speed up queries by bypassing locks. But when used improperly, they can cause really bad results.

One thought on “(nolock)

  1. I always thought that (NOLOCK) hints in update statements were ignored by the processor. Is that true? I suppose one way to find out for sure would be to trace all locks taken by the statement with and without the hint to see the difference.

    It’s interesting that the official docs say that (NOLOCK) in an update statement is deprecated: http://technet.microsoft.com/en-us/library/ms187373.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *