Isolation Levels - part XII: To go further

#sql #database

SQL isolation levels are typically characterized by their effects, such as anomalies or phenomena, or by their implementation, such as lock duration. However, this approach doesn't provide much guidance to developers on when to use each level, and that's what I tried to address in this series.

ANSI SQL does not describe this topic accurately.
Here's a more detailed explanation of the issue:
A Critique of ANSI SQL Isolation Levels

Once correctly described, those anomalies can be tested, and Martin Kleppmann has created a testing suite for it: https://github.com/ept/hermitage.

The complete description of the isolation level is more complex than what has been described here.
Here is a comprehensive description:
Consistency Models - Kun Xi

You may wonder how Oracle can enforce referential integrity without row share locks and serializable isolation level. The magic relies on using the index on the foreign key as a range lock.
I had put many details in this old presentation:
Indexing Foreign Keys in Oracle

YugabyteDB has one of the most extensive implementations available, with all levels like PostgreSQL, but additionally solves the Read Committed inconsistency with statement restarts like Oracle.
Here is the documentation:
Isolation levels | YugabyteDB Docs

Every database behaves differently even when using an isolation level that has the same name. For example you may be surprised by these simple examples by Daniel Vérité: Isolation Repeatable Read in PostgreSQL versus MySQL.
You can migrate an application without changes only with truly compatible databases, like between PostgreSQL and YugabyteDB. Being only "wire-compatible" may work but cause wrong results and data corruption.

In this final post of the series, I want to make it clear that my aim when comparing different database implementations is to understand them better. Please note that I am not trying to determine which database is better or worse. All of the databases mentioned in this series are utilized for running critical OLTP applications. For instance, some people like to make jokes about Oracle, but the lack of true Serializable doesn't affect the consistency of existing applications in any way. To avoid conflicts, applications written for Oracle use explicit locking such as SELECT FOR UPDATE, LOCK TABLE, and DBMS_LOCK. Remember that explicit locking was ignored by the original description of transaction isolation.