Search code examples
sqlsql-servert-sqldatabase-designquery-hints

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


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


Solution

  • 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