Re: SELECT FOR UPDATE could see commited trasaction partially.

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