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