Isolation Levels

Isolation is the "I" in ACID. When many transactions touch the same data at the same time, isolation decides how much they can see of each other's work. Too little and you get wrong balances and double bookings. Too much and everything waits in line. Databases give you a dial, the isolation level, and choosing it well is a real design decision.

The core problem#

One bank account holds $1000. Two ATMs process withdrawals at the same moment:

The final balance is $700. It should be $200. ATM 1's withdrawal vanished.

This is the same race condition you get with threads and shared memory, except here the fix is transactions and isolation levels instead of mutexes.

Isolation is a spectrum:

Before looking at the levels, you need the three classic anomalies they prevent.

Anomaly 1: dirty read#

A transaction reads data another transaction hasn't committed yet. If that other transaction rolls back, you acted on a value that never really existed.

Analogy: a friend says they're quitting their job tomorrow, you tell everyone, and then they change their mind. You acted on something that was never final.

Why it's dangerous:

• Shop: T1 reserves an item (stock 10 → 9). T2 reads 9 and shows "9 left". T1 rolls back. Stock is really 10.
• Bank: T1 deposits $10,000 (balance $100 → $10,100). T2 reads $10,100 and approves a loan. T1 rolls back. The loan was approved on money that never arrived.

Anomaly 2: non-repeatable read#

You read the same row twice in one transaction and get different values, because another transaction committed a change in between.

Same query, same transaction, different answer.

The difference from a dirty read: both values were committed and real. The value just changed under you mid-transaction.

Analogy: you check your balance ($1000) and start planning bills. Your roommate legitimately moves money out. You check again: $500. Both readings were true at the time, but they disagree within your one session.

Why it's dangerous:

• Reports: a monthly report reads total revenue as $50,000 at the top. A refund commits. The bottom of the report reads $49,500. The report contradicts itself.
• Booking: you read seat 5A as free and tell the user. Someone else books it and commits. You read again and it's taken.

Anomaly 3: phantom read#

A non-repeatable read is about an existing row changing. A phantom read is about rows appearing or disappearing from the result of a range query.

The original 3 rows didn't change. A new "phantom" row appeared.

Analogy: you count 10 people in a meeting room and start preparing 10 lunch boxes. Someone walks in. Nobody changed seats (that would be a non-repeatable read); a new person appeared.

Why it's dangerous:

• Payroll: you count 50 engineers and start summing salaries. Someone transfers into Engineering and commits. A second query sees 51. Your totals don't match.
• Inventory: you count 12 out-of-stock products. Another transaction sells the last unit of something. Your next aggregate query includes a 13th.

The three anomalies side by side#

The four isolation levels#

The SQL standard defines four levels by which anomalies they allow.

Read uncommitted (lowest)#

You can see other transactions' uncommitted changes, so dirty reads are possible.

• Allows: dirty, non-repeatable and phantom reads.
• Use: almost never. At most for rough analytics ("about how many orders are pending?"), where speed beats accuracy. PostgreSQL doesn't even implement it separately; asking for it gives you Read Committed.

Read committed (the practical default)#

You only ever see committed data. Each statement sees the latest committed data as of when that statement starts, so two reads in one transaction can still differ.

• Blocks: dirty reads.
• Allows: non-repeatable and phantom reads.
• Default in: PostgreSQL, Oracle, SQL Server.
• Good for: most web traffic. A page load is essentially one read. If a price changes between loading a list and clicking a product, showing the newer price is fine. A worker that reads one item, processes it and writes a result is also fine here.

Repeatable read (snapshot isolation)#

The transaction works from a snapshot taken when it starts. Every read sees the same data, whatever others commit meanwhile.

How it works: MVCC. With multi-version concurrency control, the database keeps several versions of each row, tagged with when they were committed. Each transaction reads the newest version committed before it started.

Say the row has two versions: balance = $1000 committed at time 50, and balance = $500 committed at time 105.

• Blocks: dirty and non-repeatable reads.
• Phantoms: the SQL standard allows them at this level, but common implementations go further. PostgreSQL's repeatable read reads everything from the snapshot, so phantoms don't appear. MySQL's InnoDB uses the snapshot for plain reads and adds gap locks for locking reads.
• Still possible: write skew. Two transactions each read the same snapshot, make a decision, and write different rows. Example: two doctors each check that someone else is on call, and both go off call. Neither overwrote the other, but the rule "at least one doctor on call" is broken. Only serializable prevents this.
• Default in: MySQL (InnoDB).
• Good for: long reports, balance checks that read the same data twice, batch jobs that must see one consistent picture.

Serializable (highest)#

Transactions behave as if they ran one at a time, in some order. No anomalies are possible.

The result is the same as running T1 fully, then T2.

• Blocks: everything above, plus write skew.
• Cost: the lowest concurrency. Under contention you get waiting or retries.
• Good for: moving money, buying the last unit in stock, booking the last seat: anywhere one anomaly is unacceptable.

Summary#

Each level blocks more anomalies than the one before it:

That safety costs speed:

• Higher isolation: safer data, but more locking or aborts, less concurrency and lower throughput.
• Lower isolation: more throughput, but your application must handle the anomalies.

How serializable is implemented#

There are three main approaches.

1. Actual serial execution. Run one transaction at a time on a single thread. There are no conflicts because there is no concurrency. It only works if transactions are short and the data fits in memory. Redis works this way: commands, MULTI/EXEC blocks and Lua scripts run one after another.

2. Two-phase locking (2PL): pessimistic. Take locks before touching data. Readers and writers of the same rows block each other until the holder commits or aborts.

Conflicts are handled up front, but there is a lot of waiting and a risk of deadlocks (each transaction waiting on a lock the other holds). The database detects these and aborts one. MySQL's serializable level works this way.

3. Serializable snapshot isolation (SSI): optimistic. Let transactions run on snapshots without blocking, track what each one read and wrote, and check for dangerous conflicts at commit time.

This is what PostgreSQL's serializable level uses. It works well when conflicts are rare. When they're frequent, many transactions abort and retry, so your code must be ready to retry.

Isolation without isolation levels#

Many NoSQL stores don't offer SQL isolation levels. You get similar effects with other tools:

• Eventually consistent reads never return uncommitted data, but might not show the very latest write.
• Strongly consistent reads return the latest committed write for that item.
• Conditional writes give optimistic concurrency without locks: "decrement stock only if stock > 0". If the condition fails, the write is rejected and you retry or report "sold out". This is a common fix for the last-item problem.

The same trick works in SQL:

UPDATE products SET stock = stock - 1
WHERE id = 42 AND stock > 0;
-- 0 rows updated means it was already sold out

Key takeaways#

• Without isolation, concurrent transactions cause lost updates, dirty reads, non-repeatable reads and phantoms.
• Read committed (PostgreSQL's default) blocks dirty reads and is fine for most web traffic.
• Repeatable read gives each transaction a consistent snapshot via MVCC, but still allows write skew.
• Serializable prevents every anomaly, implemented by serial execution, two-phase locking or SSI.
• Stronger isolation means less throughput. Use it where correctness is critical and be ready to retry aborted transactions.