Introduction

In our day to day T-SQL querying 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 possession, is the best.

The algorithm which is written for optimizer at low level is not known to the ordinary people,  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 they affect all operators in the statement. One such query hint is NOLOCK. As the name suggests many users feel, that when this hint is specified in the query, the  operation does not takes lock. This is not correct.


Performing the test

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  = Database
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 the 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 the same row.

BEGIN TRAN  
  
UPDATE   dbo.employee SET  e_name='SHASHANK'  WHERE   e_id = 'a1' 
    
EXEC   sp_lock

Now I run the same queries Query1 and Query2

Query 1 - Running with query hint NOLOCK




Query 2 - 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 running 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 command to find out text corresponding to these SPID's, below is the result:





Conclusion

From the 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 taking the exclusive lock (exclusive lock is not compatible with any other lock). But if in same transaction we don't use query hint (NOLOCK) it will be blocked by update statement.

Drawback of NOLOCK is dirty read. So it is not advised to use it in production environment. But it can be used to read data from a table partition which won't be updated when this select is running. Like you can run query to select data from Table partition containing Jan 2013 data summing no records will be updated for January.


See Also


Other Languages