AW: 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
in any mode that allows more concurrency than full serializable mode.
Yes, definitely.
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.
From my latest tests( see following post), I tend to agree, that this is
extremely sensitive :-(
I do however think that Vadim's patch description was the correct thing to do.
The problem case seems to be when the function is not executed inside a txn.
I was not able to reproduce any failure, when inside txns, since the first update
or select for update blocks the rest.
Andreas
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
Import Notes
Resolved by subject fallback
-----Original Message-----
From: Mikheev, Vadim [mailto:vmikheev@SECTORBASE.COM]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 soThis 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!
Yes, there seems to be a confusion about visibility.
Each query in SERIALIZABLE isolation level uses a common snapshot
for a TX. Each query in READ COMMITTED isolation level uses its own
snapshot. It seems the only difference between SERIALZABLE and READ
COMMITTED. But there's a sort of SERIALIZABLE world inside functions
even under READ COMMITTED mode.
regards,
Hiroshi Inoue