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 http://stackoverflow.com/questions/3636950/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.