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.
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.
Originally published at https://dev.to on November 28, 2023.
