Hi guys, I want to tell you the use of NOLOCK with simple example but before this lets discuss some terms
Lock:-
When multiple users or applications access the non-shareable resource at the same time, Locking allows to access the resource sequentially. Locks are maintained internally by SQL Server. It is automatically acquired and released.
There are broadly two types of lock
1) Shared lock
It's a read lock and it is applied when we execute select statement or any other statement in which no modification of data is happening.
2) Exclusive lock
It is applied when modification of data is happening. When this lock is applied only sequential access is allowed on that DB object
Transaction:-
A unit of work executed to be whole.
Dirty read:-
Reading the uncommitted data is called dirty read. Lets understand this with example suppose we perform update on table in transaction and before the transaction is committed we accessed the modified data so this data is dirty read because it might be possible that accessed data might not be part of this table if transaction is rolled back.
Now its ideal time to discuss the NOLOCK
Please use the below script for table schema
CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NULL,
[Name] [varchar](50) NULL,
[Phone] [varchar](10) NULL,
[Email] [varchar](50) NULL,
)
I have some records in this table
![](http://findnerd.com/imagedata/4201/4201.gif)
Now execute the following query in window 1 of SQL Sever. I am updating the User table in transaction but not committing it. This will put the exclusive lock on this table means no operation is allowed until transaction is committed.
![](http://findnerd.com/imagedata/4205/4205.gif)
now open the another window and try to access the user table. We will see its not returning the result due to exclusive lock on user table
![](http://findnerd.com/imagedata/4206/4206.gif)
Here NOLOCK will do the trick. Now if we execute the query with NOLOCK it will return the result but it returned the dirty data because transaction is not committed
![](http://findnerd.com/imagedata/4207/4207.jpg)
0 Comment(s)