On Fri, 2007-06-01 at 20:06 +0200, Florian G. Pflug wrote:
> Simon Riggs wrote:
> > On Mon, 2007-05-28 at 19:56 -0400, Bruce Momjian wrote:
> >> Added to TODO:
> >>
> >> * Fix self-referential UPDATEs seeing inconsistent row versions in
> >> read-committed mode
> >>
> >> http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php
> >>
> >
> > I'm sorry guys but I don't agree this is a TODO item.
>
> Maybe the TODO suggested has a too narrow focus, but I think that
> that *something* has to be done about this.
>
> > IMHO this follows documented behaviour, even if y'all are shocked.
> Yes, but documented != sensible && documented != intuitive &&
> documented != logical.
I've regrettably spent too many years with locking databases, so to me
the behaviour is perfectly logical, and intuitive, because I didn't
expect a lock avoiding strategy to be "free". We have a way of bypassing
locks, but there is no way to bypass the need to think about what the
answer will be if you do chose to bypass them.
This is just a classic database problem. Many books and courses bypass
these issues because they appear to be solved. Every couple of years
people go ballistic when they discover this kind of stuff exists; its
made the papers and SIGMOD too. I don't mean to dismiss everybody's
concerns, but it does seem I have a different perspective on them.
> > In the case of concurrent UPDATEs the second UPDATE will normally
> > perform the subSELECT then hang waiting to perform the UPDATE. If you
> > use FOR SHARE the query will hang on the subSELECT (i.e. slightly
> > earlier), which makes the second query return zero rows, as some of you
> > were expecting.
>
> Sure, but with a similar argument you could question the whole
> update-in-read-committed-mode logic. After all, you wouldn't need
> that logic if you always obtained a share lock on the rows to be updated
> *before* you started updating them.
Document it better if you will, or add an option that would allow you to
set the default to always use read locks, but there's just nothing
actually wrong with the current behaviour.
BTW, DB2 has just such an option, IIRC, but the equivalent "Repeatable
Read" (doesn't mean same thing as the ANSI phrase) mode is not
recommended for normal use. You'll then run up against the need to
optimise the locking further and end up with "Cursor Stability" mode.
Thats a lot of work for something few people will use in practice.
Read Committed == Show me the data, whether or not its been updated
Bear in mind this has nothing to do with self-referencing joins, its a
problem-of-misunderstanding of any UPDATE or DELETE with a sub-select
that references a table that can be updated concurrently.
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com