Using a database table as a queue

by

I’d use an IDENTITY field as the primary key to provide the uniquely incrementing ID for each queued item, and stick a clustered index on it. This would represent the order in which the items were queued.

To keep the items in the queue table while you process them, you’d need a “status” field to indicate the current status of a particular item (e.g. 0=waiting, 1=being processed, 2=processed). This is needed to prevent an item be processed twice.

When processing items in the queue, you’d need to find the next item in the table NOT currently being processed. This would need to be in such a way so as to prevent multiple processes picking up the same item to process at the same time as demonstrated below. Note the table hints UPDLOCK and READPAST which you should be aware of when implementing queues.

e.g. within a sproc, something like this:

DECLARE @NextID INTEGER

BEGIN TRANSACTION

-- Find the next queued item that is waiting to be processed
SELECT TOP 1 @NextID = ID
FROM MyQueueTable WITH (UPDLOCK, READPAST)
WHERE StateField = 0
ORDER BY ID ASC

-- if we've found one, mark it as being processed
IF @NextId IS NOT NULL
    UPDATE MyQueueTable SET Status = 1 WHERE ID = @NextId

COMMIT TRANSACTION

-- If we've got an item from the queue, return to whatever is going to process it
IF @NextId IS NOT NULL
    SELECT * FROM MyQueueTable WHERE ID = @NextID

If processing an item fails, do you want to be able to try it again later? If so, you’ll need to either reset the status back to 0 or something. That will require more thought.

Alternatively, don’t use a database table as a queue, but something like MSMQ – just thought I’d throw that in the mix!

More Related Contents:

  1. How can I schedule a job to run a SQL query daily?
  2. Reset identity seed after deleting records in SQL Server
  3. Querying data by joining two tables in two database on different servers
  4. Generate script in SQL Server Management Studio
  5. Procedure or function !!! has too many arguments specified
  6. Conditional logic in PostDeployment.sql script using SQLCMD
  7. Do clustered indexes have to be unique?
  8. How do I change db schema to dbo
  9. Single or multiple databases
  10. SQL Server equivalent of WM_CONCAT function [duplicate]
  11. Insert local .csv data in remote SQL Server via already working connection string
  12. Syntax combine two stored procedure into one [closed]
  13. How to delete duplicate rows in SQL Server?
  14. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  15. How to implement a many-to-many relationship in PostgreSQL?
  16. How to avoid the “divide by zero” error in SQL?
  17. Multiple INSERT statements vs. single INSERT with multiple VALUES
  18. Does SQLite3 not support foreign key constraints?
  19. SQL Server 2008: how do I grant privileges to a username?
  20. Which SQL query is better, MATCH AGAINST or LIKE?
  21. How to Implement Referential Integrity in Subtypes
  22. NOT NULL constraint over a set of columns
  23. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  24. Name database design notation you prefer and why?
  25. Converting Select results into Insert script – SQL Server
  26. How to export data from Excel spreadsheet to Sql Server 2008 table
  27. PostgreSQL how to create a copy of a database or schema?
  28. Query a Table’s Foreign Key relationships
  29. Copy table structure to new table in sqlite3
  30. Bulk insert using stored procedure

Leave a Comment