Re: Strange problem with Sequence.. please help..

xtanto_at_hotmail.com wrote:
> Hi All,
>
> Thanks for all your answer... Now I realized I cannot use sequence to
> get _continuous_numbers.
>
> So what is the best approach to get the _continuous_numbers without any
> chance to have a gap ?
>
> Thank you very much,
> xtanto

Without using a sequence, you will end up serializing data input into the system - only one session will be able to insert into your table at a time. What I have read in the past is that you could create a table with a single row that contains a counter, select the current value from the table, update the value by adding one to it, and then perform your data input. You must take great care when doing this - different database engines handle this situation differently. Some databases will cause sessions to sit patiently on the SELECT of the one row counter table because another session has a pending UPDATE on the single row table. Databases, such as Oracle, that provide read consistency, by default will read the old value from the counter table (even after another session updated it), and then possibly cause hard to track down primary key violations and/or missing records in the database if two sessions happen to insert data at roughly the same time. Tom Kyte's books detail how to work around this problem - as I recall the Expert One on One book goes into detail of what can go seriously wrong if this is not handled correctly.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 22 2006 - 06:49:15 CDT