Re: Concurrency question

Seems like you are mixing the ID and the Unique Key metaphors.

You could create a sequence for each inventory type to guarantee uniqueness and fill the tables with the ID (PIN) and the supporting info. That way you would not even need a read, just a straight insert on the initialization. AFAIK, an array of sequences is not supported, but there is no limit of sequence 'types' and you could generate the sequence name on the fly with dynamic DML.

Your description sounds like you might be able to take advantage of the 9i object types as well - but that is a separate topic.

/Hans

John Bossert wrote:

> Good ideas. A couple of wrinkles...
>
> 1) 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:54:32 CDT