Re: Performance Implications of Using Exceptions

Robins Tharakan wrote:
>
> I think James was talking about Sybase. Postgresql on the other hand 
> has a slightly better way to do this.
>
> SELECT ... FOR UPDATE allows you to lock a given row (based on the 
> SELECT ... WHERE clause) and update it... without worrying about a 
> concurrent modification. Of course, if the SELECT ... WHERE didn't 
> bring up any rows, you would need to do an INSERT anyway.
How does that help?

If the matching row doesn't exist at that point - what is there to get 
locked?

The problem is that you need to effectively assert a lock on the primary 
key so that you can update
the row (if it exists) or insert a row with that key (if it doesn't) 
without checking and then inserting and
finding that some other guy you were racing performed the insert and you 
get a duplicate key error.

How does Postgresql protect against this?

James

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance