Understanding Database Isolation Levels: Dirty Reads, Non‑Repeatable Reads & Phantom Reads

1. Read Phenomena and Differences

1) Dirty Read (reading uncommitted data)

Dirty read, also called reading invalid data, occurs when a transaction reads data that another transaction has modified but not yet committed; if the first transaction rolls back, the second transaction has read stale, incorrect data.

2) Non‑repeatable Read (same query returns different data within a transaction)

Non‑repeatable read happens when a transaction reads the same row twice and gets different results because another concurrent transaction modifies and commits the row between the two reads.

3) Phantom Read (inserts cause different result sets)

Phantom read occurs when a transaction reads a set of rows twice and the second read sees additional rows inserted by another transaction, making the result set appear to change like a phantom.

Dirty Read

A transaction reads uncommitted data from another transaction

Non‑repeatable Read

A transaction reads data before and after another transaction commits changes

Phantom Read

A transaction reads a set of rows, another transaction inserts rows, and the second read sees the new rows

2. Lock Implementation Mechanisms

Exclusive Lock

The locked object can be read and modified only by the transaction holding the lock; other transactions cannot acquire any lock on it.

Shared Lock

The locked object can be read by other transactions but not modified; multiple transactions may hold shared locks simultaneously.

3. Isolation Levels

1) Read Uncommitted

Definition

The lowest isolation level; a transaction can read data modified by other transactions that have not yet committed.

Lock Mechanism

Uses a first‑level lock protocol: reads do not acquire locks; writes acquire row‑level shared locks that are released at transaction end.

Operation Logic

Transaction 1 reads a row; Transaction 2 can read or update the same row because Transaction 1 holds no lock. When Transaction 2 updates, Transaction 1 may read the uncommitted version, causing a dirty read. Updates are blocked until the reading transaction finishes.

Drawbacks

Cannot prevent dirty reads, non‑repeatable reads, or phantom reads.

2) Read Committed

Definition

Also called "read committed"; a transaction cannot read data that another transaction has modified but not yet committed.

Lock Mechanism

Uses a second‑level lock protocol: reads acquire row‑level shared locks that are released immediately after the read; writes acquire row‑level exclusive locks held until transaction end.

Operation Logic

During a read, other transactions can also read the same row (shared locks). When a transaction reads a row, it cannot be updated until the read lock is released; after the lock is released, another transaction may update, leading to non‑repeatable reads. Writes are blocked until the writing transaction finishes, thus preventing dirty reads.

Drawbacks

Cannot prevent non‑repeatable reads or phantom reads.

3) Repeatable Read

Definition

A higher level that eliminates non‑repeatable reads by holding shared locks on rows for the entire transaction.

Lock Mechanism

Reads acquire row‑level shared locks held until transaction end; writes acquire row‑level exclusive locks held until transaction end.

Operation Logic

While Transaction 1 reads a row, other transactions can read but cannot modify it because the shared lock is held. Writes are blocked until Transaction 1 commits, preventing non‑repeatable reads. However, phantom reads can still occur.

Drawbacks

Cannot prevent phantom reads.

4) Serializable

Definition

The highest isolation level; it prevents all three read anomalies, including phantom reads.

Lock Mechanism

Reads acquire table‑level shared locks; writes acquire table‑level exclusive locks, both held until transaction end.

Operation Logic

When Transaction 1 reads a table, other transactions may read but cannot update, insert, or delete rows until Transaction 1 finishes. When Transaction 1 updates, all other transactions are blocked from any operation on that table.

Remarks

Transactions cannot read uncommitted changes, cannot modify rows read by another active transaction, and cannot insert rows whose keys fall into the range read by another active transaction.

5) Summary

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

Read Uncommitted

Yes

Yes

Yes

Read Committed

No

Yes

Yes

Repeatable Read

No

No

Yes

Serializable

No

No

No

Note: Most databases default to Read Committed (e.g., SQL Server, Oracle). MySQL defaults to Repeatable Read.