Thread: 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
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