Isolation Levels Deep Dive

Isolation Levels: Choosing What Anomalies You Can Tolerate

Isolation is not “on/off.” Each isolation level allows certain anomalies and prevents others. Stronger isolation improves correctness guarantees but often reduces concurrency and throughput.

In production, the right isolation level is the one that prevents your business bugs at acceptable cost.

The Core Problem: Concurrent Transactions

When two transactions run at the same time, you must decide what each one is allowed to see. Isolation level defines that visibility.

Most systems implement isolation using a combination of:

• MVCC snapshots (what rows are visible)
• Locks (what rows can be modified)
• Conflict detection (especially for serializable)

Read Committed: The Default in Many Systems

Read Committed means each statement sees only committed data at the time the statement starts. A transaction can see different results for the same query if it runs the query twice.

What it prevents:

• Dirty reads (reading uncommitted changes)

What it allows:

• Non-repeatable reads
• Phantom reads (depending on engine and query shape)
• Lost updates (if application is careless)

-- Transaction A (Read Committed)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- later, same TX:
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

If another transaction updates and commits between the two SELECT statements, A can observe different balances.

Repeatable Read: Stable Snapshot for the Transaction

Repeatable Read means the transaction sees a consistent snapshot across its lifetime (snapshot taken at the start of the transaction, or at first read depending on engine).

What it prevents:

• Dirty reads
• Non-repeatable reads

What it may allow:

• Phantom reads (implementation-dependent)
• Write skew (important in real systems)

-- Transaction A (Repeatable Read)
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 42;
-- another TX inserts an order and commits
SELECT COUNT(*) FROM orders WHERE user_id = 42;
COMMIT;

Under repeatable read, the count typically stays stable within the transaction, because the snapshot is stable.

Serializable: Strongest Isolation, Highest Cost

Serializable aims to make concurrent execution behave as if transactions were executed one by one in some order.

How it is achieved varies:

• Strict locking approaches (blocking)
• Serializable snapshot isolation (conflict detection and aborts)

Serializable often increases:

• Abort/retry rates
• Lock contention (in some engines)
• Latency under contention

-- In serializable mode, conflicts may cause aborts
BEGIN;
-- business invariant read
SELECT SUM(balance) FROM accounts WHERE group_id = 7;
-- write based on read
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT; -- may fail and require retry

Production implication: serializable requires robust retry logic at application level.

Isolation Level Is Not Just a Database Setting

Isolation interacts with application patterns:

• “Read then write” patterns are sensitive to anomalies.
• Idempotency and retries become critical under serializable.
• Long-running transactions amplify contention and snapshot retention.

Throughput vs Correctness Tradeoff

As isolation increases:

• Concurrency decreases (more blocking or more aborts)
• Work increases (conflict detection bookkeeping)
• Tail latency increases under contention

This is why “always use serializable” is rarely correct in high-throughput OLTP systems.

Common Production Choices

Typical patterns:

• Read Committed: general OLTP workloads with careful update patterns.
• Repeatable Read: workflows that require stable reads inside a transaction (reports, multi-step operations).
• Serializable: critical invariants where anomalies are unacceptable and retry is implemented.

How to Decide: Start from Invariants

Decide based on invariants you must protect:

• No overselling inventory
• No double-spend of balance
• No duplicate unique allocation under concurrency

If an invariant can be violated under weaker isolation, you must either:

• Increase isolation, or
• Add explicit locking, or
• Use atomic database primitives (constraints, unique keys, conditional updates)

Failure Modes in Production

• Hidden anomaly bugs: weak isolation allows subtle data corruption.
• Lock contention: stronger isolation increases blocking.
• Retry storms: serializable conflicts cause thundering herd retries.
• Long transaction snapshots: repeatable read holds snapshots, increases bloat risk.
• Inconsistent expectations: developers assume stronger guarantees than configured.

Operational Checklist

• Document default isolation level and where it is overridden.
• Identify business invariants that require protection.
• Test concurrency scenarios with realistic load.
• Implement safe retry logic for serializable or deadlock cases.
• Keep transactions short to reduce lock duration and snapshot retention.
• Monitor abort/retry rate if using serializable.
• Track lock waits and timeouts as leading indicators.
• Ensure background jobs do not hold long snapshots.
• Prefer constraints and atomic updates where possible.
• Review isolation after major feature changes (new invariants).

Summary

Isolation levels define which anomalies you accept. Read Committed maximizes concurrency but allows more anomalies. Repeatable Read stabilizes snapshots but can increase retention and still allows some anomalies. Serializable offers the strongest guarantees but requires retries and can reduce throughput. Choose isolation based on invariants, not ideology.