Re: Concurrency question

Create a unique, non null, number key field for PIN and index it, call it ID or whatever.

Create a sequence e.g. PIN_ID and use it to generate sequential key values for ID.

Create a second sequence e.g. ALLOCATE_PIN, starting with the same initial value and increment as your PIN_ID. Simply use this sequence value as the selector for the next PIN_ID

SELECT * FROM PIN WHERE ID=ALLOCATE_PIN.NEXVAL; Voila, your next unused PIN, using this method you will experience no concurrency issues.

"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.
> >
>
>
Received on Sun Jun 08 2003 - 15:39:46 CDT