Adventures with NOLOCK

Some of the beauty of being a database professional is the opportunity to deal with our friend NOLOCK.  For one reason or another this query directive (yes I am calling it a directive* and not a hint) is loved and idolized by vendors, applications, developers, and upper management alike.  The reasons for this vary from one place to the next, as I have found, but it always seems to boil down to the perception that it runs faster.

And yes, queries do sometimes run faster with this directive.  That is until they are found to be the head blocker or that they don’t run any faster because you can write good TSQL.  But we are not going to dive into those issues at this time.

A gem that I recently encountered with NOLOCK was rather welcome.  Not because of the inherent behavior or anomalies that can occur through the use of NOLOCK, but rather because of the discovery made while evaluating an execution plan.  Working with Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) , I came across something that I would rather see more consistently.  Let’s take a look at this example execution plan:



First is a look at the plan to see if you can see what I saw.

Read Uncommitted


And now, we can see it clear as day.  In this particular case, SQL Server decided to remind us that the use of this directive allows uncommitted reads to occur so it throws that directive into the query text of the execution plan as well.  This is awesome!  In short, it is a visual reminder that the use of the NOLOCK directive, while it may be fast at times, is a direct route to potentially bad results by telling the database engine to also read uncommitted data.

How cool is that?  Sadly, I could only reproduce it on this one version of SQL Server so far.  If you can reproduce that type of behavior, please share by posting to the comments which version and what you did.  For me, database settings and server settings had no effect on this behavior.  No trace flags were in use, so no effect there either.  One thing of note, in my testing, this did not happen when querying against a table direct but did only occur when querying against a view (complexity and settings produced no difference in effect for me).

* I would like to make it a goal for every database professional to call this a DIRECTIVE instead of a hint.  A hint implies that you may have a choice about the use of the option specified.  And while NOLOCK does not entirely eliminate locks in the queries, it is an ORDER to the optimizer to use it.  Therefor it is more of a directive than a mere suggestion.

4 thoughts on “Adventures with NOLOCK”

  1. Using Microsoft SQL Server 2012 (SP1) – 11.0.3153.0 (X64)
    I could effectively reproduce this with a table as long as there’s a WHERE clause in the query.

  2. NOLOCK to me has always been about dirty reads, and as side affect you could avoid some locking conflicts and get some performance improvement.

  3. I’d never think that the fact that nolock causes dirty reads is a surprise for anyone who use even semiconsciously. ONE line description from MSDN: “NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED”. How else it supposed to work? I’d expect some more interesting things like page splits from the article.

    BTW, the same hint vs directive logic applicable to the rest of locking directives (xlock, updlock).

    1. I think you missed the point. The article was a quick demonstration to remind people that the NOLOCK directive is READUNCOMMITTED and that you can even see that fact in the execution plans. Seeing that in the execution plans is not always something that shows up.

      And yes, people are still quite unaware that nolock causes dirty reads or that it can read uncommitted data. Indicative of this, is the fact that the NOLOCK directive runs rampant in production code across all industries and in thousands upon thousands of databases.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.