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

I am confused by the BOL phrase:

我对BOL短语感到困惑：

"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" [ 1 ]

“无法为插入，更新或删除操作修改的表指定READUNCOMMITTED和NOLOCK.SQL Server查询优化器忽略FROM子句中的READUNCOMMITTED和NOLOCK提示，这些提示适用于UPDATE或DELETE语句的目标表”[1]

For example, if I write

例如，如果我写

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

it is run without errors (or warnings) and is probably equivalent to

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

--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;

which is also run without errors or warnings.
Is it equivalent?

这也是没有错误或警告运行。它是等同的吗？

The table is the same but in FROM it is logically the source table not the target table I could have re-written 1) with a different source table as another (physical) table:

该表是相同的但在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

Why should NOLOCK be ignored on another table?
Or, if it is wrong, question then
How to write UPDATE having "NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement" because even in 1) and 2) the physical table is the same but logically the source (in SELECT) table and target (in UPDATE) table are different ones.

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

How to write an UPDATE statement demonstrating that WITH(NOLOCK) is ignored?
Why should it be ignored at all? Is it ignored?
Or, if it is a wrong question, then
Why does syntax permit the hint which is guaranteed to be ignored?

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

Once again, either it is impossible (or is it?) to write such a statement as written in documentation or I do not understand the sense of "ignores" (What is the sense to ignore it? or to have it at all?)...

再一次，或者不可能（或者是吗？）编写文档中写的这样的声明，或者我不理解“忽略”的意义（忽略它的意义是什么？或者根本没有意义？） ...

UPDATE2:
The answers show that NOLOCK is NOT (updated) ignored in the FROM clause of UPDATE statement what is asserted by BOL docs [ 1 ].
Well, the essence of this question:
Can you give me any example (context) where ignoring of NOLOCK in FROM clause of UPDATE statement would have made sense?

UPDATE2：答案显示NOLOCK在UPDATE语句的FROM子句中未被（更新）忽略，BOL docs [1]声明了什么。好吧，这个问题的实质：你能给我任何一个例子（上下文），在UPDATE语句的FROM子句中忽略NOLOCK会有意义吗？

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

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

3 个解决方案

#1

The FROM clause of an UPDATE or DELETE statement isn't evident in any of your examples. You have from clauses in subqueries, but those aren't the same thing.

在任何示例中，UPDATE或DELETE语句的FROM子句都不明显。你有子查询中的子句，但那些不是同一个东西。

Here's a FROM clause for an UPDATE:

这是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

And, as the documentation calls out, the WITH (NOLOCK) will be ignored in this case. As to why this is allowed if it's going to be ignored, one guess would be that such a hint would be valid in the SELECT version of the "same" query, and people do frequently write SELECTs (to ensure they're targetting the correct rows/columns), and then replace the SELECT clause with a UPDATE/SET pair of clauses, and can leave the rest of the query unaltered.

并且，在文档调用时，在这种情况下将忽略WITH（NOLOCK）。至于为什么允许这样做会被忽略，一个猜测是这样的提示在“相同”查询的SELECT版本中是有效的，人们经常写SELECT（以确保他们的目标是正确的）行/列），然后用UPDATE / SET子句对替换SELECT子句，并保持查询的其余部分不变。

---

Updated based on comment/"answer" from vgv8:

根据vgv8中的评论/“回答”更新：

Your example update still isn't looking at the FROM clause of the UPDATE statement

您的示例更新仍未查看UPDATE语句的FROM子句

The following works fine, even with the TABLOCKX() open on the other connection:

以下工作正常，即使在另一个连接上打开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

#2

No guessing required.

不需要猜测。

Sybase and MS SQL server use an internal, automatic 2PL resource locking, but with full compliance with the ISO/IEC/ANSI SQL Standard. The syntax gets silly when you try to understand all possible combinations, because some clauses are not relevant for every command.

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

What the manual is trying to say, but does not say in simple English, is:

手册试图说的是，但不是简单的英文，是：

• for whatever outer operation, or a single query within a transaction, you are performing, you can SET ISOLATION LEVEL
• 对于任何外部操作或事务中的单个查询，您正在执行，您可以设置ISOLATION LEVEL
• that can be specified using UNCOMMITTED, NOLOCK, HOLDLOCKsyntax as well
• 也可以使用UNCOMMITTED，NOLOCK，HOLDLOCKsyntax指定
• where you have one IL in the outer query, or a single query within a transaction, but want to use a different IL for the inner query, that can be done (use different modulators on the inner query)
• 如果在外部查询中有一个IL，或者在事务中有一个查询，但是想要为内部查询使用不同的IL，则可以这样做（在内部查询上使用不同的调制器）
• so you could have a transaction executing at IL3, and have one SELECT within it executing at IL0 or IL1
• 所以你可以在IL3上执行一个事务，并在其中有一个SELECT在IL0或IL1上执行

Separately:

分别：

• regardless of what you think you are doing, or want to do, since the locking is automatic, and ISOLATION LEVEL 3is required for UPDATES and DELETES, wherein READ UNCOMMITTED and NOLOCK do not apply, and cannot be used, if you have used them the server will ignore them
• 无论您认为自己在做什么，或想做什么，因为锁定是自动的，并且UPDATES和DELETES需要ISOLATION LEVEL 3，其中READ UNCOMMITTED和NOLOCK不适用，并且如果您使用了它们则无法使用服务器将忽略它们

#3

Having created and filled 2 identical tables Test and TestDUP [ 1 ], in one session (windows of SSMS) I execute

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

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

which blocks SELECT from another session (SSMS window) on the same table, for example:

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

 --3.1)
select * from TestDUP

but not

但不是

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

Note that 3.1) is blocked but 3.2) is not.

请注意，3.1）被阻止，但3.2）不被阻止。

Though, updating on another table TEST using SELECT from TestDUP

但是，使用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;

is blocked because WITH(NOLOCK), on another source table, is ignored in FROM clause of UPDATE statement.

因为在另一个源表上的WITH（NOLOCK）在UPDATE语句的FROM子句中被忽略，因此被阻止。

Update:

更新：

--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  

So, it now makes sense but it contradicts to documentation since NOLOCK in FROM clause of UPDATE statement is NOT ignored, does not it?

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

[ 1 ]
Create 2 identically filled tables Test and testDUP:

[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;