> Another solution that may have occurred to you is keeping
> your own counter in a table. To get a number you update
> the counter, incrementing it by one, and select the value
> all in the same transaction that collects the data and
> stores the record. Don't do it. You'll synchronize all
> your inserts.

**IF** the requirement truly exists: sequential (ie. in-
sequence) identifiers with no missing values

THEN implicit in this requirement is that the identifier
allocation must be serialised in some way. Your own table
of sequence numbers becomes the appropriate solution. It
provides both the numbers and the means of locking to ensure
serialisation.

It is self defining, AFAICT there is no other way to achieve
the requirement.


However it does not necessarily follow that the inserts must
be synchronised. In one of my applications the process of
creating invoices with sequential identifiers is done in two
stages...

1. The users enter the data required. At this stage the
the invoices are considered incomplete.

2. The user runs a stored procedure that assigns the invoice
numbers. ie. Lock the sequence record, get the next number
and update the sequence record, use the sequence number.
Only after the invoices have been given numbers are they
considered complete.

(Actually the particular application is a bit more complicated
than this, but this describes the basic idea.)

Note: The sequential identifiers in this instance are not used
as the primary key (I use a surrogate value), because they do
not exist when the record is first created. The sequence id is
used more like a "name" for the invoice.

Splitting the input from the number assignment ensures that
the transaction that assigns numbers is quite short, which
minimises the chances of synchronisation problems. It also
allows the second stage to be a "batch" process that can handle
multiple invoices (eg: users do lots of data entry, check the
inputs and then run a batch process). The use of numbers from
a table ensures that it all takes place inside a transaction.

There is yet another reason for using your own table of sequence
numbers... There is no security on generators. If you need an
auditable sequence you will presumably want security.


People seem so scared of synchronisation and serialisation.
Sure it is something to try and avoid or minimise in a multiple
user environment, but sometimes the requirement demands that
certain actions are serialised. We need to recognise these
requirements and try to implement them in a way that minimises
the problems but still behaves appropriately.

--
Geoff Worboys
Telesis Computing