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

> -----Original Message-----
> From: Mikheev, Vadim [mailto:vmikheev(at)SECTORBASE(dot)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 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!

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