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
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, *
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)
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.
This can be a good interview question. I was stumped initially as well.
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.
i got the details of lock…
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 ??????
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.
Find all objects in stored procedure not having (NOLOCK) statement
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,