Select top 1 with UPDLOCK and READPAST sets exclusive lock on the entire table

Question

I'm using the UPDLOCK and READPAST sql hints in a stored procedure in order to implement a sort of a table queue (I say a sort because I select top 1500 instead of a top 1, and I don't delete the rows after I select them. For more details, see question Return unlocked rows in a "select top n" query).

I made some tests with a simple small table, and everything seems to work great - the second call of the SP doesn't wait for the first call to end, simply skips the rows locked by the first call, and returns the next ones. On my real table, although, it only works sometimes... other times, the second call hangs and waits for the first one to end. This is because the first call locks the entire table, not just some rows.

These are the SPs for the test table and the real table:

Real table:

declare @temp as table (ID int primary key, timestamp datetime)

BEGIN TRANSACTION

insert into @temp
SELECT TOP 1 ID, getdate()
FROM subscription WITH (UPDLOCK, READPAST)
WHERE IsBeingProcessed = 0 

waitfor delay '00:00:10'

UPDATE subscription 
SET IsBeingProcessed = 1
from subscription
inner join @temp t on subscription.id = t.id

COMMIT TRANSACTION

select * from @temp t
inner join subscription s on s.id = t.id

Test table:

declare @temp as table (ID int primary key)

BEGIN TRANSACTION

insert into @temp(id)
select top 1 id
from test WITH (UPDLOCK, READPAST)
where msg like 'test'

waitfor delay '00:00:10'

UPDATE test 
SET timestamp = getdate()
from test
inner join  @temp t1 on test.id = t1.id 

COMMIT TRANSACTION

select * from test t
inner join @temp t1 on t.id = t1.id 

Running sp_lock I see that an exclusive table lock is held by the first SP on my entire "real" table, while the second SP waits with an intent exclusive lock. For my "test" table, I have an update lock on an index and row id, two intent update lock on two pages, and an intent exclusive lock on the entire table.

Do do have any ideea what might cause the entire table lock of the "real table"?

Maybe some clustered indexes that I'm having on that table, maybe some indexes that I'm missing? I don't know.

The example I've posted is very simple - it has a "top 1" and no "order by". My real select will have an "order by id" and "top 1500", possibly "top 3000". This might make the lock problem even worst.

Any ideea is very much welcomed! Thank you.

Answer 1

I think your problem is indexing. Make sure you have the proper indexes on your table and make sure your queries always use that index. Having no, or the incorrect index can make SQL Server take a page lock or a table lock, which invalidates the whole model.

Answer 2

Here is how you use tables as queues: SQL Server Process Queue Race Condition

Summary: you're missing ROWLOCK (which will use more resources in this case)