Confused about UPDLOCK HOLDLOCK

Question

While researching the use of Table Hints  I came across these two questions    Which lock hints should I use  T-SQL   What effect does HOLDLOCK have on UPDLOCK    Answers to both questions say that when using  UPDLOCK  HOLDLOCK   other processes will not be able to read data on that table  but I didn t see this   To test  I created a table and started up two SSMS windows   From the first window  I ran a transaction that selected from the table using various table hints   While the transaction was running  from the second window I ran various statements to see which would be blocked   The test table   CREATE TABLE  dbo   Test        ID   int  IDENTITY 1 1  NOT NULL       Value   nvarchar  50  NULL   CONSTRAINT  PK Test  PRIMARY KEY CLUSTERED         ID  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY    From SSMS Window 1   BEGIN TRANSACTION  SELECT   FROM dbo Test WITH  UPDLOCK  HOLDLOCK  WAITFOR DELAY  00 00 10   COMMIT TRANSACTION   From SSMS Window 2  ran one of the following    SELECT   FROM dbo Test INSERT dbo Test Value  VALUES   bar   UPDATE dbo Test SET Value    baz  WHERE Value    bar  DELETE dbo Test WHERE Value   baz    Effect of different table hints on statements run in Window 2               UPDLOCK         HOLDLOCK      UPDLOCK  HOLDLOCK      TABLOCKX  --------------------------------------------------------------------------- SELECT    not blocked      not blocked       not blocked         blocked INSERT    not blocked        blocked           blocked           blocked UPDATE      blocked          blocked           blocked           blocked DELETE      blocked          blocked           blocked           blocked   Did I misunderstand the answers given in those questions  or make a mistake in my testing   If not  why would you use  UPDLOCK  HOLDLOCK  vs   HOLDLOCK  alone     Further explanation of what I am trying to accomplish   I would like to select rows from a table and prevent the data in that table from being modified while I am processing it   I am not modifying that data  and would like to allow reads to occur   This answer clearly says that  UPDLOCK  HOLDLOCK  will block reads  not what I want    The comments on this answer imply that it is HOLDLOCK that prevents reads   To try and better understand the effects of the table hints and see if UPDLOCK alone would do what I wanted  I did the above experiment and got results that contradict those answers   Currently  I believe that  HOLDLOCK  is what I should use  but I am concerned that I may have made a mistake or overlooked something that will come back to bite me in the future  hence this question

User · Answer

UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement. The future update might be the very next statement in the transaction.

Other sessions can still see the data. They just cannot obtain locks that are incompatiable with the UPDLOCK and/or HOLDLOCK.

You use UPDLOCK when you wan to keep other sessions from changing the rows you have locked. It restricts their ability to update or delete locked rows.

You use HOLDLOCK when you want to keep other sessions from changing any of the data you are looking at. It restricts their ability to insert, update, or delete the rows you have locked. This allows you to run the query again and see the same results.

User · Answer

Why would UPDLOCK block selects  The Lock Compatibility Matrix clearly shows N for the S U and U S contention  as in No Conflict    As for the HOLDLOCK hint the documentation states      HOLDLOCK   Is equivalent to SERIALIZABLE  For more information  see SERIALIZABLE   later in this topic                SERIALIZABLE      The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level      and the Transaction Isolation Level topic explains what SERIALIZABLE means      No other transactions can modify data that has been read by the   current transaction until the current transaction completes       Other transactions cannot insert new rows with key values that would   fall in the range of keys read by any statements in the current   transaction until the current transaction completes    Therefore the behavior you see is perfectly explained by the product documentation    UPDLOCK does not block concurrent SELECT nor INSERT  but blocks any UPDATE or DELETE of the rows selected by T1 HOLDLOCK means SERALIZABLE and therefore allows SELECTS  but blocks UPDATE and DELETES of the rows selected by T1  as well as any INSERT in the range selected by T1  which is the entire table  therefore any insert    UPDLOCK  HOLDLOCK   your experiment does not show what would block in addition to the case above  namely another transaction with UPDLOCK in T2  SELECT   FROM dbo Test WITH  UPDLOCK  WHERE       TABLOCKX no need for explanations   The real question is what are you trying to achieve  Playing with lock hints w o an absolute complete 110  understanding of the locking semantics is begging for trouble     After OP edit      I would like to select rows from a table and prevent the data in that   table from being modified while I am processing it    The you should use one of the higher transaction isolation levels  REPEATABLE READ will prevent the data you read from being modified  SERIALIZABLE will prevent the data you read from being modified and new data from being inserted  Using transaction isolation levels is the right approach  as opposed to using query hints  Kendra Little has a nice poster exlaining the isolation levels

[sql-server] Confused about UPDLOCK, HOLDLOCK

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to concurrency

Examples related to locking