Thread: AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1

> > > I doubt if it's a bug of SELECT. Well what
> > > 'concurrent UPDATE then SELECT FOR UPDATE +
> > > SELECT' return ?
> > 
> > I'm going to add additional check to heapgettup and
> > heap_fetch:
> >
> 
> SELECT seems to be able to return a different result
> from that of preceding SELECT FOR UPDATE even after
> applying your change.

Only if you left this cursor position without doing an actual update
(i.e. after fetch next). The select for update is only supposed to guard 
the current cursor position. Once you leave without modification
another session can be allowed to update.  
This is how it is supposed to react in read committed
mode. If you don't like this you need repeatable read.

The example given is of questionable value, since a select for update 
without a cursor in read committed mode does not need to behave any different 
than a simple select without for update.

> SELECT doesn't seem guilty but the result is far 
> from intuitive.

It is intuitive. The bug was iirc, that you saw 2 versions of the same row
in the second select statement (= 2 rows returned by second select).
Vadim's patch will let you see only the newer row.

> It seems impossoble for all queires inside such
> a function to use a common snapshot.

In read committed they are not required to !

It looks like a lot of people on the list are absolute fans
of repeatable read isolation :-) Not me, I know a lot of applications
where committed read, or even read uncommitted makes a lot more 
sense.

Andreas

Zeugswetter Andreas SB  <ZeugswetterA@Wien.Spardat.at> writes:
> It is intuitive. The bug was iirc, that you saw 2 versions of the same row
> in the second select statement (= 2 rows returned by second select).

I think we should be extremely wary of assuming that we have a clear
characterization of "what the bug is", let alone "how to fix it".
The real issue here is that SELECT has different MVCC visibility rules
from UPDATE and SELECT FOR UPDATE.  I suspect that that *must* be so
in any mode that allows more concurrency than full serializable mode.
Thus, the question we are really facing is how we might alter the
visibility rules in a way that will make the results more intuitive
and/or useful while still allowing concurrency.

This will take thought, research and discussion.  A quick fix is the
last thing that should be on our minds.

A first question: where did the MVCC rules come from originally, anyway?
Is there any academic research to look at?
        regards, tom lane