Re: Revisited: Transactions, insert unique.

At 10:17 PM 24-04-2000 -0400, Mike Mascari wrote:
>Lincoln Yeoh wrote:
>PostgreSQL implements SELECT...FOR UPDATE to allow for the
>sequence you'be described:
>
>Session 1:
>
>BEGIN;
>SELECT webuser FROM webusers WHERE webuser = 'webuser1';
>
>Session 2:
>
>BEGIN;
>UPDATE webusers SET webuser = 'webuser2' WHERE webuser =
>'webuser1';*

The 6.5.3 running on my site does not block for the case I described which
has an INSERT after the select.

begin;
select from mytable where field=value for update;
if rows=0 {
    insert into mytable (field,field2) values (value,value2);
} else {
    update mytable set field=value, field2=value2;
}
commit;

Tested on 6.5.3.
Did a select for update in two concurrent transactions, they did not block,
and both inserts went through (if there was a unique, one of the
transactions would have had to be rolled back and redone from scratch).

If the behaviour is different for 7.0, I'm interested!

Cheerio,

Link.