Re: MERGE vs REPLACE

Josh Berkus wrote:
> Jaime,
> 
> > why? seems that REPLACE only work if there are at least one row
> > matching...
> 
> Scenario:
> 
> session1: REPLACE .... 1           
>                   session2:  REPLACE ..... 1
> session1: check to see that "1" exists .... no
>                   session2: check to see that "1" exists .... no
> session1: INSERT 1
>                   session2: INSERT 1 .... ERROR
> 
> Get the picture?  The only way to avoid a race condition is to be able to 
> do "predicate locking", that is to lock the table against any data write 
> matching that predicate.

Isn't the standard practice to do the INSERT, and if that fails, do
the UPDATE?  In fact, if someone is already doing a INSERT inside a
transaction, it will wait for the transaction to complete so you can
then do the UPDATE, or complete the INSERT if the transaction aborted.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073