Re: sequence omits numbers?

Originally posted by Stefan R?Hle
> Hi there,
>
> I have encountered a strange problem: a sequence seems to leave out
> numbers - how can that happen?
>
> But let me describe the setup briefly:
> For out internal online ordering system I use a function
> (stored in db,
> lets call it new_number()) that creates new invoice numbers.
> These have
> to be unique so new_number() fetches a new number from a sequence for
> each invoice number.
> The whole application consists of a few webpages based on php
> using the
> pear db.php to communicate with our oracle 8i server.
> Each time a user creates a new invoice new_number() is called in order
> to supply a new number. (it is only possible to get a new number once,
> so there is no problem with the 'reload' button of the browser)
> Everything is logged so that I can review which user has created which
> new invoice-number etc.
> When everything is ok, the numbers have no gaps inbetween, but
> sometimes
> there are small gaps (3 or 4 numbers missing) and even large
> gaps (30-50
> numbers missing).
>
> I have no idea how that can happen especially because I log
> each call of
> the new_number() function in my php-scripts...
> In order to troubleshoot that I first had a trigger in mind that fires
> when the function is called but since that is not possible I have no
> further idea how to find the error. Any ideas?
>
> Stefan
>
> --
> *******************************
> Stefan Röhle
> Zentrum für Datenverarbeitung
> Johannes Gutenberg-Universität
> D-55099 Mainz
> Germany
>
> Tel. +49-(0)6131/39-26303
> Fax. +49-(0)6131/39-26407
> Email: roehle_at_uni-mainz.de
> *******************************

Not only is it NOT a bug - it IS a feature! To guarantee no gaps in IDs created from a sequence, Oracle would have to serialize inserts, which would make your application non-scalable. Consider just 2 users (and ignoring caching for now):

USER1> insert into invoice( id ) values (seq.nextval); -- Gets 1

USER2> insert into invoice( id ) values (seq.nextval); -- Gets 2

USER1> insert into invoice( id ) values (seq.nextval); -- Gets 3

USER2> rollback;

USER1> commit;

Invoice now contains ID values 1 and 3. 2 has disappeared! There is a gap!

If Oracle did guarantee gap-free numbering (which it does not) something like this would have to happen:

USER1> insert into invoice( id ) values (seq.nextval); -- Gets 1

USER2> insert into invoice( id ) values (seq.nextval); -- Blocked -- USER2 cannot get value 2 because USER1 might rollback and leave 1 unused; session has to wait for USER1 to either commit or rollback, which might be seconds or minutes...

USER1> insert into invoice( id ) values (seq.nextval); -- Gets 2

USER1> commit;
-- USER2's insert now completes and gets 3

In a real system with lots of users, this would be intolerable.

--
Posted via http://dbforums.com