WITH (NOLOCK) example

Introduction

I want to show the use of With (NoLock) hit with a simple example but before that let's brush up following term.
Lock it allows different type of resource to be lock by the transaction.
When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.
A transaction is unit of work submitted as hole to database for processing.
Dirty Read is a process of reading database record without locking the record being read.
Stand the risk of reading an uncommitted row that is subsequently rolled back. Resulting in reading of local copy of record that is not consistent with what is stored in database.
Non repeatable read is a situation where a session finds itself in when it perform multiple read.
It is possible to perform the query in the same transaction more than one and show different result.
Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.
This occur when user insert a record while transaction is underway.
Shared Lock(s) is read lock it is occur when SQL server perform select statement or any other statement to read the data tell to read but do not modify the data.
When one user is reading the data he/she issue read lock so that other user do not modify data.
Exclusive Lock (x) are generally use during modification activity use to lock data being modified by one transaction.it prevent modification by another concurrent transaction.
Update Lock (u) update lock a mix of shared and exclusive lock.
Update Lock is kind of Exclusive Lock except it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of row which has Shared Lock, as soon as Update Lock is ready to change the data it converts itself to Exclusive Lock.
WITH (NOLOCK) hit
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

Background 

In the following example we are denoting two seperate user by opening two sperate query window in SQL Server. I am using Northwind database on both the windows. So sotry is like this
User one update table saying that Dairy Products are Chees and Paneer but he did not commit data.
User one weight for an approval.
User two fire Select qury same time but qury run infinite loop as select qury issue share lock that is not granted as User one is updating data.
Another user fire Select qury same time WITH (NOLOCK) hit and qury run to show uncommitted data as Diry product "Chees and Paneer".
As our user one approval is rejected and he roll back his update to Dairy product to Chees only.
Oh the user with nolock hit is still considering that Diry product contin the Paneer.
But he still avoid the situation of being dead lock like user one.

Using the code

Let's open query window one and run the update query as mention bellow.

Collapse | Copy Code

--Query window 1
USE Northwind

Collapse | Copy Code

SELECT * FROM Categories

Collapse | Copy Code

BEGIN TRANSACTION
 
UPDATE Categories SET Description='Cheese and Paneer' 
WHERE CategoryName='Dairy Products' 

Collapse | Copy Code

SELECT * FROM Categories 

Open query window two and fire the same SELECT query.

Collapse | Copy Code

--Qury window 2
USE Northwind 
SELECT * FROM Categories 

I run the same query from above again and i have notice that it never completes, because the UPDATE has not yet been committed.
Open query window third and fire the same SELECT query WITH (NOLOCK) hit.

Collapse | Copy Code

--Qury window 3 
USE Northwind 
SELECT * FROM Categories WITH (NOLOCK) 

Collapse | Copy Code

--Let me rollback the query in Window 1. 
ROLLBACK 
SELECT * FROM Categories  

But our window  3 user is still considering that  Diary Products are Cheese and Paneer reading uncommitted data that is rolled back after time.
And our window 2 user is fall in to dead lock condition.
We can find the lock issue in each window using sp_lock.
If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock.
For window 2 while our query is in dead lock we can veryfy lock on the page by firing sp_lock on separate window.
You can find that there is share lock issue when you run the select query with out (NOLOCK) and there is NO Share lock issue when you run the qury with (NOLOCK). Hence when you perform update in that is yet to commit exclusive lock is issue.
Where (NOLOCK) dous not honour exclusive lock.


More Links:  http://sqlserverplanet.com/tsql/using-with-nolock