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 (http://blog.SQLAuthority.com)

About these ads

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

  1. 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: http://msdn.microsoft.com/en-us/library/ms187373.aspx). 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.

    Like

  2. Pingback: SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

  3. 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.

    Like

  4. 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 ??????

    Like

  5. 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.

    http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

    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.

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s