Re: Processing a work queue

On 4/30/07, John D. Burger <john@mitre.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