WITH(NOLOCK) in UPDATE(INSERT) of value SELECTed from a source table different from target (being updated) is ignored - by Gennady Vanin (Геннадий Ванин)

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 626170 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/1/2010 5:28:12 AM
Access Restriction Public


There is no sense in ignoring NOLOCK for source table which is different from target one

There is no sense to ignore WITH(NOLOCK) in UPDATE statement at all as it follows from related bugs [1] and [2]

Related bugs:  
BOL incorrectly asserts that WITH(NOLOCK) in FROM clause of UPDATE statement is ignored

BOL(documentation) gives incorrect syntax of WITH() meta-description in relation to NOLOCK 
Sign in to post a comment.
Posted by Microsoft on 11/30/2011 at 6:15 PM
Hello there, I tried the following

Window - 1
create table t1 (c1 int, c2 int)

create table t2 (c1 int, c2 int)

declare @i int
set @i = 0

while (@i < 100)
    insert into t1 values (@i, @i+100)
    insert into t2 values (@i, @i)
    set @i = @i + 1

- Window 2
begin tran
select sum(c1) from t2 with (TablockX)

here SP_LOCK shows that there is X lock on T2
54    6    261575970    0    TAB                                 X    GRANT

- window 1
-- this does not block
update t1 set c1 = (select SUM (c1) from t2 with (NOLOCK))

-- this blocks
update t1 set c1 = (select SUM (c1) from t2)

What I am seeing is that the lock hint is correctly applied to the source table. So I am closing this as no repro. Please feel free to re-activate if you see different behaviour

Posted by Microsoft on 12/6/2010 at 10:39 AM
Thank you for reporting this discrepany in the syntax. We will investigate and correct the topic as appropriate in a future update to Books Online.
Kind Regards,
Gail Erickson
SQL Server Documentation Team