Isolation Levels - part II: Characteristics and use-case

In the previous post I introduced the importance of understanding isolation levels. Before a more detailed description, here is the principal characteristics of each isolation level.

---

Descriptions of isolation levels are often incomplete or incorrect because the SQL Standard defined them when not all potential anomalies were clearly articulated. Only three phenomena were described. Furthermore, the definition relied on a possible implementation involving locks for both reads and writes. However, in practice, almost no database implements isolation levels using such aggressive locks, as it would not be performant and scalable.

Modern databases utilize Multi-Version Concurrency Control (MVCC), which offers read isolation without locks. They add some locking or conflict detection on top of it. This article aims to elucidate isolation levels for developers, focusing on databases that implement MVCC and providing all SQL isolation levels on top of it.

The summary is in the following table, and more explanations will follow in the next posts.

MVCC Isolation level	Serializable Snapshot Isolation	Snapshot Isolation	Read Committed	NoSQL-like single-object consistency
Set by (in YugabyteDB or PostgreSQL)	Serializable	Repeatable Read & Read Only	Read Commited & Read Uncommited	Non-Transactional write
Use case	OLTP complex transactions with no explicit locking, and guaranteed without anomalies	OLTP short transactions with retry logic, or long read-only transactions	OLTP preventing anomalies with explicit locking rather than retry logic	Fast data-ingest that doesn't require atomic visibility
Characteristics
Read Time	start of transaction	start of transaction	start of statement	Depends on transaction isolation level
Write Time	commit (if no anomalies)	commit (if no anomalies)	commit (if no deadlocks)	each row has its write time
Development Constraints	retry logic	retry logic for basic anomalies and explicit locking if possibility of write skew	explicit locking to avoid read and write skew and lost update, and statement ordering for deadlocks	enable non-transactional write (implicit only for sequences)
Implementation in MVCC	Snapshot Isolation + read intents	Snapshot Isolation	Snapshot per statement, statement restart	row timestamp is the write time rather than the commit time
Implementation specificities
Oracle	still shows some anomalies	set with "serializable"	The default and most used waits on enqueue update restarts to get consistent result in RC	(in-Memory fast ingest)
SQL Server	predicate lock with range locks	Only with read locks	The default with read locks No read locks when using MVCC snapshots no restart (lock and wait)
MySQL	predicate lock with range locks	default but not fully complies with RR
PostgreSQL	keeps track of predicates anomaly detection at commit all conflicting transactions must be serializable		waits on enqueue no restart (can be inconsistent) default
YugabyteDB	keeps track of read intents (distributed) anomaly detection on writes all conflicting transactions must be serializable	the default only when RC is disabled	the default (when RC is enabled) wait queue, update restarts, deadlock detection

In the next posts of this series, I dig into more specific details about certain SQL databases while excluding those that don't utilize MVCC. These databases require locking for all operations, including reads that can block writes. The classical isolation levels encompass dirty reads, apply to these databases, and can be found in older literature.

I primarily focus on databases that implement all SQL isolation levels and exclude those not compatible with most SQL applications built with Read Committed.

---

The topic is complex, and some databases may have changed their behavior through versions. Please comment if you find anything inexact.