I need to implement a queue using table.
The business requirement is to have a single queue which will be accessed by 5-10 boxes to get the next job/jobs. There will not be more than 5000 jobs per day. Also, a batch of jobs should be "dequeued" at one time.
Just wondering what are the problem areas and issues I might run into as I havent done it before. If anyone has faced this/done this before, can you please point me to a design/sample implementation or issues that need to be taken care of.
Thanks
There are a lot of general purpose queuing or messaging services. Even if you want to implement your own system, you could try to have a look at a few others. The first thing that come to mind is JMS (Java Message Service) with implementations like Apache ActiveMQ, OpenJMS or JBoss Messaging. Then you have a lot of non-opensource offering as well.
Second thing that comes to mind is Amazon Simple Queue Service. There is a couple of products that implement the same kind of interface, like django-queue-service.
Good luck !
This does not sound too hard; simply include a timestamp that you can sort by whenever jobs are entered. Depending on the database, this field can be autofilled with the current timestamp.
When fetching jobs it is as easy as putting the SELECT and DELETE statements in a transaction. If you feel that is uncomfortable, something like this might do it:
UPDATE tblQueue SET mark = <unique application id> WHERE mark IS NULL ORDER BY timestamp ASC LIMIT 1
SELECT * FROM tblQueue WHERE mark = <unique app id>
DELETE FROM tblQueue WHERE mark = <unique app id>
By using this setup you can avoid transactions, if they scare you.
Your definition of a batch is somewhat unclear; if you simply mean that I should be able to process 10 items at a time, just change the LIMIT 1 clause of the first query to LIMIT 10.
If you mean that jobs can be grouped, you probably need a job queue, and put subitems in another table (that is not a queue, just a regular table with a foreign key pointing to the job item).
Thanks Vegard.
But the approach that you suggested will result in loss of job requests in case the system which took job fails/crashes.
I was thinking of a Queue table with following columns
- RequestID/MessageID (Primary Key)
- LockedBy (who is working on the request)
- LockedTime (when request is locked for processing)
- RequestedTime (when request gets added to queue)
- CompletionTime (when is request completed)
- Status (Pending/Processed)
- RequestMessage (serialized java object in my case)
- Requestor (who enqueued the request)
I can write a stored procedure [GetNextItemsInQueue] which returns a list of lets say 10 requests, sets the Locked By and Locked Time. If Locked Time increases a specified limit, the record can be put back to Pending state.
Any problems with this one?
