Re: Potential G2-item cycles under serializable isolation

On Mon, Jun 15, 2020 at 5:42 AM Kyle Kingsbury <aphyr(at)jepsen(dot)io> wrote:
> On 6/14/20 9:30 PM, Thomas Munro wrote:
> > By my reading of their manual, MySQL (assuming InnoDB) uses SI for
> > REPEATABLE READ just like us, and it's also their default level.
> >
> > https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
>
> That can't be right. MySQL repeatable read allows lost update, write skew, and
> at least some kinds of read skew: it's got to be weaker than SI and also weaker
> than RR. I think it's actually Monotonic Atomic View plus some read-only
> constraints?

At first glance it seemed to me that MySQL's repeatable read must be
more or less the same as Postgres' repeatable read; there is only one
snapshot in each case. But it's very different in reality, since
updates and deletes don't use the transaction snapshot. Worst of all,
you can update rows that were not visible to the transaction snapshot,
thus rendering them visible (see the "Note" box in the documentation
for an example of this). InnoDB won't throw a serialization error at
any isolation level.

I think that means that InnoDB's RR mode could "render" *some* of the
rows concurrently inserted by another transaction (those that our xact
happened to update) as visible, while leaving other rows inserted by
that same other transaction invisible. That kind of behavior seems
really hazardous to me. It almost seems safer to use RC mode, since
using a new snapshot for each new statement is "more honest".
Presumably RC mode doesn't exhibit the inconsistency I described -- at
least all of the rows that were concurrently inserted by that other
xact become visible together.

--
Peter Geoghegan