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


i confused bol phrase:

"readuncommitted , nolock cannot specified tables modified insert, update, or delete operations. sql server query optimizer ignores readuncommitted , nolock hints in clause apply target table of update or delete statement" [ 1 ]

for example, if write

--script 1)  update test set txt=(select txt test with(nolock) id=1)  id=1 

it run without errors (or warnings) , equivalent to

--script 2) set transaction isolation level serializable; begin tran declare @nvarm nvarchar(max);  select @nvarm=txt test id=1; --select @nvarm; update test  set txt=@nvarm  id=1; commit; 

which run without errors or warnings.
equivalent?

the table same in logically source table not target table have re-written 1) different source table (physical) table:

--script 3) select * testdup test;  go;  update test set txt=(select txt testdup with(nolock) id=1)      id=1 

why should nolock ignored on table?
or, if wrong, question then
how write update having "nolock hints in clause apply target table of update or delete statement" because in 1) , 2) physical table same logically source (in select) table , target (in update) table different ones.

how write update statement demonstrating with(nolock) ignored?
why should ignored @ all? ignored?
or, if wrong question, then
why syntax permit hint guaranteed ignored?

once again, either impossible (or it?) write such statement written in documentation or not understand sense of "ignores" (what sense ignore it? or have @ all?)...

update2:
answers show nolock not (updated) ignored in clause of update statement asserted bol docs [ 1 ].
well, essence of question:
can give me example (context) ignoring of nolock in clause of update statement have made sense?

[ 1 ]
table hints (transact-sql)
sql server 2008 r2
http://msdn.microsoft.com/en-us/library/ms187373.aspx

the clause of update or delete statement isn't evident in of examples. have clauses in subqueries, aren't same thing.

here's clause update:

update t set col = u.val   /* <-- start of clause */    table t (nolock)        inner join    table2 u        on           t.id = u.id /* end of clause */     u.colx = 19 

and, documentation calls out, with (nolock) ignored in case. why allowed if it's going ignored, 1 guess such hint valid in select version of "same" query, , people write selects (to ensure they're targetting correct rows/columns), , replace select clause update/set pair of clauses, , can leave rest of query unaltered.


updated based on comment/"answer" vgv8:

your example update still isn't looking @ the clause of update statement

the following works fine, tablockx() open on other connection:

update t  set txt= td.txt test t inner join testdup td  (nolock) on t.id = td.id t.id = 1 

Comments

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

java - Android recognize cell phone with keyboard or not? -

iphone - How would you achieve a LED Scrolling effect? -