Re: Processing a work queue

On 4/30/07, John D. Burger <john(at)mitre(dot)org> wrote:
> Can someone explain why [advisory locks] are a better fit than whatever locks
> SELECT FOR UPDATE acquires?

ok, here's an example. I was thinking that my sequence idea might not
be safe because of race conditions revolving around querying the
sequence table. Here is how I might use advisory locks eliminate the
race condition:

create table job (job_id serial primary key);
create sequence worker;

-- get next job
select
pg_advisory_lock(1),
(
case
when (select last_value from worker) < (select last_value from
job_job_id_seq)
then (select job from job where job_id = (select nextval('worker')))
else null::job
end
) as job,
pg_advisory_unlock(1);

couple notes here:
* this may not actually safe, just fooling around
* does not account for is_called
* assumes left to right evaluation of expressions (dangerous?)

Here we are using advisory lock guard around the check
sequence/evaluate sequence step. The idea is to prevent the race of
somebody incrementing worker after we looked at it last.

Advisory locks can hold locks for sub-transaction duration or even (as
in this example) sub-query duration. This query can be dropped into a
much larger transaction without ruining concurrency...any standard
type of lock can't be released like that.

merlin