This is correct behavior with MVCC. Do a 'select * from x' in thread 2 and to understand why. The select for update in thread 2 sees the data in table x as it was prior to thread 1 committing, thus it won't see the row with a=2.
For further suggestions you'll have to explain what you are logically trying to accomplish. Kiriakos On Mar 5, 2012, at 1:41 AM, Sam Wong wrote: > Hi, > > I hit a UPDATE/LOCK issue in my application and the result has surprised me > somewhat… > And for the repro, it boils down into this: > --- > CREATE TABLE x (a int, b bool); > INSERT INTO x VALUES (1, TRUE); > COMMIT; > > _THREAD 1_: > BEGIN; > UPDATE x SET b=FALSE; > INSERT INTO x VALUES (2, TRUE); > > _THREAD 2_: > BEGIN; > SELECT * FROM x WHERE b=TRUE FOR UPDATE; -- It will be blocked, as expected > > _THREAD 1_: > COMMIT; > > _THREAD 2_ will be unblocked. It will return no rows. > I expect it to return (2, TRUE) instead, when I design the program. > > If I issue the same SELECT query in THREAD 2 right now, it does indeed return > (2, TRUE). > > For the same SQL statement, in MySQL-InnoDB, it does return (2, TRUE) in the > first SELECT. > > I understand why this happens in PgSQL, (because it first limited the > selection and locked the row, upon unlock it recheck the condition)… > > I don’t like THERAD 2 only see half of the fact of the committed transaction > (it see the effect of the update but not the insert), is there anything I > could do? > > I considered: > * ISOLATION serialization – but the thread 2 would abort as deadlock… > * Both thread do: LOCK TABLE x AS SHARE ROW EXCLUSIVE MODE – does resolve my > issue but it creates a big lock contention problem, and relies on app to do > the right thing. > * Advisory lock – pretty much the same, except that I could unlock earlier to > make the locking period shorter, but nevertheless it’s the whole table lock… > > Thoughts? > > Thanks, > Sam
