Horton

Re: Potential G2-item cycles under serializable isolation

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Kyle Kingsbury <aphyr@jepsen.io>
Cc: Thomas Munro <thomas.munro@gmail.com>, Andres Freund <andres@anarazel.de>, Kevin Grittner <kgrittn@gmail.com>, Jeff Davis <pgsql@j-davis.com>, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2020-06-15T17:15:59Z
Lists: pgsql-bugs 
On Mon, Jun 15, 2020 at 5:42 AM Kyle Kingsbury <aphyr@jepsen.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

Commits

  1. Doc: Add references for SI and SSI.

  2. Improve comments for [Heap]CheckForSerializableConflictOut().

  3. Avoid update conflict out serialization anomalies.

  4. Implement genuine serializable isolation level.