24

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

SELECT SomeField FROM SomeTable NOLOCK

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

TAB     IS  GRANT

PAG    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:

SELECT SomeField FROM SomeTable WITH (NOLOCK)
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.

51
SELECT SomeField
FROM   SomeTable NOLOCK 

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

SELECT NOLOCK.SomeField
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 Jan 30 '17 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. – Gareth Lyons Jan 30 '17 at 19:26
  • Is NOLOCK not a reserved keyword? Shouldn't it complain if you don't use [NOLOCK]? – Aaroninus Jan 30 '17 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. – Gareth Lyons Jan 30 '17 at 20:05
  • Nice catch, LOL. My favorite Q/A today. – RBarryYoung Jan 31 '17 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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