SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object?

Recently I was talking with Vinod Kumar regarding NOLOCK. Suddenly he asked me do I know what kind of lock WITH(NOLOCK) hint takes on object. The immediate response of mine was that NOLOCK does not take any lock. He responded suggesting that I should think more and answer.

I realized right after his suggestion to think harder and I said Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. Here is the script to prove it.

Step 1: Run following script with query hint NOLOCK

SELECT *
FROM sys.all_objects a WITH (NOLOCK)
CROSS JOIN sys.all_objects b WITH (NOLOCK)


Step 2: Check Lock on the object using DMV

SELECT resource_database_id, request_mode, request_type, *
FROM sys.dm_tran_locks

You will clearly see from resultset that NOLOCK takes Schema Lock. Well it is very natural but the name NOLOCK sometime makes us to make incorrect statement.

Please also read great blog post by Robert Davis on The Truth about NOLOCK Hints.

Reference : Pinal Dave (https://blog.sqlauthority.com)