user7292 Published in September 20, 2018, 12:44 pm
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
)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:
SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'
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.