From: | Kiriakos Georgiou <kg(dot)postgresql(at)olympiakos(dot)com> |
---|---|
To: | Sam Wong <sam(at)hellosam(dot)net> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SELECT FOR UPDATE could see commited trasaction partially. |
Date: | 2012-03-05 09:21:12 |
Message-ID: | 416D660D-870B-40C1-8E1D-3D912651EA4C@olympiakos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Molesworth | 2012-03-05 09:30:06 | Re: atoi-like function: is there a better way to do this? |
Previous Message | Sam Wong | 2012-03-05 06:41:18 | SELECT FOR UPDATE could see commited trasaction partially. |