Monday, 1 March 2010

Queue table processing in SQL Server

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!

4 comments:

  1. Thanks for this. Simple, but good summary.

    ReplyDelete
  2. This is great! I'm running into lots of blocking using an update top 1 statement. The readpast is exactly what I was looking for.

    ReplyDelete
  3. Thank you very much for sharing this. I had a strikingly similar case and using the hints you mentioned helped a lot.

    ReplyDelete
  4. Thanks for sharing, which helped me implementing a queue.
    I've altered the statement a bit, to improve performance, using the OUTPUT feature from the UPDATE statement, thereby eliminating the last SELECT statement:

    UPDATE QueueTable
    SET IsBeingProcessed = 1
    OUTPUT inserted.Id [,inserted.]
    WHERE ID = @NextId

    ReplyDelete