Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


1
0
Sign in
to vote
Type: Bug
ID: 626170
Opened: 12/1/2010 5:28:12 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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:
[1]
BOL incorrectly asserts that WITH(NOLOCK) in FROM clause of UPDATE statement is ignored
https://connect.microsoft.com/SQLServer/feedback/details/626425/bol-incorrectly-asserts-that-with-nolock-in-from-clause-of-update-statement-is-ignored

[2]
BOL(documentation) gives incorrect syntax of WITH() meta-description in relation to NOLOCK
https://connect.microsoft.com/SQLServer/feedback/details/626176/bol-documentation-gives-incorrect-syntax-of-with-meta-description-in-relation-to-nolock
Details
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)
go


create table t2 (c1 int, c2 int)
go

declare @i int
set @i = 0

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

- 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

Thanks
Sunil
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
Sign in to post a workaround.