Re: Concurrency question

"John Bossert" <jbossert_at_usa.net> wrote in message news:3EE39185.30306_at_usa.net...
> Additional detail... tables are simplified to mask irrelevant attributes.
>
> create table PIN (
> pin varchar2(10) not null,
> pin_type_cd varchar2(2) not null) primary key pin;
>
> create table PIN_HISTORY (
> agreement_history_id number not null,
> pin varchar2(10) not null,
> allocation_date date) primary key agreement_history_id;
>
> agreement_history_id comes from a sequence agreement_history_seq.
>
> (simplified) present method for obtaining a pin:
>
> insert into pin_history
> select agreement_history_seq.currval, min(pin) from pin a where not exists
> (select pin from pin_history b where a.pin = b.pin) and
> pin_type_cd = 'XX';
>
> How would I redesign this to use sequences (one for each type of pin?)
There
> may be hundreds of pin_type_cd's (and they will expand over time) and it
would
> seem that I would have to have a sequence for each type.
>
> Maximus wrote:
> > "John Bossert" <jbossert_at_usa.net> wrote in message
> > news:3EE37324.7090604_at_usa.net...
> >
> >>I have a table of unique numbers, call them PINs. Once a PIN has been
> >
> > assigned
> >
> >>to a user, it needs to be unavailable to other users.
> >>
> >>I have a query which identifies unallocated PINs. However, I don't know
> >
> > the
> >
> >>best way to avoid the situation where two transactions could grab the
same
> >
> > PIN,
> >
> >>if a second transaction began before the first transaction committed.
> >>
> >>Is there a convention/best practice for handling this scenario? I'm
aware
> >
> > of
> >
> >>SERIALIZABLE, but am concerned about the potential impact on throughput.
> >>
> >>Oracle 8.1.7.3.0 and 9.2.0.3.0 on Solaris 8. Client application is
Tomcat
> >
> > 4.1
> >
> >>and java servlets.
> >>
> >>Pointers appreciated.
> >
> >
> > You can only lock data to prevent other processes from updating, so in
your
> > case the obvious solution of using locks won't work.
> >
> > You need to change your method of allocating the PIN, for example you
could
> > create a sequence and use it to index your next unused PIN. This will
> > guarantee no two processes will try allocating the same one.
> >
>
>

John,

I'm still not sure what you're trying to do here but won't one sequence do for all types.
With sequences you cannot guarantee that values won't get lost from time to time.

HTH eric Received on Sun Jun 08 2003 - 15:20:58 CDT