Re: two inserts at same time

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. Received on Tue Oct 03 2000 - 17:48:19 CDT