On Mon, Sep 7, 2009 at 8:18 PM, Allen Fowler<allen.fow...@yahoo.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
