"Matthew Keene" <dfg778_at_yahoo.com.au> wrote in message
news:bb27253c.0403221838.7483cc7_at_posting.google.com...
> We have a problem with a program which attempts to do a classic
> 'upsert' style of processing as follows:
>
> Select from target table based on primary key
> If record found, compare with input values
> If all values match, do nothing
> Else update record
> Else insert record
>
> The problem is when two transactions try to simultaneously insert the
> same record. The first transaction will issue the select and not
> retrieve a row (because it's not there), and will proceed to do the
> insert. The second transaction will issue the select and in this case
> it will still not retrieve a row, not because it's not there, but
> because Oracle's multi-versioning allows the transaction to see the
> table as it was before transaction #1 began its update. Transaction
> #2 now issues the insert, which blocks until transaction #1 commits,
> at which time it receives a duplicate insert exception.
>
> Our current thinking is that the only way to get around this is to
> change the processing so that a duplicate exception from the insert
> would cause an update to be performed, but I'm wondering whether
> there's a more sophisticated way to do this. My ideal would be to be
> able to bypass the multi-versioning and have the select block until
> the insert was committed, and then retrieve the row which had been
> inserted. Does anybody know any way to do this ?
What version of Oracle? In 9i there is an "upsert" functionality (see merge)
Jim
Received on Mon Mar 22 2004 - 20:43:17 CST
