SELECT FOR UPDATE could see commited trasaction partially.

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