Database Transaction Isolation Levels Explained (With the Bugs Each One Prevents)

Most developers set their database’s isolation level exactly once: never. They use whatever the driver or ORM defaults to, and it works, until a bug shows up that only happens under concurrent load, only in production, and never reproduces locally. Nine times out of ten, that bug is an isolation level problem, and the team that finds it usually didn’t know isolation levels were something you could get wrong.

Here’s the direct version: an isolation level is a promise about what one transaction is allowed to see of another transaction’s in-progress or concurrent work. Stronger promises prevent more classes of bugs. Stronger promises also cost more, in throughput, in lock contention, and in transactions your database has to abort and ask your application to retry. Picking the right one is a tradeoff you should make on purpose, transaction by transaction, not a default you inherit.

The three problems isolation levels exist to solve

Before the levels, the bugs. Every isolation level is defined by which of these it prevents.

Dirty read. Transaction A writes a row but hasn’t committed. Transaction B reads that uncommitted row. Transaction A then rolls back. B now has data that never officially existed. This is the easiest bug to explain and the one every mainstream database prevents by default.

Non-repeatable read. Transaction A reads a row. Transaction B updates and commits that same row. Transaction A reads the row again, in the same transaction, and gets a different value. A single transaction seeing two different realities for the same row breaks any logic that assumes a transaction sees one consistent snapshot.

Phantom read. Transaction A runs a query that returns a set of rows matching a condition. Transaction B inserts a new row that matches that condition and commits. Transaction A re-runs the same query and now sees a row that wasn’t there before. This is the set-based cousin of a non-repeatable read.

Write skew. The one nobody explains well. Two transactions each read overlapping data, each independently decide their write is safe based on what they read, and both commit. Individually, each write was valid given what that transaction saw. Together, they violate an invariant neither transaction could see because it wasn’t looking at the other’s in-flight changes. This is the bug that survives Repeatable Read and is why Serializable exists.

The four levels, and what each one actually buys you

Level	Dirty reads	Non-repeatable reads	Phantom reads	Write skew
Read Uncommitted	Possible	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible	Possible
Repeatable Read	Prevented	Prevented	Usually prevented*	Possible
Serializable	Prevented	Prevented	Prevented	Prevented

*PostgreSQL’s Repeatable Read is implemented via snapshot isolation and prevents phantom reads in practice, even though the SQL standard doesn’t strictly require it at that level. MySQL’s InnoDB behaves similarly. Check your specific database; the standard defines a floor, not an exact implementation.

Read Uncommitted is close to theoretical at this point. It allows dirty reads, which means your queries can return data that was never actually committed. PostgreSQL doesn’t even implement it as a distinct level; setting it just gives you Read Committed. If you’re on a database where this level does something, avoid it outside of specific analytical workloads where you deliberately want an approximate, fast read over a large table.

Read Committed is the workhorse default. Every statement in your transaction sees a fresh snapshot of committed data at the moment that statement runs. This is enough for the overwhelming majority of CRUD operations: create a record, update a record, look something up before writing it. It is not enough when a transaction needs to reason about data staying stable across multiple statements, or when a decision made from one read has to remain valid when the transaction commits.

Repeatable Read takes its snapshot once, at the start of the transaction, and every read within that transaction sees that same snapshot regardless of what commits elsewhere in the meantime. This is what you want for a transaction that reads a value, computes something based on it, and writes a result, where you need the value to not have moved underneath you. It is still vulnerable to write skew, which is the trap: a transaction using Repeatable Read feels safe because it can’t be fooled by a moving value, but two Repeatable Read transactions can still independently make decisions that conflict.

A classic write skew example: a hospital on-call system requires at least one doctor on call at all times. Two doctors, both on call, each independently check “is there at least one other doctor on call besides me?” Both see the other doctor still listed and both remove themselves. Both transactions were individually correct given what they read. The invariant (at least one doctor on call) is now violated, and Repeatable Read did nothing to stop it, because neither transaction’s read set overlapped with the other’s write in a way the isolation level tracks.

Serializable is the only level that guarantees the outcome of your concurrent transactions is equivalent to some order in which they ran one at a time. It prevents write skew because the database detects when two transactions’ read and write sets could produce a result impossible under any serial ordering, and aborts one of them. The cost: real throughput loss under contention, and your application needs a retry loop, because a Serializable transaction can fail with a serialization error even when nothing was “wrong” with it, purely because the database chose to abort it in favor of ordering guarantees.

-- PostgreSQL: set isolation per-transaction, not globally
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM on_call_doctors WHERE shift_id = 42;
-- application logic decides whether it's safe to remove this doctor
UPDATE on_call_doctors SET active = false
  WHERE doctor_id = 7 AND shift_id = 42;

COMMIT;
-- if this raises a serialization_failure, retry the whole transaction

How to actually decide

Don’t set a global isolation level and call it done. Decide per transaction, based on the specific invariant that transaction has to protect:

Use Read Committed (the default almost everywhere) for the bulk of your application: simple reads, simple writes, operations where each statement being individually consistent is enough. This is most of what a typical web application does.

Use Repeatable Read when a transaction reads a value, does meaningful logic with it, and needs that value to not shift mid-transaction, but where you’ve reasoned through the concurrent cases and confirmed write skew isn’t a risk. Reporting queries and multi-step reads that don’t feed into a competing write are good candidates.

Use Serializable for the small number of transactions where correctness genuinely depends on it: financial balances, inventory allocation, booking and reservation systems, anything with an invariant like “exactly one,” “at least one,” or “never negative” that two concurrent transactions could jointly violate. Wrap these in a retry loop, because serialization failures are an expected, not exceptional, outcome.

This connects directly to query performance work: once you’ve picked the right isolation level for a transaction, the next question is usually whether that transaction is doing more locking or scanning than it needs to. Our guide to Postgres query optimization for application developers covers the indexing and query-shape side of that problem, and our deeper look at CAP theorem in practice covers the distributed-systems version of the same tradeoff: correctness guarantees cost something, and the job is picking where you actually need to pay for them.

The isolation level is not a database setting you configure once. It’s a design decision you make transaction by transaction, based on what that transaction can’t afford to get wrong.