为什么要同时使用 NOLOCK 和 NOWAIT?
一位同事编写了一个使用提示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.
相关文章