Re: Sequences

: Why is it that when a sequence number is fetched/used during a transaction,
: and subsequently, when the transaction fails, the sequence number is
: not restored to its original state?

Because that is the way Oracle sequences work :)

It would be too much overhead if the RDBMS were to keep track of all sequence numbers allocated, but not committed, such that it could re-use that number. Imagine a system with many 100s of sequences and many 1000s of transactions/min, say 20% of the transactions fail you have to keep track of which numbers have been allocated and which numbers are available for re-use - a non-trivial task.

Far better to just allocate the next number of the sequence and rely on the fact that the application designers and developers have built a system that does sensible things like retrieving sequence numbers at the last possible point in the transaction.

If you require a continuous range of values with no gaps, you generally have to write your own mechanism of providing this, such as locking a row and incrementing a value and committing. All such solutions will be generally slower than accessing an Oracle sequence, so if you can live without the continuous range it may be better to do so.

IAP Received on Thu Jul 25 1996 - 00:00:00 CEST