Understanding the SQL Server NOLOCK hint

By:   |   Updated: 2022-02-23   |   Comments (61)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Locking and Blocking


I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain what NOLOCK does and whether this is a good practice or not?


It seems that in some SQL Server shops the use of the NOLOCK (aka READUNCOMMITED) hint is used throughout the application. In this tip we take a closer look at how this works and what the issues maybe when using NOLOCK.

What does the SQL Server NOLOCK hint do?

  • The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes.
  • This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.
  • Read further to better understand the use of NOLOCK.

Example of SQL Server NOLOCK

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)

Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20
use of the nolock(aka readuncommited) hint

Let's say another user runs the below query in a transaction. The query completes and updates the records, but it is not yet committed to the database so the records are locked.

-- run in query window 1
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20

If you run the same query that was run above again, you will notice that it never completes, because the UPDATE statement run in "query window 1" has not yet been committed, so the locks are not released.

-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20

If I run sp_who2 I can see that the SELECT statement is being blocked. I will need to either cancel this query or COMMIT or ROLLBACK the query in window 1 for this to complete. For this example I am going to cancel the SELECT query.

commit or rollback query

To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 

If you notice below the Suffix column now has "B" for all records. This is because the UPDATE in window 1 updated these records. Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data. If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1.

using the nolock hint sql server ignores the locks

If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.

-- run in query window 1
-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20
using the rollback command

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database. If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

Types of SQL Server Locks Used with NOLOCK

So what kind of locking is used when the NOLOCK hint is used.

If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock. (To get the lock information I ran sp_lock in another query window while this was running.)

SELECT * FROM Person.Contact WHERE ContactID < 20 
we can see the locks that are taken if we use sp_lock

If we do the same for our SELECT with the NOLOCK we can see these locks.

SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 
sql server still creates a lock to make sure the data is consistent

The differences are that there is a "S" shared access lock that is put on the page (PAG) that we are reading for the first 19 rows of data in the table when we don't use NOLOCK. Also, we are getting a Sch-S lock versus an IS lock for the table (TAB).

So another thing to point out is that even when you just SELECT data SQL Server still creates a lock to make sure the data is consistent.

These are the lock types and the lock modes that are used for the above two queries.

Lock Types

  • MD - metadata lock
  • DB - database lock
  • TAB - table lock
  • PAG - page lock


  • S - Shared access
  • Sch-S - Schema stability makes sure the schema is not changed while object is in use
  • IS - Intent shared indicates intention to use S locks


The NOLOCK hint is the same as the READUNCOMMITED hint and can be used as follows with the same results.


SELECT statements only with NOLOCK

The NOLOCK and READUNCOMMITED hints should only be used with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

You will see this error.

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

But there is a way to get around this if the query is written differenlty, but you shouldn't.

UPDATE p SET Suffix = 'B'
   FROM Person.Contact p WITH (NOLOCK)
WHERE ContactID < 20

See this article Avoid using NOLOCK on SQL Server UPDATE and DELETE statements to learn why this is not a good idea.

Schema Change Blocking with NOLOCK

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.

-- run in query window 1
ALTER TABLE Person.Contact ADD column_b 

If we try to run our SELECT statement it will be blocked until the above is committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 

Issues with NOLOCK

We mentioned above how you can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

  • Dirty Reads - this occurs when updates are done, so the data you select could be different.
  • Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
  • Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

To learn more about these issues read this article: Concurrency Effects

Isolation Level

You can also set the Isolation Level for all queries instead of using the NOLOCK or READUNCOMMITTED hint. The isolation level will apply the READUNCOMMITTED to all SELECT statements that are performed from when this is turned on until it is turned off.

In the example below, the two SELECT statements will use the READUNCOMMITED (aka NOLOCK) hint and the UPDATE will still function as normal. This way you can set a whole batch of statements instead of modifying each query.


SELECT * FROM Person.Contact WHERE ContactID < 20 

UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1 

SELECT * FROM Person.Contact WHERE ContactID < 20 

Next Steps
  • Now that you have a better understanding of how NOLOCK works check your code to see if there are instances where the NOLOCK hint doesn't make sense and you may not be getting the results you thought you were.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-02-23

Comments For This Article

Friday, November 24, 2023 - 3:00:19 AM - Jeeva Back To Top (91774)
very useful. thanks

Monday, November 6, 2023 - 2:34:10 AM - Devendra Back To Top (91734)
Very good article. Thanks for sharing

Wednesday, August 24, 2022 - 10:38:25 PM - Gary Back To Top (90411)
My understanding is most Databases default to READCOMMITED isolation level and when SELECT statement reads data from the table which is in updating process but not commit yet, the "read" will not be blocked and DB will return the "old" data. Is this statement true?

Is it it true SQL Server has special feature that when table is in updating process and not commit yet, it will even block SELECT statement to read data unit commit is complete? Why does SQL Server choose this unusual implementation?


Monday, August 15, 2022 - 9:32:57 AM - Greg Robidoux Back To Top (90380)
Hi Kawthar, Microsoft recommends using READUNCOMMITED going forward if you need this functionality.


Friday, August 12, 2022 - 2:31:50 PM - Kawthar Back To Top (90376)
I have never liked NOLOCK in Hints and as you know SQL has deprecated those features for a while now. How do you answer that?

Thursday, February 3, 2022 - 10:01:06 AM - Karim Back To Top (89732)
Very helpful.

Thursday, December 26, 2019 - 6:13:53 AM - Rick Dobson Back To Top (83526)

Well done. Thanks.

Monday, September 2, 2019 - 9:55:39 AM - Bogdan Back To Top (82211)

Great, thank you for this clear explanation. 

Monday, May 6, 2019 - 1:33:57 PM - Greg Robidoux Back To Top (79958)

Hi Bryant,

Thanks for the input.  As you mentioned, Microsoft lists this as a hint in their list of hints.


The goal of this tip was to show people the danger of using nolock in production and the issue with dirty reads.  I think there is a place for using this, but I agree people need to know that the drawbacks can outweigh the benefits.  I have worked with many customers and see NOLOCK all over the place.  I don't think everyone understands how this works and the downsides.


Monday, May 6, 2019 - 1:18:25 PM - Bryant Back To Top (79955)


Consider that NOLOCK is NOT a hint. It is a command. Nothing provides the optimizer with the choice of ignoring the hint and moving along on its happy way, totally oblivious to the help the developer so thoughtfully provided. MS documentation might regard it as a hint but in working software it is anything but. NOLOCK can be dangerous for reasons you, Itzik, Kimberly and many others have shown.

For those of us with some multiple decades of experience the coding choice is more obvious. Newer developers need to understand the odds against them arguing with the optimizer and winning. Portraying NOLOCK as anything less than a command gives the casual reader comfort in a place where they should instead exercise extreme caution.

Thursday, March 28, 2019 - 4:07:21 AM - Dmitriy Back To Top (79405)

Thanks a lot for such great explanation!!!!

Tuesday, August 28, 2018 - 1:02:30 PM - Greg Robidoux Back To Top (77320)

Hi Chobe,

I tried the examples with SQL Server 2017 and they still work the same. Can you clarify what you mean by UNLOCK?


Monday, August 27, 2018 - 3:41:00 PM - Chobe Back To Top (77315)

I tried to do the example to lock the select statement in sqlserver2017 but all works fine ignoring any locks and apparently in this version 'UNLOCK' is deprecated, so  'unlock' is implicit in any SELECT statament 

Tuesday, May 8, 2018 - 9:19:30 AM - Sari Back To Top (75898)

Nice and detailed explanation.

Friday, March 30, 2018 - 1:12:51 AM - venkat Back To Top (75558)


 very simple and nice

Wednesday, December 27, 2017 - 5:04:07 PM - Greg Robidoux Back To Top (74515)

Hi Sachin,

you can download from here: https://www.microsoft.com/en-us/download/details.aspx?id=49502

Or just do an internet search.  You can find this for different SQL Server versions in various places.



Wednesday, December 27, 2017 - 3:35:38 PM - Sachin Kumar Back To Top (74510)


From where I can download AdventureWorks database ?


Wednesday, August 2, 2017 - 11:00:33 PM - baji Back To Top (63820)

 /** Quite Useful!! ***/


Thursday, March 9, 2017 - 6:40:20 AM - Jem Back To Top (47654)

Thanks for this - concise, helpful, not over-complicated and really good examples :) Cheers

Wednesday, January 4, 2017 - 5:02:39 AM - Paresh Rathod Back To Top (45109)

 Thanks a lot for providing detailed explanation with examples


Thursday, July 21, 2016 - 5:21:01 AM - Klaus Back To Top (41935)

Nice article, indeed!
I wonder if there is a way to do select which returns the data before the other session started the transaction without beeing locked by that session.
Thanks and regards

Tuesday, July 12, 2016 - 2:19:45 AM - sivasai s Back To Top (41866)

 Nice Article. Worth reading it.


Thursday, June 30, 2016 - 6:19:45 AM - karthic Back To Top (41794)

 Nice one Greg Robidoux !!!

There is a case for me calling web service from SQL server ...Really fed up with not finshed(broken) code/ pieces.

Tuesday, May 3, 2016 - 5:25:40 AM - Umesh P Back To Top (41390)

 Nice Article with very good explaination with the help of examples. 


Friday, February 19, 2016 - 1:54:58 AM - m.h Back To Top (40726)

that is good very good.

very thanks.

Wednesday, February 3, 2016 - 10:56:11 PM - Praveen Back To Top (40587)

Thanks for teaching me this topic. You did  it very well. 


Thursday, October 8, 2015 - 1:36:11 PM - J Back To Top (38846)

The T-SQL code for the views in CRM 2011 make extensive use of the NO LOCK hint.

Maybe there are ways known to a select few that allow implementing the hint without any adverse consequences.

Friday, September 18, 2015 - 9:44:59 AM - Greg Robidoux Back To Top (38709)

Hi Kavitha,

the first thing to do is to see how you can tune your queries.  You can use Profiler or a server side trace to look for long running queries.  I usually start with things that run over 500ms.  From there review the query plan and see if you can make these run faster either by reworking the query or adding indexes.

Also, check the system wait stats to make sure the issue is not something SQL related. This could be CPU, memory, IO, etc. and adjust as needed.

Sorry there is not a simple answer, but this is where you should start.

Monday, September 14, 2015 - 3:42:46 AM - Kavitha Back To Top (38671)

How to improve my database performance speed?  i have using sql server 2008 r2, 100 users access my database at a time through online web application. size of database 13GB. i am not using views and Triggers. i have create more index also.

Thursday, June 18, 2015 - 8:37:48 AM - Kuldeep Back To Top (37949)

Very nice post.  


Tuesday, April 28, 2015 - 4:35:46 PM - Herv Back To Top (37056)

Some of us are just born teachers.

Wednesday, March 11, 2015 - 10:52:31 AM - Akash Back To Top (36490)

Thank you for such wonderful explaination, Grag. It helps me to understand the concept.


Kind Regards,


Monday, November 3, 2014 - 5:32:00 AM - anil Back To Top (35165)

HI Grag,

Thank you for sharing this article. It halped me a lot in doing RCA for a blocking issue.




Tuesday, August 26, 2014 - 3:49:45 AM - Ramanagoud P Back To Top (34257)

Very simple & nice explaination.

Thank you so much.

Tuesday, August 12, 2014 - 11:24:27 AM - mkh Back To Top (34094)

Cool thanks, I was missing () in NOLOCK.

Friday, August 1, 2014 - 3:27:25 PM - Greg Robidoux Back To Top (33983)

Yes you can use NOLOCK on multiple tables if you are doing JOINS.

Read this tip too: http://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/

Friday, August 1, 2014 - 2:24:08 PM - Boris Back To Top (33982)

Great explanation.

Nolock can be also used with Joins 

Tuesday, July 22, 2014 - 8:41:55 AM - Praveen Back To Top (32823)


Excellent explaination

Sunday, February 9, 2014 - 5:52:49 AM - swapnil Back To Top (29377)


drop  table #tmp

create table #tmp


spid nvarchar(200) ,    dbid int ,    ObjId int ,   IndId int ,   Type varchar(200) , Resource varchar(2000) ,     Mode varchar(200) , Status varchar(200)



insert #tmp 

exec sp_lock


select DISTINCT spid ,[dbid],[TYPE] , 'dbcc inputbuffer('+spid+')' as sql, 'KILL '+spid As sql 

from #tmp WHERE [TYPE] = 'TAB'


Tuesday, January 14, 2014 - 3:16:03 AM - Binh Nguyen Back To Top (28052)

Thanks,nice post

Wednesday, September 25, 2013 - 4:02:57 AM - Vivek Grover Back To Top (26927)

Thanks Greg

This example makes sense to understand WITH (NOLOCK)


Thursday, September 5, 2013 - 9:21:14 AM - Greg Robidoux Back To Top (26618)

@Ramesh - a nonreaptable read is when you need to read the same data more than once and the data changes during the process.  The nolock allows you to get uncommited transactions, so you may get different data during your transaction.  For the second question a KEY lock with an X is an exclusive lock on an index key.  You can use the ObjId and IndID to figure out which table and index by reading sys.objects and sys.indexes to find the information.

Thursday, September 5, 2013 - 5:48:04 AM - Ramesh Back To Top (26616)

Hello Greg,

Firstly thanks for ur useful article about nolock.

Secondly i need some explanation reg dirty reads and unrepeatable reads. Reading the uncommitted data is dirty raad. This is crystal clear. However I'm not clear about unrepeatable reads.

One more thing.. sp_lock showing one more type of lock with TYPE as KEY and Mode as X. Can you shed some light on this as well?

Thanks in advance!

Friday, August 16, 2013 - 2:26:39 AM - Vishanth Back To Top (26341)

good one. . . 

Friday, August 16, 2013 - 2:25:31 AM - Ramesh Narayanan Back To Top (26340)

Awesome Article! ..

Thank You.



Monday, July 29, 2013 - 5:31:52 AM - PRITESH Back To Top (26042)

Nice Artilcle ..... :)

Wednesday, May 8, 2013 - 2:08:43 PM - Greg Robidoux Back To Top (23819)

@Del Lee - I have seen people use this when the result set doesn't need to be perfect and data that is close enough is good enough. 

I would never use this for accounting type reports or where you need to get exact numbers, because you may not get the correct values.

If you want to do a quick count of records based on certain value this could help without being blocked or causing blocking, but again the numbers may not be 100% accurate.

So the bottom line is if you need the exact data at a given point in time don't use NOLOCK if the data can be close enough that it could be a candidate.  I would never just make this a standard practice you should only do this where long running queries may impact your transactions.

Wednesday, May 8, 2013 - 1:36:47 PM - Del Lee Back To Top (23818)

I'm sure I understand the NOLOCK hint, but I'm not sure I know how to apply what I know.  In other words, I'm not sure when I would ever be comfortable with a dirty read.  The general example often given when this is explained is a reporting situation, but for most reports I've done in my career allowing dirty reads would essentially create "garbage out" because data is not consistent or is out of balance.  The easiest scenario to point out would be an accounting report that looks at debits and credits.  When a user sees a report that is out of balance, then the confidence in the integrity of the data can be questioned.

Friday, May 3, 2013 - 5:37:24 AM - ghfgj Back To Top (23700)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editorghjghjj like NotePad before cgjghjopying the code below to remove the SSMS formatting.


Thursday, February 21, 2013 - 7:52:50 AM - Darshan Back To Top (22337)

Hey Greg, Its really good post. The Explaination is very neat and confined what it was menat (NOLOCK) rather than squeezing all the things you know.

I understood the use of NOLOCK after reading this post.


Friday, February 15, 2013 - 3:49:10 AM - Suman Back To Top (22139)

Very Useful article Greg. Understood what NOLOCK actually does after reading your post. Thanks

Thursday, January 24, 2013 - 1:18:31 AM - Madhu Back To Top (21672)


Great work Greg..

Quick, simple and very informative.. Thanks to you..

As I have gained such a nice understanding with this, I would not like to hide that the section of 'Phatom read' was a bit unclear OR may be that you didnt want to go into much details of that probably.

The useful definintion of 'Phantom Read' i found was "Phantom reads occur when an insert or a delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the subsequent read, because of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the subsequent read of the transaction shows a row that did not exist in the original read." Ref: 




Tuesday, January 1, 2013 - 11:04:14 AM - Greg Robidoux Back To Top (21219)

@George - yes the advantage to using this is to avoid being blocked by other processes, which could make this finish faster, and also that even running a SELECT uses locks that could cause blocking for other processing.

I would always look at this as the last option and not as the general rule.

Monday, December 31, 2012 - 4:22:19 PM - George Back To Top (21210)

Thanks for another fantastic post, Greg.  At my work, I run a lot of reports on the data.  None of the things I run are critical.  I have been asked by the DBA to add the NOLOCK hint to everything I write that hits our production database.  We do have problems periodically with locking and it slows down our production line and can occasionally cause issues out there.

It sounds like it would, indeed, be a good idea for me to use the hint.  But I wanted to be sure I understood my use of it correctly.  When I use the hint, I am saying I am okay with dirty reads.  The benefit of this is that my query can finish quicker, rather than waiting for other locks to be released and then locking it with my own query.  I am not able to do a SELECT without locking at least some part (or all) of the table.  Is that correct?

Friday, December 28, 2012 - 11:25:02 AM - Greg Robidoux Back To Top (21176)

@CodePro - you should try to avoid using this hint and only use it in extreme cases where there is a lot of blocking occuring.  As mentioned in the article when you use this hint you get dirty reads, so this should only be used if you are facing blocking issues and you can live with the dirty reads as mentioned in the Issues section.

Friday, December 28, 2012 - 10:27:37 AM - CodePro Back To Top (21174)

I'm still not geting when I should use NOLOCK. As you point out, it is a hint. Generally speaking, you should leave such decisions up to SQL Server and the OS.

Tuesday, August 21, 2012 - 1:28:37 PM - Charles Back To Top (19149)

Great post Greg.   I have personally been using NOLOCK and another hint called READPAST for various situations.  I think it's a good idea that SQL guys know the strengths of the various hints and when to use them, and your post does a good job.

Wednesday, August 17, 2011 - 7:52:44 AM - Scott C Back To Top (14431)

At a previous job we had a web developer that thought GUID primary keys were a great idea.  (Could be one of the reasons he later got canned.)

We found that SELECT COUNT(*) FROM <table with GUID PK> WITH (NOLOCK) was not accurate, even when there was no other activity in the table.  Repeatedly running the command would produce answers that varied up or down by a small amount, apparently at random.  This was in SQL 2005, don't know if it is fixed in 2008.

Other than that, it is very useful for running reports on a production database without killing all the apps.  Not very good for accurately updating bank balances.

Tuesday, August 16, 2011 - 11:38:41 AM - Greg Robidoux Back To Top (14419)

Hi Mike, yes your correct.  A SELECT still places a shared lock on the data.  I tried to show an example of this under the LOCKS section of the tip.  Since I am only doing a small set of records a PAGE lock was used, but this could get escalated to a TABLE lock as you mentioned.

Tuesday, August 16, 2011 - 11:21:56 AM - Mike Back To Top (14417)

Thanks Greg,

for this explanation. But I miss something. A select that is send to the Database can also create a LOCK-Situation if you don't use the NOLOCK statement. So not only updatestatement's can create Locks.

If you use an select statement on an bigger table with 'order' or 'group by' statements in it, you will quick notice that there are some performence Problem's on your Database. Maybe your statement will escalate the lock to an Table-Lock.

Nice overviews of Locking you will get with http://www.sommarskog.se/sqlutil/aba_lockinfo.html 

greetings from Germany


Tuesday, August 16, 2011 - 10:07:21 AM - TheSmilingDBA Back To Top (14413)

Great explanation. Thanks Greg.

I believe NOLOCK is going to be deprecated in a future version of SQL, so users might want to use READUNCOMMEITTED and not NOLOCK.

Itzik Ben-Gan has shown a case where a page split causes a double read of some data using NOLOCKs. You might wnat to BING it!!!



get free sql tips
agree to terms