> >What we did, is to have an internal PK value (by a generator),
> >insert the new records and once everything was inserted (saved)
> >to the database and the transaction was committed (as in: no
> >faults), we then did:
> >
> >UPDATE <table>
> >SET UserVisibleColumn = GEN_ID(mygenerator, 1);
> >
> >(actually, the Oracle equivalent, but you get the idea).
> >
> >I don't think we ever missed a number.
>
> This is an interesting variant on the usual rules about theory and
> practice. In practice, that probably works. In theory it doesn't. If

you

> had a network/power/disk/application failure between the update statement
> and the commit that made it permanent, you'd get a hole in the sequence.
> Even if you use auto commit, there is some time lag between acquiring the
> sequence value and putting the update on oxide. If some madman comes into
> your computer room with an ax at exactly the wrong instance, you'll have a
> hole in the sequence.

Well, yes. But if an airplane crashes onto your server room,
a RAID array for redundancy doesn't work either :-)

In practice, the server room was locked and there was an
UPS. That helps as well.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com