Thread: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
From
Brett Hoerner
Date:
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
Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
From
Rob Sargent
Date:
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 >