I was investigating some blocking when I saw a query that looked something like this:


I saw the NOLOCK and was curious how it could be blocking other queries, in this case DELETE statements. I took a quick look at the locks using sp_lock and here is what I saw:

DB      S   GRANT



Now, my understanding is that NOLOCK is supposed to only take a Schema-Stability lock, why was it then grabbing an IS lock?

My curiosity was piqued. I looked in BOL and saw there were two ways to use it, WITH (NOLOCK) and the deprecated (NOLOCK), so I decided to give those a try. I ran the following queries followed up by running sp_lock:

DB     S       GRANT

TAB     Sch-S   GRANT
SELECT SomeField FROM SomeTable (NOLOCK)
DB      S       GRANT

TAB     Sch-S   GRANT

Sure enough, there are my Schema-Stability locks. So my question is this: what’s going on here? If the accepted syntax for using NOLOCK is either WITH (NOLOCK) or (NOLOCK), then why doesn’t the query error out when it runs with just plain NOLOCK (without the parentheses)? If it is supported, why is grabbing an IS lock? What am I missing here? I’ve been searching online for an answer, but so far have come up short.

I’ve tested this on both 2008R2 and 2012.


1 Answer 1

SELECT SomeField
FROM   SomeTable NOLOCK 

means you've just aliased SomeTable AS NOLOCK. Try the below to see this clearly:

FROM   SomeTable NOLOCK 

This obviously has no effect on the locking behaviour of the query. The query doesn't fail because despite being a keyword & showing blue in SSMS, NOLOCK is not a reserved word in Transact-SQL and therefore does not cause a syntax error. List of reserved words: https://msdn.microsoft.com/en-us/library/ms189822.aspx

Correct syntax for using as a hint:

  • (NOLOCK) is valid but deprecated.
  • WITH (NOLOCK) is the recommended syntax.
  • 14
    Wow, not sure how I didn't figure that out. It was really driving me crazy, now I just feel embarrassed:) It's the simplest things sometimes, I guess.
    – Brian
    Commented Jan 30, 2017 at 19:21
  • 2
    @Brian No worries, I had to debug something very similar recently, otherwise it may not have been so easy to spot! You can see why MS have deprecated that syntax. Commented Jan 30, 2017 at 19:26
  • Is NOLOCK not a reserved keyword? Shouldn't it complain if you don't use [NOLOCK]?
    – Aaroninus
    Commented Jan 30, 2017 at 20:01
  • 6
    Nope msdn.microsoft.com/en-us/library/ms189822.aspx Although it does appear in blue in SSMS which can throw you off. Commented Jan 30, 2017 at 20:05
  • Nice catch, LOL. My favorite Q/A today. Commented Jan 31, 2017 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.