Re: Processing a work queue

Merlin Moncure wrote:
> 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

Hmm, now I think I see what your problem is.

You're accessing the sequence from different sessions, but you need the
last value that was used in another session, right? That's why you query
last_value.

You're basically fighting against the assumption that different sessions
must have different values, which sequences are based on.

> 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),

Yes, probably. Session a will need to prevent session b from running
jobs until it's done. Or maybe not entirely...

I suppose it knows beforehand when it will be done, based on the data it
sees at the start of the session - it may be able to tell other sessions
where to start. Let it reserve the current queue for its own use, so to
speak.

That basically moves the locking problem around, but it seems better
than to lock until it's done - other sessions can do work in the mean time.

Whether that improves your performance pretty much depends on the queue
sizes, the frequency of processing and the load processing causes...
Heh, I just realize we have a work queue table here as well, I'd like to
know the result.

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

Indeed, that's one of the weaknesses of last_val. With your example I
now understand why it even exists, I always thought it an odd and
dangerous feature.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //