> Scott Marlowe wrote:
>
> Sure, but you have to trap that all the time. The solution using a
> cycling sequence keeps you from ever seeing that (unless you managed
> to check out all 9,999 other values while still getting the current
> one. No locking needed, dozens of updaters running concurrently and
> no need to track update errors.
>
> Yep, that does sound like it'd be nicer, at least if locks are
> becoming free at a reasonable rate (ie you don't have to step through
> most of the table to find a free lock). I was working on the probably
> mistaken assumption that the OP wanted the "next" / "first" available
> slot, not any free slot.
>
> If there are very few free locks at any given time I have the feeling
> the sequence approach could spend a lot of time just scanning through
> the table looking for free entries. Then again, using an aggregate
> subquery is far from free either, and it's a whole lot nicer to just
> repeat one statement until it succeeds rather than retrying the whole
> transaction if it conflicts with another (which will happen often if
> there's really high demand for locks).
>
> In fact, both transactions trying to grab the lowest free lock is
> practically a recipe for serialization failures, making it even less
> attractive. With only two concurrent connections it'd work OK if one
> used min() and the other used max() ... but add another couple and
> you're in trouble.
>
> The serial based approach sounds a fair bit better.
Serial access to the firewall is what I'm now emulating with "LOCK
bw_pool" as the first statement in the transaction. AFAIK Postgres'
serializable transactions give you decent parallelism at the price of
expecting you to retry them due to serialization errors, and thus cannot
be relied upon for doing actions on a shared external resource (like
manipulating the firewall) after having LOCKed some table. It's a pity
there's no way to let go of the lock as soon as possible, only
implicitly at the end of the transaction.