Resources For IT Professionals
Page Details
  • First published by (0Microsoft Partne)
  • When: 14 Aug 2013 1:55 AM
  • Last revision by (eMicrosoft Partne)
  • When: 11 Sep 2013 3:27 PM
  • Revisions: 9
  • Comments: 2
Options

Revision #2

Wiki  >  TechNet Articles  >  Understanding NOLOCK Query Hint  >  Revision #2
Understanding NOLOCK Query Hint
You are currently reviewing an older revision of this page.
Go to current version

In our day to day TSQL quering we use lot of query hints to modify the way a particular query will be executed .When we specify query hint SQL server produces optimized plan using this query hint .This can be dangerous if it is not tested before in UAT as it is known fact that query plan which sql server makes using optimizer, which is its  prized posession ,is the best.Algorithm written for optimizer at low level  is not known to anybody ,how it makes best/optimized, most cost effective plan is not known to outside world but we know it does.
Query hints specify that the indicated hints should be used throughout the query and it  affect all operators in the statement.One such query hint is NOLOCK.As the name suggests many users feel  when specified in TSQL,the  operation does not takes lock.This is not the case

I will demonstrate it using simple query.I create a simple table with e_id as PK col ,name ,address and cell no.

Begin tran 
  
select * from dbo.employee where e_id='a1' 
  
exec sp_lock

If you see below this transaction has SPID 55 which is ID for the code which is just executed .It has taken two locks IS,S

 

In Mode Column
S=Shared lock
IS=Intent Shared

In type column
DB=databse
TAB=table

Now let us run same query with NOLOCK query hint and see if it actually takes any lock

Begin tran 
  
select * from dbo.employee with (NOLOCK) where e_id='a1' 
  
exec sp_lock




As you can see same lock is taken  on same table (see Objid in both fig they are same 1131151075) .IS and S.
So point is what is difference between query execution one which is given with NOLOCK and one which is not given with any nolock query hint.

Difference comes when both are trying to select data from table which has taken exclusive lock ,I mean to say difference comes when query is trying to access table which is locked by INSERT/UPDATE statement.
I will show this with query>let us run an update command on the same table for same row.

begin tran
  
update dbo.employee set e_name='SHASHANK' where e_id='a1'
  
exec sp_lock

Now i run same above queries qury 1 and query 2

Query 1



Now other query which is not using any Query hint



Now we see the difference query with NOLOCK query hint produced output but simple query with no hint is not producing any output .It is blocked and that can be seen by runnig SP_Who2 ,i ran this query and result is below




As you can see SPID 56 is blocking SPID 55 .Then I ran DBCC INPUTBUFFER comamnd to find out text correrponding to these SPID's ,below is result




From above query output it is clear that when we use NOLOCK query hint transaction can read data from table which is locked by Update/insert/delete statement by takein exclusive lock(exclusive lock is not compatible with any other lock).But if in same transaction we dont use query hint (NOLOCK) it will be blocked by update statement.
Draw back of NOLOCK is dirty read.So it is not advised to use it in production enviroment.But can be used to read data from a table partition which wont be updated when this select is runnig. Like you can run query to select data from Table partition containing Jan 2013 data suuming no records will be updated for Jan month.
Revert to this revision