none
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

    • Edited by vgv8 Saturday, November 27, 2010 7:38 PM removed the rhetorical question which distracted attention from real questions
    Friday, November 26, 2010 7:26 AM

Answers

  • 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
    • Marked as answer by vgv8 Friday, December 10, 2010 4:04 AM
    Wednesday, December 01, 2010 6:23 PM

All replies

  • 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
    • Proposed as answer by Naomi NModerator Friday, November 26, 2010 5:16 PM
    • Unproposed as answer by vgv8 Saturday, November 27, 2010 7:41 PM
    Friday, November 26, 2010 8:10 AM
  • Wednesday, December 01, 2010 5:48 AM
    Moderator
  • 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
    • Marked as answer by vgv8 Friday, December 10, 2010 4:04 AM
    Wednesday, December 01, 2010 6:23 PM
  • 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

    • Edited by vgv8 Friday, December 10, 2010 11:33 PM prepended citations with ЭКЭ to avoid conflict with earlier citation
    Wednesday, December 01, 2010 8:20 PM
  • 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
    Wednesday, December 01, 2010 8:47 PM
  • 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? 

     

    Saturday, December 04, 2010 12:07 PM
  • 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
    Saturday, December 04, 2010 7:20 PM
  • 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, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 04, 2010 8:10 PM
  • --Blocks

    UPDATE testDup SET Txt= td.Txt

    FROM testDup td WITH (NOLOCK)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    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]

    • Edited by vgv8 Friday, December 10, 2010 11:35 PM
    Friday, December 10, 2010 4:25 AM
  • 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, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, December 10, 2010 10:53 PM
  •  

    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, esquel@sommarskog.se
    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)

    Saturday, December 11, 2010 1:13 AM
  • 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, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 11, 2010 2:16 PM