How NOLOCK works in SQL Server

Hello friends, today I am going to talk about Table hint(NOLOCK), I have seen lot of developers who all use NOLOCK table hint in their select queries but do not know how it works.

NOLOCK :- When we use NOLOCK table hint in a select query, it reads the uncommitted data from that table. It is similar to setting up isolation level "Read uncommitted". Lets take an example to understand it clearly.

I have created a table "Employee"  and inserted few rows into it using below script.

create table Employee(Eid int, Name varchar(50))

insert into Employee values(101,'Neeraj')

insert into Employee values(102,'Neha')

insert into Employee values(103,'Rachit')

Lets updated Employee name where employee id=103 using below statement.

Begin transaction

Update Employee set Name='Vikas' where Eid=103

We can see that we have updated one row of employee table in session(56)(shown by red arrow), now we will open a new session and try to select all rows from Employee table using below query.

Select * from Employee

we can see in the above screen shot query is executing, and not returning any result set.

Reason:- Since we have not committed the transaction yet in session(56) after updating the Employee table, hence Employee table is locked and we are not able to select data from Employee table in different session.

Lets write below query and see what happens.

Select * from Employee(NOLOCK)

 

I have put NOLOCK table hint in the query and query works fine and reason is that query is reading the uncommitted data from employee table, the change we made in table is visible that is not committed yet(shown by red arrow).

Note:- We should think before using NOLOCK in queries whether it has impact or not, in other way if we are querying financial data where data correctness is most important, we should not use NOLOCK .

  

I hope you will like this article.