Isolation Levels - part II: Characteristics and use-case
Tags: sql, database
Published: 2023-11-28 17:35:00

In the [previous post](https://dev.to/franckpachot/isolation-levels-part-i-introduction-bd5) 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.

<table>
  <tr>
   <td><strong>MVCC Isolation level</strong>
   </td>
   <td><strong>Serializable Snapshot Isolation</strong>
   </td>
   <td><strong>Snapshot Isolation</strong>
   </td>
   <td><strong>Read Committed</strong>
   </td>
   <td><strong>NoSQL-like single-object consistency</strong>
   </td>
  </tr>
  <tr>
   <td colspan="5" >
   </td>
  </tr>
  <tr>
   <td><strong>Set by (in YugabyteDB or PostgreSQL)</strong>
   </td>
   <td><strong>Serializable</strong>
   </td>
   <td><strong>Repeatable Read & Read Only</strong>
   </td>
   <td><strong>Read Commited & Read Uncommited</strong>
   </td>
   <td><strong>Non-Transactional write</strong>
   </td>
  </tr>
  <tr>
   <td><strong>Use case</strong>
   </td>
   <td>OLTP complex transactions with no explicit locking, and guaranteed without anomalies
   </td>
   <td>OLTP short transactions with retry logic, or long read-only transactions
   </td>
   <td>OLTP preventing anomalies with explicit locking rather than retry logic
   </td>
   <td>Fast data-ingest that doesn't require atomic visibility
   </td>
  </tr>
  <tr>
   <td colspan="5" ><strong>Characteristics</strong>
   </td>
  </tr>
  <tr>
   <td><strong>Read Time</strong>
   </td>
   <td>start of transaction
   </td>
   <td>start of transaction
   </td>
   <td>start of statement
   </td>
   <td>Depends on transaction isolation level
   </td>
  </tr>
  <tr>
   <td><strong>Write Time</strong>
   </td>
   <td>commit (if no anomalies)
   </td>
   <td>commit (if no anomalies)
   </td>
   <td>commit (if no deadlocks)
   </td>
   <td>each row has its write time
   </td>
  </tr>
  <tr>
   <td><strong>Development Constraints</strong>
   </td>
   <td>retry logic
   </td>
   <td>retry logic for basic anomalies and explicit locking if possibility of write skew
   </td>
   <td>explicit locking to avoid read and write skew and lost update, and statement ordering for deadlocks
   </td>
   <td>enable non-transactional write
<p>
(implicit only for sequences) 
   </td>
  </tr>
  <tr>
   <td><strong>Implementation in MVCC</strong>
   </td>
   <td>Snapshot Isolation + read intents
   </td>
   <td>Snapshot Isolation
   </td>
   <td>Snapshot per statement, statement restart
   </td>
   <td>row timestamp is the write time rather than the commit time
   </td>
  </tr>
  <tr>
   <td colspan="5" ><strong> 
Implementation specificities</strong>
   </td>
  </tr>
  <tr>
   <td><strong>Oracle</strong>
   </td>
   <td>
<ul>

<li>still shows some anomalies
</li>
</ul>
   </td>
   <td>
<ul>

<li>set with "serializable"
</li>
</ul>
   </td>
   <td>
<ul>

<li>The default and most used

<li>waits on enqueue

<li>update restarts to get consistent result in RC
</li>
</ul>
   </td>
   <td>
    (in-Memory fast ingest)
   </td>
  </tr>
  <tr>
   <td><strong>SQL Server</strong>
   </td>
   <td>
<ul>

<li>predicate lock with range locks
</li>
</ul>
   </td>
   <td>
<ul>

<li>Only with read locks
</li>
</ul>
   </td>
   <td>
<ul>

<li>The default with read locks

<li>No read locks when using MVCC snapshots

<li>no restart (lock and wait) 
</li>
</ul>
   </td>
   <td>
   </td>
  </tr>
  <tr>
   <td><strong>MySQL</strong>
   </td>
   <td>
<ul>

<li>predicate lock with range locks
</li>
</ul>
   </td>
   <td>
<ul>

<li>default but not fully complies with RR
</li>
</ul>
   </td>
   <td>
   </td>
   <td>
   </td>
  </tr>
  <tr>
   <td><strong>PostgreSQL</strong>
   </td>
   <td>
<ul>

<li>keeps track of predicates

<li>anomaly detection at commit

<li>all conflicting transactions must be serializable
</li>
</ul>
   </td>
   <td>
   </td>
   <td>
<ul>

<li>waits on enqueue

<li>no restart (can be inconsistent)

<li>default
</li>
</ul>
   </td>
   <td>
   </td>
  </tr>
  <tr>
   <td><strong>YugabyteDB</strong>
   </td>
   <td>
<ul>

<li>keeps track of read intents (distributed)

<li>anomaly detection on writes

<li>all conflicting transactions must be serializable
</li>
</ul>
   </td>
   <td>
<ul>

<li>the default only when RC is disabled
</li>
</ul>
   </td>
   <td>
<ul>

<li>the default (when RC is enabled)

<li>wait queue, update restarts, deadlock detection 

</li>
</ul>
   </td>
   <td>
   </td>
  </tr>
</table>


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.