Implementing SQL Server queue table processing logic is something I keep meaning to blog about and finally I’ve got round to it thanks to my memory being jogged by StackOverflow questions I’ve recently participated in, including this one. The scenario is you queue up records in a database table, each representing a piece of work needing to be done. You then want to have processes that periodically poll this table to pick up the next item of work from the queue and process them.
What you want to avoid
- Multiple processes picking up the same queue item. You want each item in the queue to be processed once after all.
- Blocking. If multiple processes are polling the queue and they are blocking each other, then scalability will be limited.
Solution
DECLARE @NextId INTEGER
BEGIN TRANSACTION
-- Find next available item available
SELECT TOP 1 @NextId = ID
FROM QueueTable WITH (UPDLOCK, READPAST)
WHERE IsBeingProcessed = 0
ORDER BY ID ASC
-- If found, flag it to prevent being picked up again
IF (@NextId IS NOT NULL)
BEGIN
UPDATE QueueTable
SET IsBeingProcessed = 1
WHERE ID = @NextId
END
COMMIT TRANSACTION
-- Now return the queue item, if we have one
IF (@NextId IS NOT NULL)
SELECT * FROM QueueTable WHERE ID = @NextId
It’s all about the table hints
UPDLOCK
This grabs an update lock until the transaction is completed and prevents another process from picking up the same queue item.
READPAST
If a process encounters a row that is currently locked by another, this hint will make it skip over that locked row, and allow to move on to find the next available one.
This was a topic I investigated some time ago and spent some time working through to end up at this approach, finding this MSDN reference on table hints a valuable resource. I then found this article on MSSQLTips which demonstrates the same approach - if only I’d found that at the start, as it was the READPAST hint that was the one I wasn’t aware of initially!