Re: Processing a work queue

On 4/27/07, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> Anyone have any ideas on how to handle a work queue? I've been thinking
> about optimizing this process for quite a while.
>
> Basically, my queue table consists of a few-hundred-thousand records
> describing "things to do". To pare things to the minimum, a queue record
> can be considered to have a status (available, assigned, completed), a
> priority, and a description-of-work.
>
> A process will grab an item from the queue, mark it as in-progress,
> process it, and, depending on success, update the item as completed or
> as available with an updated priority. There may be upwards of a
> thousand "worker" processes and the work of each process may be
> completed in anywhere from a few seconds to nearly an hour. I expect the
> system as a whole to be handling a few-dozen queue items per second.
>
> My original plan to fetch work was:
>
> begin;
>
> select item-id, item-info
> from the-queue
> where available
> order by priority
> limit 1
> for update;
>
> update the-queue
> set status = 'assigned'
> where item-id = previously-selected-item-id;
>
> commit;

how about this:
create table job(job_id int, [...])
create sequence worker;

your worker threads can do something like:
select * from job join
(
select nextval('worker') as requested_job
) on job_id = requested_job
and
(
(select (w.last_value, w.is_called) < (j.last_value, j.is_called)
from worker w, job_id_seq j)
)

and then sleep appropriately if there is nothing to do. Of course, if
the job fails you have to put it back on the queue. No locking
required! This relies on false being < true...safer to break out to a
case stmt but im just trying to be clever :-)

This has couple of advantages but is also pretty fragile. I'm not
necessarily suggesting it but it was a fun way to think about the
problem.

merlin