HOLDLOCK, TABLOCKX not working as expected

Question

HOLDLOCK, TABLOCKX not working as expected

daytonbrown2

Say Hey Kid

Points: 679
More actions
March 14, 2024 at 1:30 am

#4394917
Hi All,
I'm certain this is normal behavior, I'm just not getting it.
Run this code to create table
```
CREATE TABLE test (id INT IDENTITY(1, 1), someval VARCHAR(20));

INSERT INTO dbo.test (someval)
VALUES
('row1' -- someval - varchar(20)
);
```
then this code in a new window
```
BEGIN TRANSACTION;

SELECT TOP (1)
*
FROM dbo.test AS t WITH (TABLOCKX, HOLDLOCK);

WAITFOR DELAY '00:01';

SELECT TOP (1)
*
FROM dbo.test AS t WITH (TABLOCKX, HOLDLOCK);

COMMIT TRANSACTION
```
From what I expect, if I then run a select (from test) in a new session, I should be blocked until the transaction commits. Correct? However, the select works fine. DB is set to read committed. Thanks much for the help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

I just tried this, and for me the blocking occurs, exactly as you expected.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

you said " DB is set to read committed" - does this mean your db has Read Committed Snapshot Isolation on? if so this is likely why.

daytonbrown2 Say Hey Kid Points: 679 More actions · Answer 3

So very strange. Trying those exact queries again today, and working exactly as expected. Even tried setting RCSI on and off, no change. Thanks for checking. Much appreciated.

Interestingly enough, I used that technique for queueing records that can have multiple applications processing them. Thinking that didn't work forced me to find something new. Now I'm just using update with output inserted. (or delete with output) No additional locking needed and much safer/cleaner.