Horton

Re: Potential G2-item cycles under serializable isolation

Daniel Verite <daniel@manitou-mail.org>

View thread View on postgr.es
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Thomas Munro" <thomas.munro@gmail.com>
Cc: "Peter Geoghegan" <pg@bowt.ie>,"Andres Freund" <andres@anarazel.de>,"Kyle Kingsbury" <aphyr@jepsen.io>,"Kevin Grittner" <kgrittn@gmail.com>,"Jeff Davis" <pgsql@j-davis.com>,"PostgreSQL mailing lists" <pgsql-bugs@lists.postgresql.org>
Date: 2020-06-15T12:30:23Z
Lists: pgsql-bugs 
	Thomas Munro wrote:

> > Speaking of comparing behaviors across systems, the behavior that
> > MySQL calls REPEATABLE READ mode is actually READ COMMITTED, while the
> 
> 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

With InnoDB, a RR transaction that just reads has the same behavior
than with Postgres, in the sense that it does consistent reads across
all tables.

But it should be noted that when concurrent writes are involved,
InnoDB does not confine the transaction to its snapshot as Postgres
does.
In particular, rows that a simple SELECT can't see because of RR
visibility rules are found by UPDATEs or INSERTs, and are somehow
incorporated into the RR transaction.
If InnoDB's RR is based on Snapshot Isolation, what it does with
it seems to be in violation of Snapshot Isolation as seen by postgres,
so it's somewhat unfair/confusing to use the same term for both.

From the user point of view, Repeatable Read in InnoDB exhibits
anomalies that are not possible with Postgres' Repeatable
Read.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

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.