Queues and locking contention in SQL Server

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s