Re: Potential G2-item cycles under serializable isolation

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Thomas Munro" <thomas(dot)munro(at)gmail(dot)com>
Cc: "Peter Geoghegan" <pg(at)bowt(dot)ie>,"Andres Freund" <andres(at)anarazel(dot)de>,"Kyle Kingsbury" <aphyr(at)jepsen(dot)io>,"Kevin Grittner" <kgrittn(at)gmail(dot)com>,"Jeff Davis" <pgsql(at)j-davis(dot)com>,"PostgreSQL mailing lists" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Potential G2-item cycles under serializable isolation
Date: 2020-06-15 12:30:23
Message-ID: 075a9f7b-af0b-487e-94ae-f89fecbe149b@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Kyle Kingsbury 2020-06-15 12:42:38 Re: Potential G2-item cycles under serializable isolation
Previous Message Sandeep Thakkar 2020-06-15 07:26:19 Re: BUG #16460: Error when executing REFRESH MATERIALIZED VIEW WITH DATA;