[GENERAL] select for update not locking properly.
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.
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.
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