[GENERAL] select for update not locking properly.

[Joseph Shraibman]

I have a program that does this:
BEGIN;
SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;

UPDATE mytable SET mystring = '' 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.

Re: [GENERAL] select for update not locking properly.

[Joseph Shraibman]

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;
> 
> UPDATE mytable SET mystring = '' 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.

Re: [GENERAL] select for update not locking properly.

[Tom Lane]

Joseph Shraibman <[EMAIL PROTECTED]> 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:



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)



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



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



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



 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=#



  mystring
-
 foo bar baz
(1 row)

regression=#

Looks pretty cool to me...

regards, tom lane