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
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:
Show quoted text
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