About

NOLOCK and READUNCOMMITTED are equivalent SQL Server table hints, used to apply READ UNCOMMITTED transaction isolation semantics to the hinted table. Other object references in the query are generally unaffected, retaining the isolation level in effect for the statement as a whole.

Under the READ UNCOMMITTED isolation level, transactions can see uncommitted changes made by other concurrent transactions, even if those changes are eventually aborted (rolled back). SQL Server's implementation of READ UNCOMMITTED also allows duplicate reads, partial reads of LOB data types, skipped rows, and statements may also fail completely with error 601:

"Could not continue scan with NOLOCK due to data movement."

Reads performed until the READ UNCOMMITTED isolation level do not generally acquire shared (S) locks (except on the HoBt when reading a heap) and do not respect update (U) or exclusive (X) locks set by other transactions changing data.

Schema stability (Sch-S) locks are still taken during query compilation and execution. A concurrent transaction that takes a schema modification lock (Sch-M), for example by issuing a TRUNCATE TABLE or DDL statement will still block a read with NOLOCK. Similarly, a transaction reading a structure at the READ UNCOMMITTED isolation level will block other processes needing to acquire a Sch-M lock (see the lock compatibility matrix in Books Online).

The READUNCOMMITTED and NOLOCK table hints cannot be applied directly to the target table in INSERT, UPDATE, DELETE, or MERGE operations. The SQL Server optimizer ignores READUNCOMMITTED and NOLOCK hints on any aliases of the target table in the FROM clause of a INSERT, UPDATE, DELETE, or MERGE statement. As well as being ineffective, the ability to specify this hint against an alias of an updated table is deprecated and will be removed from a future version of SQL Server.

Many applications that currently rely on NOLOCK hints to achieve higher concurrency (most commonly to avoid readers blocking writers) can achieve the same effects by switching to the READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON, or by changing to the SNAPSHOT isolation level.

For more information on isolation levels, see SET TRANSACTION ISOLATION LEVEL in Books Online. More information about row-versioning isolation levels can be found in the topic Locking and Row Versioning and subsections.

history | show excerpt | excerpt history