At 04:35 AM 2/20/2004, Martijn Tonies wrote:

>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.

Cheers,


Ann