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

Hi,

On Sun, 13 Aug 2006, Jorge Godoy wrote:

> Christian Kratzer <ck-lists(at)cksoft(dot)de> writes:
>
>> I would at least try to assign multiple such numbers in batches to mimize
>> contention on the row you store the counter in.
>
> What do you mean here? How would you guarantee that on of the receiver
> transactions didn't rollback and left a gap in the "sequence"?

you would need to serialize the transactions assigning the
numbers and you would need to update the the counter in
the same transaction that assigns your numbers to your
documents or whatever.

Assigning a batch of 1000 numbers in one transaction would
propably be more efficient than assigning 1000 numbers in
1000 separate transactions that all need to be serialized.

> I believe that for invoices it is less problematic. At least here I don't
> need the "time" part control, so if I leave one blank I can fill it later in
> the same day without problems (except, of course, if the sequence number is
> tied to some other physical evidence such as the paper counterpart of the
> invoice and that is also chronologically assigned).

Thats of course the idea. The numbers on the paper invoices
have to be gapless. The tax people want to have a warm
fuzzy feeling that they are seeing all your invoices or they
will begin to speculate on how much vat they have not
received from you.

> The whole problem appears because no matter how much we validate input and
> relationships on the input interface, something might happen and make the
> "INSERT" transaction fail. Theoretically, all should go fine, but... :-)

increment the counter in the same transaction that assigns
your values.

Of course I know little or nothing about your application
and what you need gaples sequences for.

I just pulled the invoice example out of my hat to show
that there are legitimate use cases for gapless sequences
of numbers.

Greetings
Christian

--
Christian Kratzer ck(at)cksoft(dot)de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136