Re: Oracle Serial numbers

Glenn Berry wrote:
>
> huele_at_pacbell.net wrote:
> >
> > I want to have serial number on my table record by using "nextval", but
> > the number go from 1,2,3,4,5 then 21,21,24,25. Any expert can help me
> > out?
> > Thanks alot.
>
> I have made an assumption that you are looking for serial numbers ie 1 -
> 20 but you actually got 1,2,3,4,5,21,22,23,24,25.
>
> This is usually caused by the values being cached and then you lose the
> instance for some reaseon, the way to avoid this is to set nocache for
> the sequence.
>
> Glenn.

Not always true. If the next serial is selected and not committed for some reason, values in the sequence will be lost. The answer is to read sequence numbers from a table where they are stored and committed in the same transaction as whatever uses the serial number. This has a downside in that a lock is taken on the row, which has obvious implications in a multiple user environment.

Cheers
Chris Received on Wed Oct 09 1996 - 00:00:00 CEST