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.

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

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

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

Solarwinds
Previous Post
SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Next Post
SQL SERVER – SQL Server Management Pack Guide for System Center Operations Manager 2007

Related Posts

9 Comments. Leave new

  • Nakul Vachhrajani
    May 8, 2011 10:57 pm

    Great question followed by a simple answer – and for once, I was also stumped. But, thinking for a bit cleared my thoughts.

    I believe the rationale behind this behaviour of SQL Server is that with NOLOCK is similar to READUNCOMMITTED, which means “no shared locks are issued and no exclusive locks are honored” (BOL: . For any query plan to compile and execute, SQL Server definitely needs to ensure that the underlying schema does not change during the process. That is reason #1 as to why SQL Server acquires a Sch-M lock.

    Reason #2 is hidden behind reason#1. As mentioned, SQL Server needs to ensure that the schema does not change. A way to ensure this is to issue a table-lock as well. But, that would eliminate the whole point of being the least costly option – the least costly option is to acquire as high-level a lock as possible, which is the Sch-M.

    Reply
  • This can be a good interview question. I was stumped initially as well.

    Reply
  • Danforth Locksmith
    February 6, 2012 11:54 am

    Using this enhancement we don not require temp table and then insert the records from executing stored procedure in case when we mostly require change data and also change column name from result set of stored procedure.

    Reply
  • Good post,
    i got the details of lock…
    thanks pinal

    Reply
  • Dushyant Dhavan
    August 9, 2012 7:52 am

    This is nice question to ask and understand what is exactly happening inside SQL server. However it would be great if explained which approach is correct, NOLOCK, WITH [NOLOCK] or not using any hints? How much performance impact noticed when you pulling out data from millions of rows. I would like to know about this in detail. Any help ??????

    Reply
  • First off I would like to say thanks for your great write ups on all sorts of topics. I found another website that might help us all understand this topic better.

    Basically the using the with (nolock) option allows you to pull data from a table if it is being updated. My co-worker who is more experienced than me in t-SQL referred to that as a “dirty read”. In our shop we are doing reporting and not worrying about transactions to happen. Normally our data is already process before we begin to report on it. So it is not a big deal.

    Thanks again.

    Reply
  • Find all objects in stored procedure not having (NOLOCK) statement

    Reply
  • Mayur Kothiwala
    July 5, 2016 9:37 am

    i create one store procedure with two different statements (first update and then select). now how can i apply no lock here to avoid deadlock. when i implement it, shows error that need to use ; to terminate previous statement (update).but i need to use both statement in same store procedure and also need to avoid deadlock situation.please provide any suggestion,

    Reply

Leave a Reply

Menu