pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
2+ messages / 2 participants
[nested] [flat]

* Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
@ 2010-07-20 02:06  Brett Hoerner <bretthoerner@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Brett Hoerner @ 2010-07-20 02:06 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
@ 2010-07-20 20:33  Rob Sargent <robjsargent@gmail.com>
  parent: Brett Hoerner <bretthoerner@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Rob Sargent @ 2010-07-20 20:33 UTC (permalink / raw)
  To: pgsql-sql

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
> 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2010-07-20 20:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-20 02:06 Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock Brett Hoerner <bretthoerner@gmail.com>
2010-07-20 20:33 ` Rob Sargent <robjsargent@gmail.com>

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