RE: AW: Re: [SQL] possible row locking bug in 7.0.3 & 7 .1

> > 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

This is not correct - SELECT has same rules. Are you able to reproduce
this bad behaviour without running queries in functions? I assume
the answer is NO. I just overlooked function case two years ago.
But SELECT/UPDATE visibility rules are same!
Ever wonder why in SERIALIZABLE mode UPDATE/SELECT_FOR_UPDATE cause
rollback in the event of concurrent modification? Because of concurrent
modifications make visibility of SELECT and UPDATE different and this
means *unconsistent* view of database for applications.
In READ COMMITTED mode a query must see changes made by previous
queries - the only one rule we have to follow to provide consistent
result for applications.

> 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.

I agreed to leave it as Known Bug for 7.1.

> A first question: where did the MVCC rules come from
> originally, anyway?

From the fact that I've used Oracle before Postgres'95,
liked it and had time to read its documentation -:)

> Is there any academic research to look at?

There is academic Theorem of Serializability but it's
different from SERIALIZABLE mode definitions in standard.
Probably, this difference was caused by lobbying from
Oracle...

Vadim