为什么“在应用于 UPDATE 或 DELETE 语句的目标表的 FROM 子句中”忽略 NOLOCK？

Question

我对 BOL 短语感到困惑：

“不能为通过插入、更新或删除操作修改的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器会忽略适用于 UPDATE 或 DELETE 语句的目标表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示” [1]

例如，如果我写

--script 1) 
UPDATE Test SET Txt=(Select Txt from TEST WITH(NOLOCK) where ID=1) 
WHERE ID=1

它运行时没有错误（或警告），并且可能相当于

--script 2)
set transaction isolation level SERIALIZABLE;
begin tran
Declare @nvarm nvarchar(max);

Select @nvarm=Txt from Test where ID=1;
--Select @nvarm;
UPDATE Test  SET Txt=@nvarm  WHERE ID=1;
commit;

运行时没有错误或警告。
是等价的吗？

表是相同的，但在 FROM 中，逻辑上它是源表而不是目标表 我可以用不同的源表重写 1) 作为另一个（物理）表：

--script 3)
select *
into testDup
from TEST;

GO;

UPDATE Test SET Txt=(SELECT Txt FROM TestDUP WITH(NOLOCK) where ID=1) 
    WHERE ID=1

为什么在另一个表上应该忽略 NOLOCK？
或者，如果错了，请提问
如何编写具有“FROM 子句中适用于 UPDATE 或 DELETE 语句的目标表的 NOLOCK 提示”的 UPDATE，因为即使在 1) 和 2) 中，物理表也是相同的，但逻辑上是源（在 SELECT 中）表和目标（在更新中）表是不同的。

如何编写一条 UPDATE 语句来证明WITH(NOLOCK) 被忽略？
为什么要完全忽略它呢？被忽略了吗？
或者，如果这是一个错误的问题，那么
为什么语法允许保证被忽略的提示？

再说一次，要么不可能（或者是？）写出文档中写的这样的语句，要么我不理解“忽略”的含义（忽略它是什么意思？或者根本拥有它？） ...

更新2：
答案表明，在 UPDATE 语句的 FROM 子句中，NOLOCK 并未（更新）被忽略，这是 BOL 文档 [1] 所断言的。
好吧，这个问题的本质是：
你能给我任何例子（上下文），其中忽略 UPDATE 语句的 FROM 子句中的 NOLOCK 是有意义的吗？

[ 1 ]
表提示 (Transact-SQL)
SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/ms187373.aspx

Answer 1

无需猜测。

Sybase 和 MS SQL 服务器使用内部自动 2PL 资源锁定，但完全符合 ISO/IEC/ANSI SQL 标准。当您尝试理解所有可能的组合时，语法会变得很愚蠢，因为某些子句并不与每个命令相关。

该手册试图表达的内容（但不是用简单的英语表达）是：

• 对于您正在执行的任何外部操作或事务中的单个查询，您可以SET ISOLATION LEVEL
• 使用 UNCOMMITTED、NOLOCK、HOLDLOCK 语法指定，以及
• 在外部查询中有一个 IL 或事务中的单个查询，但希望对内部查询使用不同的 IL 时，可以是完成（在内部查询上使用不同的调制器），
• 这样您就可以在 IL3 上执行一个事务，并在其中有一个 SELECT

分别在 IL0 或 IL1 上执行：

• 无论您认为自己在做什么，或者因为锁定是自动的，并且 UPDATES 和 DELETES 需要隔离级别 3，其中READ UNCOMMITTED 和 NOLOCK 不适用，并且不能使用，如果您使用了它们，服务器将忽略它们

Answer 2

UPDATE 或 DELETE 语句的 FROM 子句在您的任何示例中都不明显。子查询中有 from 子句，但它们不是同一回事。

这是 UPDATE 的 FROM 子句：

UPDATE t
SET Col = u.Val
FROM   /* <-- Start of FROM clause */
   Table t WITH (NOLOCK)
       inner join
   Table2 u
       on
          t.ID = u.ID
/* End of FROM clause */
WHERE
    u.Colx = 19

并且，正如文档中所指出的，在这种情况下，WITH (NOLOCK) 将被忽略。至于为什么允许这样做（如果要忽略的话），一种猜测是这样的提示在“相同”查询的 SELECT 版本中是有效的，并且人们确实经常编写 SELECT（以确保它们瞄准正确的行/列），然后将 SELECT 子句替换为 UPDATE/SET 子句对，并且可以查询的其余部分保持不变。

---

根据 vgv8 的评论/“答案”进行更新：

您的示例更新仍然没有查看 UPDATE 语句的 FROM 子句

即使在另一个连接上打开 TABLOCKX()，以下工作正常:

UPDATE T  SET Txt= td.Txt
FROM TEST t inner join TESTDUP td  WITH (NOLOCK) on t.ID = td.ID
where t.ID = 1

Answer 3

创建并填充 2 个相同的表 Test 和 TestDUP [1] 后，在一个会话（SSMS 窗口）中，我执行

--2)
begin tran
Select Txt from TestDUP  with(TABLOCKX) 
WHERE ID=1
--rollback

阻止来自同一表上的另一个会话（SSMS 窗口）的 SELECT，例如：

 --3.1)
select * from TestDUP

但不注意

 --3.2)
select * from TestDUP WITH(NOLOCK)

3.1）被阻止但 3.2) 则不然。

但是，使用 TestDUP 中的 SELECT 更新另一个表 TEST

--4)WITH(NOLOCK) is not honored until completing
-- (commit/roollback)-ing transaction 2)
UPDATE Test  SET Txt=
(Select Txt from TESTDUP WITH(NOLOCK)  where ID=1)
  WHERE ID=1;

会被阻止，因为 UPDATE 语句的 FROM 子句中忽略另一个源表上的WITH(NOLOCK)。

更新：

--4.1)WITH(NOLOCK) is honored 
-- in FROM clause of UPDATE statement 
UPDATE Test  SET Txt= td.Txt
FROM TESTDUP td  WITH (NOLOCK)
where test.ID = 1 

--4.2) Note that without NOLOCK this script is blocked
-- until first transaction 2) completes (rollbacks or commits)
UPDATE Test  SET Txt= td.Txt
FROM TESTDUP td  WITH (NOLOCK)
where test.ID = 1  

所以，它现在是有道理的，但它与文档相矛盾，因为 UPDATE 语句的 FROM 子句中的 NOLOCK 不会被忽略，不是吗？

[ 1 ]
创建 2 个填充相同的表 Test 和 testDUP：

if object_id('Test') IS not NULL
drop table Test;

CREATE TABLE Test (
  ID int IDENTITY PRIMARY KEY,
  Txt nvarchar(max) NOT NULL
)
GO
-----------
INSERT INTO Test
SELECT REPLICATE(CONVERT(nvarchar(max), 
     CHAR(65+ABS(CHECKSUM(NEWID()))%26)),100000)
GO 10

--COPYING TEST into TESTDUP with creating of the latter
select *
into testDup
from TEST;