問題描述

一位同事編寫了一個使用提示with (NOLOCK,NOWAIT)"的查詢.

A colleague wrote a query which uses the hints "with (NOLOCK,NOWAIT)".

例如

select first_name, last_name, age
from people with (nolock,nowait)

假設:

NOLOCK 說不用擔心任何級別的任何鎖定，現在就讀取數據"

NOLOCK says "don't worry about any locks at any level, just read the data now"

NOWAIT 說不要等待，如果表被鎖定，只會出錯"

問題:
為什么同時使用兩者?當然 NOWAIT 永遠不會被實現，因為 NOLOCK 意味著它無論如何都不會等待鎖......?

NOWAIT says "don't wait, just error if the table is locked"

Question:
Why use both at the same time? Surely NOWAIT will never be realised, as NOLOCK means it wouldn't wait for locks anyway ... ?

推薦答案

這是多余的(或者至少是無效的).在一個查詢窗口中，執行:

It's redundant (or at least, ineffective). In one query window, execute:

create table T (ID int not null)
begin transaction
alter table T add ID2 int not null

保持這個窗口打開，打開另一個查詢窗口并執行:

leave this window open, open another query window and execute:

select * from T WITH (NOLOCK,NOWAIT)

盡管有 NOWAIT 提示，并且盡管記錄為在遇到任何鎖時立即返回消息，但第二個查詢將掛起，等待 Schema 鎖.

Despite the NOWAIT hint, and despite it being documented as returning a message as soon as any lock is encountered, this second query will hang, waiting for the Schema lock.

<打擊>閱讀有關表格提示的文檔:

NOWAIT:

指示數據庫引擎在表上遇到鎖時立即返回消息

Instructs the Database Engine to return a message as soon as a lock is encountered on the table

請注意，這里指的是鎖，任何鎖.

Note that this is talking about a lock, any lock.

NOLOCK(嗯，實際上是READUNCOMMITTED):

READUNCOMMITTED 和 NOLOCK 提示僅適用于數據鎖.所有查詢，包括那些帶有 READUNCOMMITTED 和 NOLOCK 提示的查詢，都會在編譯和執行期間獲取 Sch-S(架構穩定性)鎖.因此，當并發??事務在表上持有 Sch-M(架構修改)鎖時，查詢會被阻塞.

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.

所以，NOLOCK 確實需要等待 some 鎖.

So, NOLOCK does need to wait for some locks.

這篇關于為什么要同時使用 NOLOCK 和 NOWAIT?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！