Re: Best approach for a "gap-less" sequence

On 8/17/06, Brad Nicholson <bnichols@ca.afilias.info> wrote:

> > > Hmm, I think you are wrong.  There is a SELECT ... FOR UPDATE;
> > > The first-to-obtain the gapless sequence transaction will establish
> > > a lock onthe "tax_id" row.  The other transaction will block until
> > > the first transaction finishes (and the row is updated) and will
> > > establish the row lock on it.
> >
> > yes, you are right...i didnt think the problem through properly.
>
> Lets just hope the performance on a concurrent system is not a
> requirement of such a system...
>

right, if the transations are long running, there is a big problem as
they are serialized around access to the sequence.  however this is
better than the control record approach because control record have
problems with mvcc bloat.  concurrent performance will of course be
awful.

a good compomise in some cases is to save off canceled transactions
ids' in a free list   you would still have to deal with transactions
that were not gracefully cancelled though.

merlin