3

In SQL Server Databases, the WITH (NOLOCK) query hint is ignored on target tables in UPDATE and DELETE statements. However, does this also apply to tables included in a join for an update or delete? For instance, is:

update t
set value1 = 123
FROM mytable t
inner join othertable o with (nolock) on t.id = o.id

equivalent to:

update t
set value1 = 123
FROM mytable t
inner join othertable o on t.id = o.id
1
  • Why do you want to use NOLOCK anyway? Side note: you put the hint in the wrong place, it needs to be immediately after the table reference. Nov 4, 2023 at 21:10

1 Answer 1

6

No it is not ignored. This is easy to test.

Setup

DROP TABLE IF EXISTS mytable, othertable

CREATE TABLE mytable(id INT, value1 INT)
CREATE TABLE othertable(id INT, dirtyvalue INT)

INSERT mytable VALUES (1, NULL)
INSERT othertable  VALUES (1, 100)

Connection 1

BEGIN TRAN

UPDATE othertable SET dirtyvalue = 999

WAITFOR DELAY '00:02:00' /*To give time to run connection 2*/

ROLLBACK

Connection 2 (whilst above still running)

update t
set value1 = o.dirtyvalue
output inserted.value1
FROM mytable t
inner join othertable o  with (nolock) on t.id = o.id

The uncommitted 999 value is used in the update rather than 100 as would have happened without the hint if running at RCSI or being blocked if running at RC locking isolation level.

Note the 999 value is never actually committed to othertable so the use of NOLOCK here means that the state persisted in mytable never logically existed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.