Confused about UPDLOCK, HOLDLOCK

by

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.

More Related Contents:

  1. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  2. Comma separated results in SQL
  3. Only inserting a row if it’s not already there
  4. How to check if a column exists in a SQL Server table
  5. Understanding PIVOT function in T-SQL
  6. Using T-SQL, return nth delimited element from a string
  7. Get first day of week in SQL Server
  8. Using RegEx in SQL Server
  9. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  10. INSERT INTO vs SELECT INTO
  11. SQL Server 2008 Empty String vs. Space
  12. How do I use the CONCAT function in SQL Server 2008 R2?
  13. How to set a default value for an existing column
  14. TSQL: How to convert local time to UTC? (SQL Server 2008)
  15. SQL Server: combining multiple rows into one row
  16. How do I find a stored procedure containing ?
  17. SQL Recursive CTE: Finding objects linked by property
  18. SQL Server, the misleading XLOCK & optimizations
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. Convert decimal time to hours and minutes
  21. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  22. How do I move a table into a schema in T-SQL
  23. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  24. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  25. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  26. How to wait for 2 seconds?
  27. What are row, page and table locks? And when they are acquired?
  28. Generate MD5 hash string with T-SQL
  29. Select statement to find duplicates on certain fields
  30. Merge multiple rows into a single row

Leave a Comment