NOLOCK in SQL

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations.

SELECT * FROM Employee WITH (READUNCOMMITTED)

this is same as

SELECT * FROM Employee WITH NOLOCK

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

Check this link for an example.

http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/[^]

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Neha Sharma14y ago
Hii, Refer this link: http://www.allinterview.com/showanswers/5546.html if it helps you mark it as answer.
Satyapriya Nayak14y ago
http://www.devasp.net/net/articles/display/926.html
Santhosh Kumar Jayaraman14y ago
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations.

SELECT * FROM Employee WITH (READUNCOMMITTED)

this is same as

SELECT * FROM Employee WITH NOLOCK

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

Check this link for an example.

http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/[^]