Описание тега nolock

Описание тега Вопросы с тегом
A SQL Server table hint that can be used to prevent queries from issuing shared read locks. While often used to prevent a query from blocking other queries it also makes it susceptible to dirty reads and other potential data issues.

From the SQL Server Books Online topic on Table Hints:

Is equivalent to READUNCOMMITTED.

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

You can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:

  • The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON.
  • The SNAPSHOT isolation level.