Horton

Re: Potential G2-item cycles under serializable isolation

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: 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-15T01:30:27Z
Lists: pgsql-bugs 
On Sun, Jun 14, 2020 at 6:37 AM Peter Geoghegan <pg@bowt.ie> wrote:
> That seems reasonable to me.

Thanks!

> The doc patch says "some other systems may even offer Repeatable Read
> and Snapshot Isolation as distinct isolation levels". This is true of
> SQL Server, which has an explicit snapshot isolation level, though
> possibly only because SI was retrofitted to SQL Server 2005 -- I guess
> that that's what you have in mind. Perhaps you should reword this a
> little to convey that you mean a separate isolation mode *and* an
> actually-distinct set of behaviors to go with it.

Ok, changed it to "distinct isolation levels with different behavior",
and pushed this.

I also made the biblio entries look more like the existing examples.

> 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

> behavior that DB2 calls REPEATABLE READ is actually SERIALIZABLE. The
> range of guarantees that you actually get are *enormous* in reality!
> ISTM that REPEATABLE READ is by far the most confusing and ambiguous
> isolation level that the standard describes.

I think IBM might even have changed that between System R and DB2, so
there's probably an interesting story behind that.

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.