Re: Concurrency question

From: John Bossert <jbossert_at_usa.net>
Date: Sun, 08 Jun 2003 23:37:47 GMT
Message-ID: <3EE3C8B7.9080205@usa.net>

Good ideas. A couple of wrinkles...

This would be a perfect solution if I had a single class/type of PIN. I will have hundreds. With your approach it would seem that I'd have to have a pair of sequences (PIN_ID and ALLOCATE_PIN) for each class/type of PIN.

If I could set up something like an array of sequences... Any trick for this with 8i/9i ?

2) PINs are a kind of inventory, each with a dollar value. In the event of a rollback, I'd lose PINs due to the loss of the sequence value. I suppose I could run a reaper process to periodically find these "lost" PINs and assign them new sequence values from PIN_ID, but I'd prefer to have a "self-maintaining" PIN pool.

Maximus wrote:
> 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.
>>>
>>
>>

>
>

-- 
John Bossert

In what concerns you much, do not think that you
have companions: know that you are alone in the world.

             -- Henry David Thoreau

Received on Sun Jun 08 2003 - 18:37:47 CDT