List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 8 2009 12:24am
Subject:Re: Queue / FIFO in MySQL?
View as plain text  
On Mon, Sep 7, 2009 at 8:18 PM, Allen Fowler<allen.fowler@stripped> wrote:
> Hello,
>
> I need to create a system where records are generated by a "producer" process and
> processed by several "worker" processes.
>
> I was thinking about something like:
>
> Producer:
> 1) Producer INSERTs new records with "state" = "new" & "worker" = "null"
> 2) Producer sleeps and loops back to step #1
>
> Worker(s):
> 1) Worker UPDATEs all records with "worker" = "pid" & "state" = "working" where
> "state" == "new"
> 2) Worker SELECTs all records where "worker" = "pid" & "state" = "working"
> 3) For each record that is done, worker updates record with "state" = "done"
> 4) Worker loops back to step #1
>
> Note: In this scheme the worker winds up with all "new" records generated since the
> last worker claimed any. Not sure how else to guarantee atomicity. I would prefer "only n
> records per request". Ideas?
>bly using python...)
>
> Thank you,
> :)


Assuming you are using MYISAM tables, all you really need to do is (a)
use a LOCK TABLE before the first UPDATE statement and UNLOCK TABLES
after, and (b) put a LIMIT clause on the UPDATE statement.  Other than
that, what you outlined is exactly what I do for a very similar
process, although right now I only have one "worker" process, but if I
wanted to add more, it's already built to handle that.

-Hank
Thread
Queue / FIFO in MySQL?Allen Fowler8 Sep
  • Re: Queue / FIFO in MySQL?Hank8 Sep
  • Re: Queue / FIFO in MySQL?Perrin Harkins8 Sep
    • Re: Queue / FIFO in MySQL?Alex Arul Lurthu8 Sep
  • RE: Queue / FIFO in MySQL?Gavin Towey8 Sep
    • RE: Queue / FIFO in MySQL?Jerry Schwartz8 Sep