Re: Processing a work queue

On 5/1/07, Alban Hertroys <alban(at)magproductions(dot)nl> wrote:
> Merlin Moncure wrote:
> > 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
>
> I've seen your name pop up regularly on this list (or are you from
> freebsd-stable?), so you kind of got me scratching my head whether you
> really don't understand sequences. Kind of hard to imagine... Maybe I
> don't understand what you're asking.

been posting here for years :-)

> Sequences are safe in concurrent use.
> * Nextval() always returns a new number, so no two concurrent sessions
> can get the same one.
> * Currval() is only valid within one session after calling nextval(), so
> it's number cannot have been modified by another session.
>
> Why do you expect to need locking?

take another look at my example. there are two things happening that
have to be logically combined into one operation. one is checking the
last_value column of two sequences and the other is the nextval().
the advisory lock protects against this:

session a: worker last_value < job last_value..true!
session b: worker last_value < job last_value..true!
session a: increments worker
session b: increments worker

this will cause a job to get skipped. My first go at this example
didn't have the locks in there and I was thinking I introduced a race
(i'm almost sure of it),

the advisory lock serializes those two operations, but only those two
operations. sequences are naturally safe from point of view of
generating unique number if used with nextval(), setval(), etc. but
not necessarily
select last_value from s;

from point of view of comparing that value with something else and
incrementing it if and only if that condition is true in a consistent
context.

merlin