[thelist] Queue processing with a database table.

[Matt Warden]

On Sun, Aug 29, 2010 at 9:04 AM, Bill Moseley <moseley at hank.org> wrote:
> It's possible that a process might die, so the "start_time" is used to clear
> up stale "processing" states.
>
> My first pass was SELECT ... FOR UPDATE, but (at least in Postgresql) what
> happens is if two processes do the "SELECT * from queue WHERE state =
> 'ready' limit 10 FOR UPDATE" then when the first COMMIT happens the second
> transactions ends up returning zero rows.  Or to be clear, say there's 1000s
> of rows, if the first transaction selects 10 rows, but updates 5 to
> "processing" then after it commits the second transaction only returns the 5
> rows that are still "ready".    Seems that the second SELECT .. FOR UPDATE
> grabs the rows and then blocks.  Then after the first commit tests the WHERE
> again.
>
> Anyway, is there a way to do this other than a full table lock?

If you really only want to utilize the database for this (as opposed
to creating a singleton as Hassan suggested), I would create a second
table called QUEUE_CLAIM where your processing machines insert rows to
claim individual rows in the queue table. Create a constraint that
enforces the uniqueness you want on QUEUE_ID (or whatever your queue's
PK is). The database will take care of the rest.

This suggestion only works because you are dealing with relatively low
chunk sizes (10 rows).

I suspect your database would actually manage the concurrency scenario
you put forth, but that gets deeper into implementation details than I
feel comfortable. The above suggestion aims to avoid those details,
and as a nice bonus will work across database platforms.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.