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 |
|
|
|
(in-Memory fast ingest) |
| SQL Server |
|
|
|
|
| MySQL |
|
|
||
| PostgreSQL |
|
|
||
| YugabyteDB |
|
|
|
|
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.
Top comments (0)