Isolation Levels

Isolation is the 'I' in ACID — the promise that concurrent transactions don't interfere. Perfect isolation (Serializability) means transactions produce results indistinguishable from running them one at a time in some order. Perfect isolation is expensive; real databases offer a spectrum of isolation levels, each preventing some anomalies but allowing others.

The ANSI SQL standard defines four levels, from weakest to strongest:

1. Read Uncommitted — a transaction can read uncommitted changes from other transactions ('dirty reads'). Almost no database uses this in practice.
2. Read Committed — a transaction only sees data that other transactions have committed. No dirty reads. But re-reading a row within one transaction can return different values ('non-repeatable reads'), and the set of rows matching a predicate can change ('phantom reads'). This is the PostgreSQL, SQL Server, and Oracle default.
3. Repeatable Read — once a transaction reads a row, re-reading it returns the same value. Non-repeatable reads are prevented. Phantom reads may still occur (new rows matching a WHERE can appear). This is the MySQL/InnoDB default.
4. Serializable — transactions are equivalent to running them sequentially. No anomalies. Most expensive.

The choice is a per-transaction or per-session setting: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;.

Dirty Read — transaction T2 reads a value that T1 wrote but hasn't committed. If T1 then rolls back, T2 has read data that 'never existed.' Almost no database allows this; Read Uncommitted isolation exists in the standard but is rarely used.

Non-Repeatable Read — within a single transaction, T2 reads balance = 100, then T1 commits balance = 50, and T2 reads balance = 50. The same query returns different values within one transaction. Read Committed allows this; Repeatable Read prevents it.

Phantom Read — within a single transaction, T2 runs SELECT * FROM orders WHERE total > 100 and gets 5 rows. T1 inserts a new row with total = 200 and commits. T2 runs the same query and gets 6 rows. The row set changed.

Serialization Anomaly — the most subtle. Two transactions each read a count (5), each increment it (6), each write 6. The final count is 6, not 7. Individually each transaction looks fine; the anomaly is only visible in their interaction. This is what Repeatable Read allows and Serializable prevents.

The classic example of why this matters: a post_view_count table where two simultaneous viewers each read the count (5), each increment, each write 6. You've lost a view. At Serializable, one transaction would have aborted and retried, giving the correct count of 7.

Serializable is the gold standard but expensive. There are two implementation strategies:

1. Lock-based (two-phase locking, 2PL) — the classic approach. Acquire shared locks on reads, exclusive locks on writes; never release a lock until the transaction ends. Guarantees serializability but suffers from deadlocks and low concurrency. MySQL's Repeatable Read uses next-key locking, a variant of 2PL.

2. SSI (Serializable Snapshot Isolation) — used by PostgreSQL 9.1+. Uses MVCC for reads (no read locks) and tracks 'rw-dependencies' between transactions. If a transaction's reads might have been invalidated by another's writes, the database aborts it. Higher concurrency than 2PL but with abort/retry overhead.

When to use Serializable:

• Financial invariants where races are unacceptable.
• Constraints that span multiple rows (e.g., 'at most 5 active reservations per user').
• Operations where retrying on abort is safe and acceptable.

When NOT to use Serializable:

• High-throughput workloads where the abort rate kills performance.
• Read-heavy workloads where Read Committed suffices.
• Long-running transactions (they'll abort more often).

In practice, many applications run at Read Committed and use SELECT … FOR UPDATE (an explicit row lock) for the specific read-modify-write sequences that need stronger isolation. This is cheaper than upgrading the whole transaction to Serializable.

SELECT … FOR UPDATE is the surgical alternative to bumping the isolation level. It acquires a row-level write lock on the rows returned by the SELECT, holding them until the transaction commits. Other transactions trying to read (with FOR UPDATE) or write those rows block.

Pattern for safe read-modify-write:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- locks row
-- application checks balance >= 100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

This gives the correctness of Serializable for the specific rows involved, without paying the cost for the rest of the transaction.

Trade-off: row locks can cause contention and deadlocks. If two transactions try to lock the same set of rows in opposite orders, you get a deadlock — Postgres will abort one and you must retry.

Rules of thumb:

• Default to Read Committed.
• Use SELECT … FOR UPDATE for specific read-modify-write sequences.
• Upgrade to Serializable only when correctness requires it and the workload tolerates aborts.
• Never hold locks across external calls (HTTP, etc.) — that's how you get deadlocks at scale.

Mental model. Think of Isolation Levels as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Isolation Levels mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Isolation Levels, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.