Re: select for update not locking properly.

And I forgot to mention my version is:

PostgreSQL 7.0.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66


Joseph Shraibman wrote:
>
> I have a program that does this:
> BEGIN;
> SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
> <my program takes mystring and appends to it>
> UPDATE mytable SET mystring = '<appended string here>' WHERE x = 3 AND y
> = 4;
> END;
>
> But the locking isn't working properly.  I do something that should
> cause 3 different threads to try and do that append, and the first one
> goes through properly, but the second two append to the result of the
> first on only, meaning that the third one didn't see the result of the
> second's append.
>
> I tried to make a simple example that reproduced this, but failed.
>
> I'm guessing that the value of that the select is generated before the
> row is locked, and thus each of my last two threads saw the table after
> the first append, *then* one of them blocked because the other had
> locked the table. It's the only thing I can figure.