Understanding Transaction Isolation Levels and Dirty Reads Across Databases

Key Points

Considering only ACID or non‑ACID aspects is insufficient; you must know which transaction isolation levels your database supports.

Some databases claim "eventual consistency" but may return inconsistent results for repeated queries.

Compared with the database you seek, some provide higher isolation levels.

Dirty reads can cause a record to have two versions or be completely lost.

Repeatedly re‑executing the same query within a transaction can lead to phantom reads.

MongoDB recently made headlines because its core developer realized that MongoDB performs dirty reads by default.

This article explains what transaction isolation levels and dirty reads are, and shows how popular databases implement them.

ANSI SQL defines four standard isolation levels: Serializable, Repeatable Read, Read Committed, and Read Uncommitted.

Many databases default to Read Committed, which prevents seeing in‑flight data by acquiring temporary read locks that are held until the transaction commits.

If you need the same result for multiple reads within a transaction, the database holds read locks for the entire duration; this is automatic with the Repeatable Read level.

Repeatable Read still allows phantom reads, where new rows matching a WHERE clause appear on a subsequent execution.

Serializable isolation uses range locks to prevent new rows from being added that match the query, eliminating phantom reads.

Higher isolation levels generally reduce performance due to lock contention; some databases also support Read Uncommitted (NOLOCK) which ignores locks and permits dirty reads.

Problems Caused by Dirty Reads

Before discussing dirty reads, understand that a table is a logical structure; data is stored in one or more indexes (clustered index or heap). Inserts affect each index; updates modify the index entries for changed columns, often requiring a delete‑then‑insert on each index.

Below are diagrams of a typical table and the execution plan for updating indexed columns (images illustrate the concept).

In SQL Server, PK denotes the primary key (often the clustered index) and IX denotes a non‑clustered index.

Dirty reads cause inconsistencies in several ways:

Uncommitted reads can see rows that are later rolled back, returning data that never existed.

Data movement during updates can lead to double reads of the same record with different values (e.g., reading a customer in California then Texas after an update).

Record loss can occur if a row is moved between partitions and a subsequent query no longer finds it, as happened in MongoDB.

Dirty reads can interfere with sorting operations when the execution plan caches pointers to rows that are later updated.

Snapshot Isolation (Row‑Level Versioning)

To avoid dirty reads while maintaining performance, many databases offer snapshot isolation. Under snapshot isolation, a transaction sees a consistent snapshot of the data as of its start time.

Implementation involves creating temporary copies of modified rows rather than relying solely on locks, hence the term "row‑level version control." Many databases that support snapshot semantics automatically use it when a "Read Committed" isolation level is requested.

Transaction Isolation Levels in SQL Server

SQL Server supports all four ANSI levels plus an explicit snapshot level. Read Committed may use snapshot semantics depending on the READ_COMMITTED_SNAPSHOT setting. Before enabling this option, thorough testing is required because while Read Committed can improve read performance, it may degrade write performance, especially when tempdb resides on slow storage. The infamous NOLOCK hint forces an uncommitted read, effectively setting the transaction to Read Uncommitted. It was common in SQL Server 2000 and earlier, but is now discouraged.

Transaction Isolation Levels in PostgreSQL

PostgreSQL claims to support all four ANSI levels, but in practice only three are available. Requests for Read Uncommitted are silently upgraded to Read Committed, so PostgreSQL never allows dirty reads. When you select Read Uncommitted, you actually get Read Committed; PostgreSQL’s implementation of Repeatable Read prevents dirty reads, making the effective isolation stricter than requested, which the SQL standard permits. PostgreSQL does not expose an explicit snapshot isolation level; snapshot semantics are automatically applied when using Read Committed because PostgreSQL was designed around multiversion concurrency control (MVCC). Prior to version 9.1, PostgreSQL lacked a true Serializable level and would downgrade to Repeatable Read; modern supported versions no longer have this limitation.

Transaction Isolation Levels in MySQL

InnoDB defaults to Repeatable Read and supports all four ANSI levels. Read Committed in InnoDB uses snapshot semantics. MyISAM does not support transactions at all; it uses table‑level read/write locks, though inserts may sometimes bypass the lock.

Transaction Isolation Levels in Oracle

Oracle supports three levels: Read Committed (default, using snapshot semantics), Serializable, and Read‑Only. Oracle never allows uncommitted reads, so dirty reads are impossible. Serializable is the only level that provides behavior equivalent to Repeatable Read; Oracle does not list Repeatable Read as a separate option. Read‑Only transactions see only data committed before the transaction starts and prohibit INSERT, UPDATE, and DELETE statements.

Transaction Isolation Levels in DB2

DB2 defines four levels: Repeatable Read, Read Stability, Cursor Stability, and Uncommitted Read. These do not map one‑to‑one with ANSI terminology.

Repeatable Read corresponds to ANSI Serializable (no dirty reads).

Read Stability corresponds to ANSI Repeatable Read.

Cursor Stability implements Read Committed and is the default; snapshot semantics are available in DB2 9.7 and later.

Uncommitted Read behaves like SQL Server’s uncommitted read, allowing dirty reads; it is recommended only for read‑only tables.

Transaction Isolation Level in MongoDB

MongoDB does not support multi‑document transactions. Single‑document operations are atomic, and two‑phase commit provides only transaction‑like semantics. During a two‑phase commit or rollback, applications may observe intermediate (partial) data, effectively giving MongoDB dirty‑read semantics with the possibility of duplicate or lost records.

Transaction Isolation Level in CouchDB

CouchDB also lacks transactions but uses MVCC to avoid dirty reads. Read requests always see the latest snapshot at the start of the request, providing semantics equivalent to a snapshot‑based Read Committed level.

Transaction Isolation Level in Couchbase Server

Couchbase Server does not provide isolation for indexes; primary index updates are immediate, while secondary indexes are updated lazily. Although documentation does not explicitly state it, the indexing process appears to use snapshots, which may mitigate dirty reads, but the delayed secondary index updates prevent true Read Committed isolation. Couchbase does not support transactions, though explicit locks can be used for up to 30 seconds before they are automatically released.

Transaction Isolation Level in Cassandra

Cassandra 1.0 isolated even single‑row writes; fields are updated individually, preventing mixed‑value reads. Since version 1.1, Cassandra offers "row‑level isolation," equivalent to an uncommitted read level; higher isolation levels are not provided.

Understanding Your Database's Transaction Isolation Level

As the examples demonstrate, evaluating a database solely from an ACID or non‑ACID perspective is insufficient; you must know which isolation levels are supported and under what circumstances they are applied. Source: http://www.uml.org.cn/sjjm/201612052.asp