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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!