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.
Received on Wed Oct 04 2000 - 09:53:57 CDT
