A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.
What is NOLOCK
NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.
It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true that any Sch-M lock will block the Sch-S lock request until it hold the resources.
Also remember, it can be used only with SELECT statements and not with tables being modified by DELETE, UPDATE or INSERT statements.
SELECT * FROM TABLENAME WITH(NOLOCK) SELECT * FROM TABLENAME WITH(READUNCOMMITTED)
Pros and cons of NOLOCK
1. Reduces blocking:
Least restrictive isolation level of SQL Server READ UNCOMMITTED used during compilation and execution which increases the concurrency with cost of dirty reads. Readers can read and writers can write at the same time.
2. Can change Isolation level for individual tables:
NOLOCK hint reads uncommitted data from specified tables by changing the isolation level for those tables only during execution of the query. Isolation level can be handled at table level instead of query level using NOLOCK table hint.
1. Dirty Reads:
Reading data which is not committed yet is a “dirty” data, which may or may not be committed. READ UNCOMMITTED isolation level allows to read uncommitted data from other concurrent transactions which may lead to these dirty reads.
2. Non Repeatable Reads
In case, you read the data repeatedly and its changes during this process, may leads to different data during each read for same row.
3. Phantom Reads
Phantom read occurs when a delete or insert is performed on a row being read by a transaction. So delete will cause less record whereas insert will cause more records,
We can achieve the same thing by setting the isolation level to READ UNCOMMITTED. But it changes the isolation level for entire query and NOLOCK hint works at table level. In case you have many tables and some of them are read only, you can set the NOLOCK hint (in turn READ UNCOMMITTED isolation level) on those tables only.