List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 8 2009 6:54pm
Subject:RE: Queue / FIFO in MySQL?
View as plain text  
>-----Original Message-----
>From: Gavin Towey [mailto:gtowey@stripped]
>Sent: Tuesday, September 08, 2009 1:41 PM
>To: Allen Fowler; mysql@stripped
>Subject: RE: Queue / FIFO in MySQL?
>
>You can add a LIMIT n to your update clause.
>
>Regards,
>Gavin Towey
>
>-----Original Message-----
>From: Allen Fowler [mailto:allen.fowler@stripped]
>Sent: Monday, September 07, 2009 5:18 PM
>To: mysql@stripped
>Subject: Queue / FIFO in MySQL?
>
>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?
>
>I am sure something like this must have been before....  Can anyone point me 
>to
>example code, libraries, and/or refinements on the scheme?  (preferably using
>python...)
>
>Thank you,
>:)
>
[JS] Based on my (somewhat distant) experience, using a database as a FIFO can 
turn into a performance nightmare. I don't know how many "workers" you plan on 
having, nor how many incoming jobs you're going to have, but unless your sleep 
times can be long relative to the time it takes to insert or update the 
records things can get out of control very quickly.

Isn't there some alternative available, such as (in *NIX) a FIFO?

As for atomicity, I would think you'd want to wrap the UPDATE and SELECT in a 
single transaction.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com




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