Transaction Isolation Levels Explained

Isolation levels are usually taught as a ladder from “fast and loose” to “slow and safe”, which is misleading enough to cause outages. They are not degrees of safety. Each level is a specific list of anomalies the database guarantees will not occur, and the useful question is never “is this level safe” but “which anomaly am I still exposed to, and does my code care?”

The four anomalies, in the order they stop mattering

Dirty read — reading a row another transaction has written but not committed. If that transaction rolls back, you acted on data that never existed. Every mainstream database prevents this by default; you can largely stop thinking about it.

Non-repeatable read — reading the same row twice in one transaction and getting different values, because someone committed in between.

Phantom read — running the same query twice and getting a different set of rows, because someone inserted a row matching your WHERE.

Write skew — the subtle one. Two transactions read an overlapping set, each concludes its own write is fine, and both commit. Neither saw the other, and together they produce a state the rule they were both enforcing forbids.

What each level actually promises

Level	Dirty read	Non-repeatable read	Phantom	Write skew
Read uncommitted	possible*	possible	possible	possible
Read committed	prevented	possible	possible	possible
Repeatable read	prevented	prevented	prevented†	possible
Serializable	prevented	prevented	prevented	prevented

* PostgreSQL has no true read uncommitted — asking for it gives you read committed. † Under snapshot isolation (PostgreSQL’s repeatable read), phantoms are prevented, though the SQL standard permits them at this level. This is a real divergence between the standard and every implementation you will actually use, and it is why quoting the standard rarely settles an argument.

Read committed: what most of your code runs under

Each statement sees a fresh snapshot of committed data. This is the default almost everywhere and it is the right default.

The gap: two reads in one transaction can disagree.

BEGIN;
SELECT balance FROM accounts WHERE id = 7;  -- 100
-- another transaction commits a withdrawal here
SELECT balance FROM accounts WHERE id = 7;  -- 40
COMMIT;

Both answers were true when read. If your logic assumed they would match, your logic is wrong under read committed — and it will be wrong rarely, under load, which is the worst way to be wrong.

Repeatable read: one snapshot for the whole transaction

Every read in the transaction sees the database as of the moment it began. Reads never contradict each other. Reporting and multi-step reads become straightforward.

What it still allows is write skew, and here is the canonical shape:

-- Rule: at least one doctor must remain on call.
-- Two doctors click "leave" at the same instant.

-- Transaction A                      -- Transaction B
BEGIN;                                BEGIN;
SELECT count(*) FROM shifts           SELECT count(*) FROM shifts
  WHERE on_call = true;  -- 2           WHERE on_call = true;  -- 2
-- "2 > 1, safe to leave"             -- "2 > 1, safe to leave"
UPDATE shifts SET on_call = false     UPDATE shifts SET on_call = false
  WHERE doctor = 'A';                   WHERE doctor = 'B';
COMMIT;                               COMMIT;
-- now: 0 doctors on call

Neither transaction modified a row the other touched, so nothing conflicts. Both snapshots were internally consistent. The invariant is still broken.

This is the pattern to watch for: read something, decide, then write something else based on that decision. Whenever you see it, repeatable read is not enough.

Serializable: as if they ran one at a time

The database guarantees the outcome matches some serial order of the transactions. Write skew becomes impossible.

PostgreSQL implements this optimistically — it tracks read/write dependencies and aborts a transaction that would break serializability rather than blocking it up front. So the cost is not slowness, it is failure you must handle:

ERROR: could not serialize access due to read/write dependencies

Which means the non-negotiable requirement for using serializable is a retry loop. Catch the serialization failure, retry the whole transaction, cap the attempts. Code that cannot retry cannot use serializable safely.

Picking, in practice

Default to read committed. Then upgrade the specific transactions that read-then-decide-then-write:

• Booking the last seat, allocating the last item of stock
• Enforcing a limit — “no more than N of X”
• Any invariant spanning multiple rows
• Money moving between accounts

For a single such transaction you often do not need serializable at all. Taking an explicit lock on the rows you are about to reason about — SELECT … FOR UPDATE — turns the read into one that blocks others, closing the window with a mechanism that is easier to reason about locally. Reach for serializable when the invariant spans rows you cannot enumerate up front, which is precisely when row locks stop working.

What you have actually chosen

Not a safety setting. You have chosen which concurrent histories your application is willing to be surprised by, and paid for the rest in either blocking or retries.

The failure mode of getting this wrong is not an error message. It is a correct-looking system that produces an impossible state a few times a month, under load, in a way nobody can reproduce.