From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
Date: Tue, 20 Jul 2010 14:33:14 -0600
Message-ID: <4C46080A.4030904@gmail.com> (raw)
In-Reply-To: <AANLkTilpuElO1FgaZfe3cBZ9RoM5yYvIU8D8TanvRO1I@mail.gmail.com>
References: <AANLkTilpuElO1FgaZfe3cBZ9RoM5yYvIU8D8TanvRO1I@mail.gmail.com>
You could implement an optimistic lock strategy by placing a 'version'
column in the table and increment it on successful 'check-out' and test
against the value the user has as he/she tried to act on the record. If
some else got there first the second user fails to check-out the queue
item. Triggers could do the work.
On 07/19/2010 08:06 PM, Brett Hoerner wrote:
> Hi,
>
> I currently have a simple queue written ontop of Postgres. Jobs are
> inserted and workers periodically check for jobs they can do, do them,
> and then delete the rows. pg_try_advisory_lock is used to (attempt
> to) stop two workers from doing the same job.
>
> (I'm working on moving to a "real" messaging queue right now, this is
> more a point of curiosity and education now.)
>
> Here is my queue table,
>
> CREATE TABLE queue (
> id serial NOT NULL PRIMARY KEY,
> rcvd timestamp with time zone,
> sent timestamp with time zone,
> host character varying(32),
> job character varying(32),
> arg text
> );
>
> Here is an example query,
>
> SELECT q.*
> FROM (SELECT id, job, arg
> FROM queue
> WHERE job = 'foo' OR job = 'bar'
> OFFSET 0) AS q
> WHERE pg_try_advisory_lock(1, q.id)
> LIMIT 10
>
> (For information on OFFSET 0 see:
> http://blog.endpoint.com/2009/04/offset-0-ftw.html)
>
> Now if I have two workers running I will periodically see that each
> worker gets a row with the same q.id (and thus does the work). How is
> that possible? The outer query seemingly does a WHERE on an
> advisory_lock.
>
> Does anyone have any ideas? Am I grossly misusing advisory_locks?
>
> Thanks,
> Brett
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com
Subject: Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
In-Reply-To: <4C46080A.4030904@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox