There already is quite an extensive discussion of how FOR UPDATE
behaves including these kinds of violations.
What you propose is interesting though. It would have been impossible
before subtransactions but it's doable now. Still the performance
might be unusable for complex queries. It's basically generalizing the
logic a serializable transaction would take to a read committed command.
--
Greg
On 24 Jan 2009, at 18:50, Jeff Davis <pg...@j-davis.com> wrote:
This post is a follow-up of an off-list discussion with Nathan Boley.
All references to FOR UPDATE apply to FOR SHARE as well.
create table a(i int, j int);
insert into a values(1, 10);
insert into a values(2, 10);
insert into a values(3, 10);
insert into a values(4, 20);
insert into a values(5, 20);
insert into a values(6, 20);
Session 1:
BEGIN;
UPDATE a SET j = (j - 10) WHERE i = 3 OR i = 4;
Session 2:
SELECT * FROM a WHERE j = 10 FOR UPDATE; -- blocks
Session 1:
COMMIT;
Session 2 (results):
i | j
---+----
1 | 10
2 | 10
(2 rows)
There you see a snapshot of the table that never existed. Either the
snapshot was taken before the UPDATE, in which case i=3 should be
included, or it was taken after the UPDATE, in which case i=4 should
be
included. So atomicity is broken for WHERE.
So, FOR UPDATE produces known incorrect results for:
* WHERE
* ORDER BY:
http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php
* LIMIT
* SAVEPOINT/ROLLBACK TO
And I expect we'll find more, as well.
It's not simply that FOR UPDATE works strangely in a couple isolated
edge cases, as the docs imply. It works contrary to the basic
assumptions that people familiar with PostgreSQL rely on. Furthermore,
the people using FOR UPDATE are likely to be the people who care about
these edge cases.
I think that FOR UPDATE deserves a jarring disclaimer in the docs if
we
maintain the current behavior. Something along the lines of "this does
not follow normal transactional semantics and will produce incorrect
results". Existing users may find current FOR UPDATE behavior useful
to
avoid full-table locks, but they should be properly warned.
If there is a fix, the only thing that I can imagine working (aside
from
a full table lock) would be to iteratively acquire new snapshots and
re-run the query until no concurrent transaction interferes.
Regards,
Jeff Davis
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
