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