Transaction Isolation Levels#

Every database transaction makes a promise: your operations will be atomic, consistent, isolated, and durable. But isolation is not binary — it exists on a spectrum, and choosing the right level determines whether your application sees stale data, phantom rows, or grinds to a halt under contention.

ACID: The Foundation#

Before diving into isolation, recall the four guarantees:

• Atomicity — all operations in a transaction succeed or none do
• Consistency — the database moves from one valid state to another
• Isolation — concurrent transactions do not interfere with each other
• Durability — committed data survives crashes

Isolation is the most nuanced of these. Full isolation (serializability) is expensive, so databases offer weaker levels that trade correctness for throughput.

The Four Isolation Levels#

The SQL standard defines four levels, each preventing progressively more anomalies:

Level                | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------|------------|---------------------|-------------
Read Uncommitted     | Possible   | Possible            | Possible
Read Committed       | Prevented  | Possible            | Possible
Repeatable Read      | Prevented  | Prevented           | Possible
Serializable         | Prevented  | Prevented           | Prevented

Read Uncommitted#

The weakest level. A transaction can see uncommitted changes from other transactions.

-- Session A
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- Not yet committed

-- Session B (Read Uncommitted)
SELECT balance FROM accounts WHERE id = 1;
-- Returns 0 — a "dirty read"
-- If Session A rolls back, Session B used invalid data

Almost no production system uses this level. PostgreSQL does not even implement it — setting it silently upgrades to Read Committed.

Read Committed#

The default in PostgreSQL and Oracle. Each statement sees only data committed before that statement began.

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100

-- Session B
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A (same transaction)
SELECT balance FROM accounts WHERE id = 1;  -- Returns 200 (non-repeatable read)

The same query within one transaction can return different results. This is safe for most OLTP workloads but problematic for reports or multi-step business logic.

Repeatable Read#

The default in MySQL/InnoDB. Once a transaction reads a row, subsequent reads return the same value — even if another transaction modifies it.

-- Session A
BEGIN;  -- snapshot taken here
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100

-- Session B
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 100

However, phantom reads can still occur — new rows inserted by other transactions may appear in range queries.

Serializable#

The strongest level. Transactions execute as if they ran one at a time. Any anomaly causes an abort.

-- Both sessions try to insert if no row exists
-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM reservations WHERE seat = 'A1';  -- Returns 0
INSERT INTO reservations (seat) VALUES ('A1');

-- Session B (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM reservations WHERE seat = 'A1';  -- Returns 0
INSERT INTO reservations (seat) VALUES ('A1');
-- One of these transactions will be aborted with a serialization error

Your application must be prepared to retry aborted transactions.

Understanding the Anomalies#

Dirty Reads#

Reading data written by a transaction that has not yet committed. If that transaction rolls back, you acted on data that never existed.

Non-Repeatable Reads#

Reading the same row twice in one transaction and getting different values because another transaction modified and committed between your reads.

Phantom Reads#

Running the same range query twice and getting different sets of rows because another transaction inserted or deleted rows matching your predicate.

MVCC: How Modern Databases Implement Isolation#

PostgreSQL, MySQL/InnoDB, and Oracle all use Multi-Version Concurrency Control. Instead of locking rows during reads, the database keeps multiple versions of each row.

Row versions for accounts.id = 1:

Version  | Value   | Created by TX | Visible to
---------|---------|---------------|------------------
v1       | 100     | TX 50         | TX 50-99
v2       | 200     | TX 100        | TX 100+

Readers never block writers. Writers never block readers.

Each transaction gets a snapshot — a consistent view of the database at a point in time. The isolation level determines when the snapshot is taken:

• Read Committed — new snapshot per statement
• Repeatable Read — snapshot at first statement in transaction
• Serializable — snapshot at first statement, plus conflict detection

MVCC is why PostgreSQL can offer Repeatable Read without range locks and why VACUUM is necessary to clean up old row versions.

Optimistic vs Pessimistic Locking#

Pessimistic Locking#

Acquire locks before accessing data. Other transactions wait.

-- Lock the row explicitly
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions trying to update this row will block
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Best when conflicts are frequent — avoids wasted work from retries.

Optimistic Locking#

Proceed without locks, check for conflicts at commit time.

-- Application-level version check
SELECT balance, version FROM accounts WHERE id = 1;
-- balance = 100, version = 5

UPDATE accounts
SET balance = 50, version = 6
WHERE id = 1 AND version = 5;
-- If 0 rows affected, another transaction changed it — retry

Best when conflicts are rare — avoids lock contention and improves throughput.

PostgreSQL vs MySQL Defaults#

Feature              | PostgreSQL            | MySQL/InnoDB
---------------------|-----------------------|------------------------
Default isolation    | Read Committed        | Repeatable Read
MVCC                 | Yes                   | Yes
Serializable impl   | SSI (predicate-based) | Gap locks + next-key locks
Phantom prevention   | Via SSI at Serializable| Via gap locks at RR
Read Uncommitted     | Maps to Read Committed| Actually implemented

PostgreSQL's Serializable Snapshot Isolation (SSI) is particularly elegant — it detects dangerous patterns in the dependency graph and aborts only the minimum transactions necessary.

MySQL prevents phantoms at Repeatable Read using gap locks, which lock ranges between index entries. This can cause unexpected lock waits on INSERT statements.

Choosing the Right Level#

Read Committed (default for most): suitable for typical web applications where each request is a short transaction and occasional non-repeatable reads are acceptable.

Repeatable Read: use when a transaction performs multiple reads that must be consistent — financial reports, inventory checks, or any multi-step read-then-write pattern.

Serializable: use for critical correctness requirements — double-booking prevention, constraint enforcement that spans multiple tables, or anywhere a race condition would cause data corruption.

Rule of thumb: start with your database's default. Only escalate when you can identify a specific anomaly that would cause a real bug. Every step up costs throughput.

Practical Checklist#

1. Know your database's default isolation level
2. Use SELECT ... FOR UPDATE when you read-then-write in Read Committed
3. Always handle serialization errors with retry logic at Serializable
4. Monitor lock wait times and deadlock frequency
5. Prefer optimistic locking for low-contention, high-throughput paths
6. Use pessimistic locking for high-contention resources like counters or inventory
7. Test concurrent scenarios — isolation bugs only appear under load

---

This is article #254 in the Codelit engineering series. Browse all posts at codelit.io for deep dives on databases, distributed systems, and backend architecture.