Re: Best practices: MERGE

On Tue, Mar 08, 2005 at 11:45:19AM +0800, Christopher Kings-Lynne wrote:
> >The "correct" solution, as far as I can tell, is to acquire a LOCK
> >on the table IN SHARE MODE at the beginning of the transaction, but
> >this has (at least for many applications) unacceptable performance
> >characteristics.  Accepting that there is a slight risk of a race
> >condition when *not* locking the table at the beginning of the
> >transaction, what procedure minimizes this risk and recovers well
> >from said race condition, should it occur?
> 
> IN SHARE MODE is not enough, you can get deadlocks.  You require IN
> SHARE ROW EXCLUSIVE MODE.  other than that, it's a sucky solution
> because it breaks concurrency.  In pgsql 8, you can do it using
> pl/pgsql exception handling.

Luckily, PG 8 is available for this.  Do you have a short example?

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!