Re: select for update not locking properly.

Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> 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.

Can't duplicate it here. I did:

<in psql window 1>

regression=# create table mytable(mystring text, x int, y int);
CREATE
regression=# insert into mytable values('z',1,4);
INSERT 399867 1
regression=# insert into mytable values('foo',3,4);
INSERT 399868 1
regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
mystring
----------
foo
(1 row)

<in psql window 2>

regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
[ psql hangs ]

<in psql window 3>

regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
[ psql hangs ]

<back to psql window 1>

regression=# UPDATE mytable SET mystring = 'foo bar' WHERE x = 3 AND y = 4;
UPDATE 1
regression=# end;
COMMIT
regression=#

<psql window 3 now responds>

mystring
----------
foo bar
(1 row)

regression=# UPDATE mytable SET mystring = 'foo bar baz' WHERE x = 3 AND y = 4;
UPDATE 1
regression=# end;
COMMIT
regression=#

<psql window 2 now responds>

mystring
-------------
foo bar baz
(1 row)

regression=#

Looks pretty cool to me...

regards, tom lane