Clay Shannon wrote:

> We want sequential "admission numbers" in our app to be auto-generated for
> the user (they need to see them, but getting the next sequential value
> automatically rather than relying on the data entry people to always
> accurately know what the next one should be).

My first suggestion is to push back on the requirement. Even in the
days of pre-numbered paper forms, somebody occasionally made a mistake
and procedures were developed for accounting for missing numbers. One
possibility is to allocate a generator value as part of a procedure that
inserts a record with a flag saying "this record is void", and commit
that insert. Then go through the whole business of building up a valid
record. Rather than storing the valid record, update the existing
record, making it valid. There is a tiny possibility of failure in the
middle of the procedure.


But, lets try to answer your question, which is how to use all the
numbers. The "select max(id) from ..." solution has a couple of
problems. First, it's really really slow, and second, it doesn't work
in a multi-user application. Even with a read-committed transaction,
two users can collide, like this: I get the current max: 12. I choose
13. You get the current max: 12. You choose 13. I finish my business
and check that I'm still the max + 1. You finish your business, check
that you're still the max + 1 and commit. I commit and fail with a
primary key violation.

So, lets try other solutions. You must deal with two cases: the client
quits and the client dies.

When a client gets a number and decides not to use it, you can write
some clean-up code that puts the unused number in a special table. Have
the number allocator first try to get a number from that table, and if
the table is empty, then use a generator. That does have the awkwardness
that your numbers won't be assigned in sequence.

When the client - or the request, or the server- dies unexpectedly,
there's no way to recapture the number. That can be dealt with by a
scavenge program that looks for holes lower than the current generator
value and stores those values in the table of spare numbers. That of
course makes the numbers even less sequential. But you don't lose any.

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.

A final thought, if your users really don't care what they see as the
identifier as long as it's consistent, is to use 13 for all identifiers
while the data is being processed, and replace that number with a
generated value at the end of the processing, just before storing the
new records.


Good luck,


Ann