NOLOCK

Overview

When database objects are read or modified, it gets locked so that the database integrity can be maintained. The purpose of Lock is to prevent modification while performing reads or other modifications. But there are occasions when we don’t want these kind of locks. For instance a  query that takes a long time to get executed will prevent us from working on the same object if these locks are in operation. Our general tendency is to wait for it to finish and perform other operation. But there are ways to inform database not to do so, so that we can work without delay. Among them, NOLOCK is one.

What is NOLOCK?

NOLOCK is a table hint and as the name suggests it informs engine not to lock an object. Shared locks are replaced by schema locks. Schema locks us from changing the schema of the object. It is also known as READUNCOMMITTED meaning we will be reading uncommitted changes as well. This can be handy at.
For Example:

1. Lets create a table and insert few values.

CREATETABLE
TestTable
(

column1 VARCHAR(20),
column2 VARCHAR(20),
column3 VARCHAR(20)

)
GO
INSERTINTO
TestTable
VALUES
(‘row1values1’,‘row1values2’,‘row1values3’)
INSERTINTO
TestTable
VALUES
(‘row2values1’,‘row2values2’,‘row2values3’)

2. Now that the values are inserted run an uncommited statement in an transaction.


BEGINTRANSACTION
UPDATE

TestTable

SET


column1=‘Row1Values1’

 

where

column2=‘row1values2’

3.Now open an new query window and run a select statement.
select*from TestTable
We won’t get any result because the table has been locked .

4.Now Use NOLOCK table hint and run the following query.

select*from TestTable with (NOLOCK)
select*from TestTable with (READUNCOMMITTED)
 
 
We get the result. We get the updated values as well though it has not been committed yet.
 

5. Run the following in the first query window.


ROLLBACK
6.Go back to the second query window and run the following query
 
SELECT*FROM TestTable
 
SELECT*FROM TestTable WITH (NOLOCK)

 

SELECT*FROM TestTable WITH (READUNCOMMITTED)
 

Since NOLOCK comes with the power to read the uncommitted changes as well , there can be times when it may be misleading .So  NOLOCK should be used with care.

References:

Share this article


About the author