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.
Why It Matters What Kind of Lock NOLOCK Takes
The schema stability lock, shown as Sch-S in the lock DMV, is small but it has a real effect. It stops anyone from changing the table while your query reads it. So a query with NOLOCK can still be blocked, for example by an offline index rebuild or an ALTER TABLE that needs a schema modification lock. If you have ever seen a NOLOCK query stuck behind maintenance, this is the reason.
What NOLOCK skips is the shared lock on rows and pages. That is why writers do not block it, and also why it is risky:
- It can read changes that are later rolled back, often called dirty reads.
- It can miss rows, or read the same row twice, when pages split during the scan.
- It can even fail with error 601 when data moves while the scan is running.
NOLOCK is the same as the READ UNCOMMITTED isolation level, just set per table. If blocking between readers and writers is the real problem, look at READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation first. Readers then see the last committed version of each row, without blocking writers and without dirty reads.
Use NOLOCK only when a rough answer is truly fine, such as a quick row count on a busy table, and never for totals that people will act on.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
9 Comments. Leave new
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.
Good post,
i got the details of lock…
thanks pinal
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.
Thanks again.
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,