Isolation Levels — part XII: To go further
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
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 implicit locking such as SELECT FOR UPDATE, LOCK TABLE, and DBMS_LOCK. Remember that implicit locking was ignored by the original description of transaction isolation.
Originally published at https://dev.to on December 18, 2023.
