Re: How to avoid sequence from skipping numbers

"S S Wan" <sswan_at_hk.super.net.NOSPAM> wrote (in <6hrpij$eef$1_at_tst.hk.super.net>)...

| It is possible that a transaction within which a sequence is selected
| finally roll back. In this case the sequence is not rolled back however and
| the number will be skipped forever.
|
| Is there any method to avoid this? I want a sequence which will never skip
| numbers for generation of things like voucher number which is a business
| requirement (??!!).
|

Hello,
  I suggest that you don't use sequences for this. They were designed for generating unique numbers very quickly. Their main use is for populating 'identifier' columns. By default they don't guarantee anything except to return unique values. They don't even guarantee to return numbers in ascending order! They are very quick though.

If you have a requirement to generate unique numbers and guarantee no gaps and every number has been used then i suggest you use a database table which contains the current value (voucher number).

All access to this value use a 'select for update' and update it as required. The transactions which use the value must only commit after successfully completing all parts of their transaction.

What this will do is serialise all use of the voucher number.

graham Received on Sat Apr 25 1998 - 03:11:58 CDT