On Oct 3, 2007, at 10:40 AM, sacha wrote:
>
> Hi,
>
> I am attempting to implement a job queue in a postgres database. There
> is a simple job table in which each row represents a job to be run.
>
> There are multiple dispatcher threads that pull jobs off the queue and
> run them. I need concurrency control to prevent multiple threads from
> dispatching the same job. I think the most elegant place to do this is
> in the database itself using row locking (rather than with OS or
> application concurrency control mechanisms, which would restrict the
> code to a single process or host).
>
> I can get and lock the next job using
>
> job = session.query(Job).with_lockmode('update').first()
>
> However, another thread running the same query would presumably block
> until the first releases the lock.
>
> Is there a non-blocking update mode, such that the second thread
> returns immediately (and I can look for a different job), or some way
> for the query to exclude locked jobs?
>
> Apologies if this is a sqlalchemy 101 (or SQL 101) question, I'm new
> to it all and I've not been able to find answers via FAQs/google.
I usually take a low-tech approach to this problem and update the
rows which I want to process with a status flag, such as "IN
PROGRESS". Subsequent queries for job rows by other threads query
for rows which have "QUEUED" as their status flag, thereby ignoring
the IN PROGRESS rows. that way nothing is locked outside of the span
of single short-running transaction. i.e.
BEGIN
SELECT * FROM jobs WHERE status='QUEUED' FOR UPDATE
UPDATE jobs SET status='IN PROGRESS' WHERE status='QUEUED'
COMMIT
if you want just one job, then just update the WHERE criterion of the
UPDATE statement accordingly to match the job(s) you are actually
going to process (or just use an ORM flush if youre using the ORM).
with postgres 8.2 (and the latest trunk of 0.4) you can even do a
RETURNING and get the whole thing in one query:
UPDATE jobs SET status='IN PROGRESS' WHERE status='QUEUED' RETURNING *
when jobs are finished I usually mark them as COMPLETE, that way you
get a log output of job history as a bonus.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
