为什么要同时使用 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:

指示数据库引擎在表上遇到锁时立即返回消息

请注意，这里指的是锁，任何锁.

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

NOLOCK(嗯，实际上是READUNCOMMITTED):

READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁.所有查询，包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询，都会在编译和执行期间获取 Sch-S(架构稳定性)锁.因此，当并发​​事务在表上持有 Sch-M(架构修改)锁时，查询会被阻塞.

所以，NOLOCK 确实需要等待 some 锁.

So, NOLOCK does need to wait for some locks.

这篇关于为什么要同时使用 NOLOCK 和 NOWAIT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！