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