If you really stand the possiblity of two threads trying to insert/update
the same row that satisfies a certain criteria. I would say it is a good
candidate for a primary key, or at least a unique key. If both try to
insert, only one will be allowed to actually succeed. The one that fails,
should then try the update again.
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
news:37cc7447.10690191_at_newshost.us.oracle.com...
> A copy of this was sent to "Adrian Bowen" <adrian_at_raptor.win-uk.net>
> (if that email address didn't require changing)
> On Fri, 27 Aug 1999 08:57:26 +0100, you wrote:
>
> >I have a PL/SQL stored procedure which does a SELECT to test for the
> >existence of a row satisfying a set of criteria, then either updates the
> >existing row or inserts a new one. How do I ensure that two concurrent
> >sessions don't both get 'no row found' and try to do the insert? Is there
a
> >standard technique for doing this? At present the only solution I can see
is
> >to use DBMS_LOCK to ensure that only one instance of the SP is executing
at
> >any time.
> >
> >Apologies if this is an entry-level question - any suggestions
appreciated.
> >
> >Adrian Bowen
> >
>
>
> why not something like:
>
> ...
> begin
> insert into T values ( ..... );
> exception
> when dup_val_on_index then
> update t set ......;
> end;
> ....
>
> or
>
> update T set .....
> if ( sql%rowcount = 0 )
> then
> insert into t values .....
> end if;
>
>
> don't do the select, try inserting it -- if it fails row already exists so
> update it
>
> or
>
> try updating it, if you don't find it insert it.
>
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
Received on Mon Aug 30 1999 - 20:25:27 CDT
