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,
:)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Queue / FIFO in MySQL?

On Mon, Sep 7, 2009 at 8:18 PM, Allen Fowler 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=gcdmg-mysql-2@m.gmane.org

Re: Queue / FIFO in MySQL?

On Mon, Sep 7, 2009 at 8:18 PM, Allen Fowler wrote:
> 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?

SELECT...FOR UPDATE followed by one or more UPDATEs in a transaction.
Either use MIN(id) to get the next lowest id or ORDER BY and LIMIT to
get n records at a time.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Queue / FIFO in MySQL?

--000e0cd356c8bc4aef0473092420
Content-Type: text/plain; charset=ISO-8859-1

Please check out http://q4m.31tools.com/*. *It is a message queue storage
engine with sql interface. Havent used it in a production setup though we
did some tests.

--
Thanks
Alex
http://alexlurthu.wordpress.com

--000e0cd356c8bc4aef0473092420--

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@yahoo.com]
Sent: Monday, September 07, 2009 5:18 PM
To: mysql@lists.mysql.com
Subject: Queue / FIFO in MySQL?

Hello,

I need to create a system where records are generated by a "producer" proce=
ss and processed by several "worker" processes.

I was thinking about something like:

Producer:
1) Producer INSERTs new records with "state" =3D "new" & "worker" =3D "null=
"
2) Producer sleeps and loops back to step #1

Worker(s):
1) Worker UPDATEs all records with "worker" =3D "pid" & "state" =3D "workin=
g" where "state" == "new"
2) Worker SELECTs all records where "worker" =3D "pid" & "state" =3D "worki=
ng"
3) For each record that is done, worker updates record with "state" =3D "do=
ne"
4) Worker loops back to step #1

Note: In this scheme the worker winds up with all "new" records generated s=
ince 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 m=
e to example code, libraries, and/or refinements on the scheme? (preferabl=
y using python...)

Thank you,
:)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Queue / FIFO in MySQL?

>-----Original Message-----
>From: Gavin Towey [mailto:gtowey@ffn.com]
>Sent: Tuesday, September 08, 2009 1:41 PM
>To: Allen Fowler; mysql@lists.mysql.com
>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@yahoo.com]
>Sent: Monday, September 07, 2009 5:18 PM
>To: mysql@lists.mysql.com
>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





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org