Re: choosing the right locking mode

On Thu, Apr 3, 2008 at 12:58 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> 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.

Add prepared select statements and you'd get get pretty fast
performance. I'd hope it's a situation where most blocks ARE free.

Note that I based my whole argument that if a number down lower could
become free then gaps weren't a problem. If gaps somehow are a
problem, then there's a whole other set of approaches for providing
the illusion of gap free ids without all the cost.