Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock

On 21/07/10 07:27, Brett Hoerner wrote:

> 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?

You kick off two queries at once. Both have subqueries that grab a set
of id,job,arg . There's no exclusion at this stage, so they can easily
both land up with some or all of the same results.

THEN you filter the result. The filter will drop the result list to
empty if it can't acquire the lock. Under what circumstances can it not
acquire the lock? If another transaction holds it.

The first transaction might have grabbe the data, acquired the lock,
done its processing, and committed/rolled back to *release* the lock
before the second transaction gets around to checking the lock. In this
case, the second transaction will happily acquire the lock.

Classic race condition.

You should probably use one of the existing queuing mechanisms rather
than rolling your own, because building a high-performance, reliable
queueing mechanism is surprisingly hard to build. A search of the
archives here will turn up several options. I've noticed that PGQ from
Skytools gets mentioned a lot.

--
Craig Ringer