Re: Missing sequences... how to retrieve the missing no?

This is true, but you can prevent two users from selecting the same number by using an explicit table lock before the the value is selected:

   lock table sequence_numbers in exclusive mode;

• begin critical section -- select max(seq_id) into :seq_num from sequence_numbers; commit;
• end critical section --

The commit will release the lock on the table, so it is important that it is done as soon as possible. Only one user will be allowed in the critical section at any time.

The only problem I've experienced with this method is that if for any reason the commit doesn't succeed, the table remains locked until the commit is successful. I got around this by using a post to force validation on the form, and if there are problems I bail out before selecting the sequence number:

    post;
    if :system.form_status = 'CHANGED' then -- if the post failed, don't continue.

      raise form_trigger_failure;
    end if;

This seems to work fine. I would rather not have the extra post, since we already have performance problems from operating over a WAN, but so far it's the best solution I've found.

• Joey

CREATE TABLE sequence_numbers(

 table_name                      varCHAR2(30)     NOT NULL,
 last_used_seq_number            NUMBER(10)       NOT NULL,
 max_seq_numbers                 NUMBER(10)       NOT NULL

"William." wrote:

> What you say Randy can in fact be done but I (personally) would not
> recommend it, for the simple reason if you are returned an ID of 20 for
> example there is nothing to stop a (concurrent) user getting the same
> value. This of course could then lead to a PK violation.
>
> When ever I want to preserve the IDs I always use Procedures to do the
> inserting (as a transaction), and on my DB at least, in the event of a
> rollback the ID is still available, for the next insert.
>
> In article <W_ZR4.28$741.12002_at_wdc-read-01.qwest.net>, dwyermj_at_co says...
> > This should be in a FAQ.
> >
> > What you are asking to do should not be done with a sequence. Use select
> > max(id) +1 from table. You really cannot guarantee contiguous
> > (uninterrupted) values from a sequence.
> >
> > "Randy DeWoolfson" <randy_at_euclidsys.com> wrote in message
> > news:39185143.4B089986_at_euclidsys.com...
> > > that is not really the point of a sequence - or a primary key... these
> > > just need to be unique.
> > >
> > > You could write code to pack down your number sequence and fill
> > > in the gaps, and then reset your sequence generator- any number of
> > > algorithms would work, but why? lots of work for little gain...
> > >
> > > randy
> > >
> > >
> > > jess wrote:
> > >
> > > > Dear Netizens
> > > >
> > > > I am facing the issue of missing sequence number in one of our tables. I
> > > > had set the sequence number to nocache. Hence, the system is slow and
> > > > will need to retrieve the number from the disk whenever it is needed.
> > > >
> > > > However, individual sequence numbers can be skipped if they were
> > > > generated and used in a transaction that was ultimately rolled back. How
> > > > do I make provisions to catch and reuse these sequence numbers?
> > > >
> > > > Thanks in advanced.
> > > >
> > > > Rgds,
> > > >
> > > > Jessica
> > > > <jesskung_at_brel.com>
> > >
> >
> >
> >
>
> --
> Regards,
>
> William.
>
> EuroNet*Internet BV, http://www.euronet.nl/
> Wm. G. Urquhart, Oracle DBA
> Muiderstraat 1, Postbus 11095, 1001 GB Amsterdam
> T:+31 20 5355263, F:+31 20 5355400, E: williamu_at_nl.euro.net
> M:+31 06 28206038 ICQ: 56047340
Received on Thu May 11 2000 - 00:00:00 CEST