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.
Best Answer
means you've just aliased
SomeTable AS NOLOCK
. Try the below to see this clearly: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.