Locking on insert

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 ? Received on Mon Mar 22 2004 - 20:38:17 CST