Re: Concurrency question

From: John Bossert <jbossert_at_usa.net>
Date: Sun, 08 Jun 2003 19:42:16 GMT
Message-ID: <3EE39185.30306@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 - 14:42:16 CDT