One of the primary issues with implementing a high performance multi-threaded queue in SQL server is retrieving and processing tasks from a table with low rate of locking contention.
Try to remove an item out of a queue with some sort of ordering. In this case we’ll use the example of priority. In order to retrieve the item from the queue, a SELECT with an order by and top clause must be specified followed by a potential update to indicate our item in the queue is now processing.
What if between the SELECT and UPDATE statement, some other process were to try get the same task? A natural response to this would be, add a transaction, or add a check to the UPDATE statement to ensure the item hasn’t already been selected. While both of these are potential solutions, they are not ideal and both introduce potential locking contention. SQL Server has a query hint that allows for us to bypass this issue. The query hint is READPAST. The READPAST hint effectively ignores any rows which are currently locked. Combining this with a lock of some sort within a transaction will allow a much more efficient way to retrieve the items from the queue.
1 BEGIN TRANSACTION 2 3 DECLARE @QueueID INT 4 5 SELECT TOP 1 @QueueID=@QueueID FROM [Queue] AS Q WITH (READPAST, UPDLOCK) 6 WHERE Processing = 0 7 ORDER BY Q.Priority 8 9 UPDATE [Queue] SET Processing = 1 WHERE QueueID = @QueueID 10 11 COMMIT TRANSACTION
An alternative would be to use SQL services broker to implement a queue. However there are situations where SQL services broker just won’t provide the flexibility writing a custom queue will provide.