Re: two inserts at same time

I would like to suggest to create a procedure "insert_into_mytable (col1, .., col_user)" to do that. Or even a package (that is much better I think) "pk_mytable" with procedure "insert_it" - something like that, all the names are rough.

The first matter is that you will avoid duplicate code and the second one is that if you ever decide to change the procedure of inserting into mytable you will have to change code in only one place. Package will let you unify hundreds of you procedures into ... packages. :)

Alexander Cook

In article <8rfga1$vrm$1_at_nnrp1.deja.com>,   Eoin Gardiner <eoin7_at_my-deja.com> wrote:
> It's good that you have a constraint on the table.
>
> I don't like the select count(*) solution. At a minimum, a select on
> the key (which is indexed) would be more efficient, and not a select
> count, but open a cursor to specifically select the value in the key
> that you do not want to duplicate.
> However, that is still not water-tight. I prefer using the error
> condition:
>
> BEGIN
> INSERT INTO MYTABLE (COL_1, COL_2, COL_3, COL_USER)
> VALUES (V_1, V_2, V_3, USER);
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> UPDATE MYTABLE
> SET COL1 = V_1,
> COL2 = V_2,
> COL3 = V_3
> WHERE COL_USER = USER;
> END;
>
> In article <8rdnnh$k2s$1_at_nnrp1.deja.com>,
> lfto_at_my-deja.com wrote:
> > in my situation, there is a constraint on the table, so only one
> > row can be INSERTED for a key (userid). So, my conern is if two
 process
> > A and B are INSERTing a row for the same userid, then the first one
> > to commit will be ok....but the second process to commit will error
> > on the INSERT since the record already exists.
> >
> > i SELECT count(*)
> > on userid to determine if i INSERT or UPDATE...so, between the
> > statments is when another INSERT could potentially happen.
> >
> > anyway, i'm trying to figure out how to prevent two INSERTS occuring
 at
> > the same time...or prevent handling
> > the INSERT error it by catching an exception and then UPDATING
> > instead...but this might be my only option?
> >
> > mike
> >
> > In article <8rdi93$ffb$1_at_nnrp1.deja.com>,
> > Eoin Gardiner <eoin7_at_my-deja.com> wrote:
> > > Mike,
> > >
> > > It is still unclear what you are trying to achieve.
> > > If possible you must create a constraint on the table to prevent
> > > conflicting records being entered in the table. If you have a
> > > constraint, then Oracle can not violate it and you can even build
 into
> > > your PL/SQL error handling that if a duplicate error is
 encountered
> > > during an insert, then you update instead.
> > >
> > > On the other hand, if you have some obscure business rule that
 states
> > > that you can have a max of a certain type of record for a
 particular
> > > user id (suggested in your original post), the above won't work.
> > > Oracle can not throw an exception in this case (how can it know
 what
> > > your business rule is?)
> > >
> > > In article <8rdh0a$e92$1_at_nnrp1.deja.com>,
> > > lfto_at_my-deja.com wrote:
> > > > it's more the matter of handling an error in the case that a
 process
> > > > goes to INSERT a record it thinks doesn't exist which was
> > > > determined by a previous SELECT on the user id.
> > > >
> > > > and between the SELECT and INSERT, another process sneaks in the
 record
> > > > with an INSERT. ....and this brings up my question if oracle
 will
> > > > now throw and exception on the initial process because it's
 INSERTING
> > > > a record that now exists....
> > > >
> > > > but according to your email, it seems like the 2nd insert would
 be
 fine.
> > > > does oracle know that two inserts are going on at the same time
 even
> > > > though neither process has committed yet? in this case i could
 see
> > > > oracle marking both processes as valid INSERTS.......and
 allowing
> > > > both processes to INSERT???
> > > > thanks
> > > > mike
> > > > In article <8rdcca$a2p$1_at_nnrp1.deja.com>,
> > > > eldredjames_at_my-deja.com wrote:
> > > > > The second insert will be fine unless in violates a constraint
 in
 the
> > > > > table. The question becomes: Do you need both of these
 records?
 and
> > > > > What are you trying to accomplish?
> > > > >
> > > > > In article <8rd6qg$4ol$1_at_nnrp1.deja.com>,
> > > > > myarch5_at_hotmail.com wrote:
> > > > > > if there are two processes A and B, and they both go to
 INSERT
> > > > > > the same record and process A COMMITS first, will process B
 then
> > > > > > go to COMMIT its INSERT and then bomb because a record
 already
 exists?
> > > > >
> > > > > Sent via Deja.com http://www.deja.com/
> > > > > Before you buy.
> > > > >
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.
> > > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 05 2000 - 02:02:31 CDT