Confused about UPDLOCK, HOLDLOCK

sql-server tsql sql-server-2008 concurrency locking

129,823

Solution 1

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.

Solution 2

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.

129,823

Author by

Jeff Ogata

Everyone knows that debugging is twice as hard as writing a program in the first place. So if you're as clever as you can be when you write it, how will you ever debug it? -- Brian Kernighan, "The Elements of Programming Style", 2nd edition, chapter 2

Updated on October 23, 2020

Comments

Jeff Ogata over 3 years
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.
Jeff Ogata over 12 years

Thank you, but I don't think you've really answered my question: were the answers to those questions wrong in stating that (UPDLOCK,HOLDLOCK) block reads, and is there a reason to use (UPDLOCK,HOLDLOCK) instead of just (HOLDLOCK)?
Jeff Ogata over 12 years

+1, and thank you for the detailed response. I will update my question to add the detail of what my goal is.
Scott Bruns over 12 years

My second statement answers your question, they are wrong. Other sessions can still read the data.
Scott Bruns over 12 years

Updlock, Holdlock is not the same as holdlock. Updlock, holdlock locks the rows for update and serializes your transaction. Holdlock by itself just serializes your transaction. It does not lock the selected rows for further access.
Yiping over 7 years

"UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement." I love this, since XLOCK may not be working sometime
Steve about 7 years

@Remus Rusanu could you please elaborate for why the right approach is using isolation levels as opposed to using query hints? I have a procedure where i only need to lock two tables from being modified and i am using TABLOCK, HOLDLOCK, should i really change to isolation level and lock alls tables in my transaction?
niico about 7 years

I could like explanation for TABLOCKX :)
Bacon Bits almost 7 years

Note: The link for blog entry for Kendra Little returns a 404. I can find no entry dated Feb 2, 2011, as the link suggests.