Why NOLOCK is ignored "in the FROM clause that apply to the target table of an UPDATE or DELETE statement"?

Question

I am confused by the BOL phrase:

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

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:

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

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?

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?

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

score 4 · Answer 1 · answered Jan 17 '11 at 23:10

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.

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
that can be specified using UNCOMMITTED, NOLOCK, HOLDLOCKsyntax as well
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)
so you could have a transaction executing at IL3, and have one SELECT within it executing at IL0 or 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

if only you answered this way more often... +1 – gbn Jan 22 '11 at 12:33 — gbn, Jan 22 '11 at 12:33

Damien_The_Unbeliever · Accepted Answer · 2010-12-01T16:04:54.150

2

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.

Here's a FROM clause for an UPDATE:

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.

Updated based on comment/"answer" from vgv8:

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

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

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

edited Dec 01 '10 at 16:04

answered Dec 01 '10 at 08:36

Damien_The_Unbeliever

234,701
27
340
448

Will NOLOCK in "Table2 u WITH(NOLOCK)" be ignored? – Gennady Vanin Геннадий Ванин Dec 01 '10 at 12:30
@vgv8 - I believe that the NOLOCK hint will be respected on any tables other than that table which is subject to update (hence the reference in the documentation to the target table). – Damien_The_Unbeliever Dec 01 '10 at 13:02
Yeaah, this is common sense hope but the fact is different. See my answer http://stackoverflow.com/questions/4322368/why-nolock-is-ignored-in-the-from-clause-that-apply-to-the-target-table-of-an-up/4324639#4324639 – Gennady Vanin Геннадий Ванин Dec 01 '10 at 13:45
@vgv8 - I've edited, and again point out that your samples aren't dealing with an UPDATE statement's FROM clause. – Damien_The_Unbeliever Dec 01 '10 at 16:05

Gennady Vanin Геннадий Ванин · Answer 3 · 2010-12-01T17:44:35.407

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

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

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

~~Though, updating on another table TEST using SELECT from TestDUP~~

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

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?

[ 1 ]
Create 2 identically filled tables Test and 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;

Why NOLOCK is ignored "in the FROM clause that apply to the target table of an UPDATE or DELETE statement"?

3 Answers3