What is the meaning of UPDATE and DELETE WITH(NOLOCK) statements?

Question

[1] tells: 

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

How is it possible to ignore something which cannot be specified? 
Why to continue having it? 
Why were they permitted/introduced for DELETE and UPDATE? 

What did UPDATE (DELETE) WITH(NOLOCK) mean in earlier versions of SQL Server when were they introduced?

[1] Table Hints (Transact-SQL)
SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/ms187373.aspx

Update: 
I did not expect that the only question which was rhetorical would be answered.
Let's consider I did not ask it

Answer 1

The article is phrased a bit poorly.  "Cannot be specified" simply means the hints will be ignored.   Neither hint applies to a statement that modifies data, as these require locking to function properly.    They won't generate an exception if you specify them within the statement, but they won't have any effect either.

 

---

Michael Asher

Answer 2

You may consider posting the issue at:

SQL Server Documentation

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

The key is in the fact whether or not the table (that the NOLOCK hint acts on) is update or not.

For example:

UPDATE orders
SET no_of_orderlines = (
 SELECT COUNT(*)
 FROM orderlines WITH (NOLOCK)
 WHERE orderlines.order_id = orders.order_id
)

Here, the NOLOCK hint can (and will) be honored. However, in this example

UPDATE orders
SET no_of_orderlines = COUNT(*)
FROM orders   O WITH (NOLOCK)
JOIN orderlines OL ON OL.order_id = O.order_id

the NOLOCK hint (or READUNCOMMITTED transaction isolation mode) would be ignored for table "orders", because that is the table that is being updated.

---

Gert-Jan

Answer 4

The key is in the fact whether or not the table (that the NOLOCK hint acts on) is update or not.

For example:

UPDATE orders

SET no_of_orderlines = (

 SELECT COUNT(*)

 FROM orderlines WITH (NOLOCK)

 WHERE orderlines.order_id = orders.order_id

)

Here, the NOLOCK hint can (and will) be honored. However, in this example

UPDATE orders

SET no_of_orderlines = COUNT(*)

FROM orders O WITH (NOLOCK)

JOIN orderlines OL ON OL.order_id = O.order_id

the NOLOCK hint (or READUNCOMMITTED transaction isolation mode) would be ignored for table "orders", because that is the table that is being updated.

---

Gert-Jan

Hi, Gert-Jan,
are you sure? 

Because my investigations showed just opposite results and conclusions to each of yours:
[R1] removed
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

[R2]
WITH(NOLOCK) in UPDATE(INSERT) of value SELECTed from a source table different from target (being updated) is ignored
https://connect.microsoft.com/SQLServer/feedback/details/626170/with-nolock-in-update-insert-of-value-selected-from-a-source-table-different-from-target-being-updated-is-ignored

[R3]
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

Answer 5

The information you quote are bug reports. I was explaining what BOL is trying to say, and what the designed behavior is.

Please note that BOL does NOT assert that "WITH (NOLOCK) in FROM clause of UPDATE statement is ignored". Instead, it asserts that "SQL Server ... ignores the ... NOLOCK hints ... that apply to the target table of an UPDATE or DELETE statement" (emphasis mine).

Just to doublecheck, I quickly did an example. I ran a query in the format of the first example gave, so an update on table 1 with a select from table 2 with NOLOCK hint on this table 2.

When I ran this without any hint, then during the transaction this was the response of sp_lock:

spid  dbid  ObjId    IndId Type Resource             Mode   Status 
------ ------ ----------- ------ ---- -------------------------------- -------- ------ 
12   5   0      0   DB                  S    GRANT
12   5   0      0   FIL 0                X    GRANT
51   4   0      0   DB                  S    GRANT
52   4   0      0   DB                  S    GRANT
53   5   0      0   DB                  S    GRANT
54   5   0      0   DB                  S    GRANT
55   5   0      0   DB                  S    GRANT
55   2   0      0   DB  [ENCRYPTION_SCAN]        S    GRANT
55   1   1131151075 0   TAB                  IS    GRANT
56   5   1573580644 0   TAB                  X    GRANT
56   5   94623380  1   PAG 1:63625             S    GRANT
56   5   94623380  0   TAB                  IS    GRANT
56   5   0      0   DB                  S    GRANT
57   5   0      0   DB                  S    GRANT

When I ran this with NOLOCK hint, then during the transaction this was the response of sp_lock:

spid  dbid  ObjId    IndId Type Resource             Mode   Status 
------ ------ ----------- ------ ---- -------------------------------- -------- ------ 
51   4   0      0   DB                  S    GRANT
52   4   0      0   DB                  S    GRANT
53   5   0      0   DB                  S    GRANT
54   5   0      0   DB                  S    GRANT
55   2   0      0   DB  [ENCRYPTION_SCAN]        S    GRANT
55   1   1131151075 0   TAB                  IS    GRANT
55   5   0      0   DB                  S    GRANT
56   5   1573580644 0   TAB                  X    GRANT
56   5   94623380  0   TAB                  Sch-S  GRANT
56   5   0      0   DB                  S    GRANT
57   5   0      0   DB                  S    GRANT

As you can see, the query with NOLOCK does not acquire PAGE (or KEY) locks for the object that is only read. Also, there is no Intend Shared lock on TAB level. The only lock that is acquired is a Schema Shared lock.

So maybe under certain specific conditions, there are bugs, so maybe the reports are correct. But that is not the standard defined behavior. The standard behavior is what BOL states.

---

Gert-Jan

Answer 6

Gert-Jan,
thanks.

First, I do not understand the import of join for the cases under consideration.
Why iare they needed????
So, I ran without join as it is specified reproducibly in mentioned MS Connect bug reports?

1)
I do not reproduce your results,
that is for your first example (but without join) 

begin tran;
UPDATE Test 
 SET Txt=
  (Select Txt from TESTDUP 
  --WITH(NOLOCK)--or uncommented 
  where ID=1)
 WHERE ID=1;
--rollback

there are no differences with or without NOLOCK
in sp_lock results, see below

1a)
You wrote:
"As you can see, the query with NOLOCK does not acquire PAGE (or KEY) locks for the object that is only read"
 
Though I do not observe any differences but what PAGE (or KEY) could have possibly clarify.
They are granularity locks.

2)
When I run this your first example (without join),
sp_lock shows locks only on updated table (in your results it is objId = 94632280),
In my queuery table TEST  with ObjID = 194099732,
but sp_lock does not show any locks on source table
from which the values are SELECT-ed), in my query table TestDUP

One can see the name of table
select @@spid
select object_name(194099732)

 

 

 

 

RESULTS of SP_LOCK

without NOLOCK

spid dbid ObjId  IndId Type Resource   Mode  Status
------ ------ ----------- ------ ---- ------------------- -------- ------
51  33  0   0  DB     S GRANT
52  5  0   0 DB     S GRANT
53  33  194099732 1 KEY (8194443284a0)   X GRANT
53  33  194099732 1 PAG 1:188    IX GRANT
53  33  0  0 DB     S GRANT
53  33  194099732 0 TAB     IX GRANT
55  5  0  0 DB     S GRANT
56  33  0  0 DB     S GRANT
56  1  1131151075 0 TAB     IS GRANT
58  5  0  0 DB     S GRANT

==================================================
WITH(NOLOCK)

spid dbid ObjId IndId Type Resource    Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51 33 0  0 DB     S GRANT
52 5 0  0 DB     S GRANT
53 33 0  0 DB     S GRANT
53 33 194099732 0 TAB     IX GRANT
53 33 194099732 1 KEY (8194443284a0)   X GRANT
53 33 194099732 1 PAG 1:188    IX GRANT
56 1 1131151075 0 TAB     IS GRANT
56 33 0  0 DB     S GRANT

3)
It should be noted that, though I raised this issue,
but the first example does not contain FROM clause and I do not see where is this case is described in BOL

"But that is not the standard defined behavior. The standard behavior is what BOL states"

So, where is it stated in BOL? 

 

Answer 7

Again you refer to a bug report. The whole point of a bug report is to show where there are faults in the software; places where the software does not behave as specified and documented. The primary source of documentation for SQL Server is Books Online.

I don't have 2008R2 installed. If I run the repro-script on 2008, then I do not get to see the bug. In other words, on that version of SQL Server the "SELECT ... FROM x WITH (TABLOCKX)" does not block the statement "UPDATE y ... (SELECT .. FROM x WITH (NOLOCK))". This suggests that the bug is new for SQL Server 2008 R2.

So I don't understand what you are asking, or what you are trying to achieve.

---

Gert-Jan

Answer 8

I don't have 2008R2 installed. If I run the repro-script on 2008, then I do not get to see the bug. In other words, on that version of SQL Server the "SELECT ... FROM x WITH (TABLOCKX)" does not block the statement "UPDATE y ... (SELECT .. FROM x WITH (NOLOCK))". This suggests that the bug is new for SQL Server 2008 R2.

I tried the repro on SQL 2008 R2, and the UPDATE statement completed. I suspect that the bug submitter got lost in his own repro.

Here is the repro for clarity:

Window 1:

CREATE TABLE Test (
ID int IDENTITY PRIMARY KEY,
Txt nvarchar(max) NOT NULL
)
GO
-----------
INSERT INTO Test
SELECT REPLICATE(CONVERT(nvarchar(max),
CHAR(65+ABS(CHECKSUM(NEWID()))%26)),100000)
GO 10

--COPYING TEST into TESTDUP with creating of the latter
select *
into testDup
from Test;

begin tran
Select Txt from testDup with(TABLOCKX)
WHERE ID=1

Window 2:

-- Does not block
UPDATE Test SET Txt= td.Txt
FROM testDup td WITH (NOLOCK)
where Test.ID = 1

--Blocks
UPDATE testDup SET Txt= td.Txt
FROM testDup td WITH (NOLOCK)

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 9

--Blocks

UPDATE testDup SET Txt= td.Txt

FROM testDup td WITH (NOLOCK)

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

You really did not reproduce the last statement correctly because comments did tell without "WITH(NOLOCK)", i.e. it should have been

--Blocks

UPDATE testDup SET Txt= td.Txt

FROM testDup td --WITH (NOLOCK)

Though, report [R1] is dupe of [R2] and [R2] is not related to FROM clause [1]. Also,  the problem is that the phrase [1]

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

is ambiguous:
- if source table is different from target then why NOLOCK is ignored?
- if the source and target table are the same  then the rows are not being locked

The problem is to interpret this phrase from [1]

Answer 10

You really did not reproduce the last statement correctly because comments did tell without "WITH(NOLOCK)", i.e. it should have been

I did purposely changed the repro. As I said, in my original post, I think the submitter got lost in his repros.

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

And the statement that blocked was:

UPDATE testDup SET Txt= td.Txt
FROM testDup td WITH (NOLOCK) 

That is, I specify NOLOCK, yet I get blocked, because I try to use NOLOCK with the table I update.

is ambiguous: - if source table is different from target then why NOLOCK is ignored?

It isn't, as shown by the example:

UPDATE Test SET Txt= td.Txt
FROM testDup td WITH (NOLOCK)
where Test.ID = 1

Here I use NOLOCK with a table that is not the target table, and NOLOCK is honored.

- if the source and target table are the same  then the rows are not being locked

No, this is wrong. If you want to update a row, then you need to look that row.

The problem is to interpret this phrase from [1]

I don't see any problem with that excerpt from Books Online.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 11

 

I did purposely changed the repro. As I said, in my original post, I think the submitter got lost in his repros.

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

And the statement that blocked was:

UPDATE testDup SET Txt= td.Txt

 

FROM testDup td WITH (NOLOCK)

That is, I specify NOLOCK, yet I get blocked, because I try to use NOLOCK with the table I update.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

If it is ignored then why

--without any parallel execution
UPDATE testDup WITH (XLOCK) SET Txt= td.Txt
FROM testDup td WITH (NOLOCK)

gives the error:
"Conflicting locking hints are specified for table "testDup". This may be caused by a conflicting hint specified for a view"?
The same statement witout WITH(NOLOCK) succeds.

So, WITH(NOLOCK):
1)
is not always ignored on target (updated) table
2)
Why should it be if self-updated table is not being self-blocked:

UPDATE testDup WITH (XLOCK) --with or without XLOCK 
SET Txt= td.Txt
FROM testDup td

Formally speaking, in your example NOLOCK is specified on source table (albeit the same as target table). WITH(NOLOCK) on target table (being updated) is prohibited:

UPDATE testDup WITH (NOLOCK) SET Txt= td.Txt
FROM testDup td

producing the error:
"The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements"

 

2a)
BOL describe a feature which is inconsistently realized.
It is not clear which behavior is really indended to be correct one (the actual one, the described one or the third one)

Answer 12

If it is ignored then why

--without any parallel execution
UPDATE testDupWITH (XLOCK)SET Txt= td.Txt
FROM testDup tdWITH (NOLOCK)

gives the error:
"Conflicting locking hints are specified for table "testDup". This may be caused by a conflicting hint specified for a view"?

I guess the checks for conflicting locks happens at an earlier state, when the query is compiled. Then at run-time, the NOLOCK hint is ignored.

BOL describe a feature which is inconsistently realized.
It is not clear which behavior is really indended to be correct one (the actual one, the described one or the third one)

Obviously someone messed up. The correct behaviour would have been that using NOLOCK for the target table anywhere in the query would have yielded an error. But they failed to check in the compilation module, and then the error was ignored silently ignored at run-time.

They could fix it, but since it would be a breaking change, Microsoft may prefer to retain the current confusing, but benign, error.

But as far as documentation goes, I think the writers have done a good job and are describing the behaviour correctly.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)