Database Transaction Isolation Levels Explained

Almost every team I have worked with has the same gap: they know their database has isolation levels, they know the default is whatever the ORM does, and they have never once changed it deliberately.

That is usually fine. Then a booking gets double sold, or two concurrent requests both decrement the same stock count to 4, and the bug is unreproducible in every test you write because tests run one transaction at a time.

Isolation levels are the vocabulary for that class of bug. Here is what each one actually gives you.

The Anomalies Come First

Isolation levels are defined by what they forbid, so the anomalies are the real subject. There are four worth knowing, and one that people miss.

Dirty read. You read a row that another transaction has written but not committed. It may roll back, and you acted on data that never existed.

Non-repeatable read. You read the same row twice in one transaction and get different values, because someone committed between your two reads.

Phantom read. You run the same query twice and the second run returns rows that were not there the first time. The individual rows are stable; the set is not.

Lost update. Two transactions read the same value, both compute a new one from it, and both write. The second write silently discards the first. This is the one that eats stock counts and view counters.

Write skew. The subtle one. Two transactions each read a set, each check a constraint that currently holds, and each write a different row. Neither overwrites the other, and the constraint is broken afterwards. Two doctors both checking “is anyone else on call” and both going off call is the classic example.

What Each Level Prevents

Level	Dirty read	Non-repeatable read	Phantom	Lost update	Write skew
Read uncommitted	Allowed	Allowed	Allowed	Allowed	Allowed
Read committed	Prevented	Allowed	Allowed	Allowed	Allowed
Repeatable read	Prevented	Prevented	Varies	Varies	Allowed
Serializable	Prevented	Prevented	Prevented	Prevented	Prevented

The two “varies” cells are where portability goes wrong. The SQL standard says repeatable read permits phantoms. PostgreSQL’s repeatable read is implemented as snapshot isolation and does not exhibit them, and it aborts a transaction rather than losing an update. MySQL’s InnoDB repeatable read uses next-key locking, which suppresses phantoms for locking reads but not in the same way. The standard describes a floor, not the behaviour you will actually get.

The Defaults Are Not the Same

This is the single most useful fact in the article.

• PostgreSQL: read committed
• MySQL / InnoDB: repeatable read
• SQL Server: read committed
• Oracle: read committed
• SQLite: serializable, by virtue of its locking model

Port an application from MySQL to PostgreSQL without touching the transaction code and you have quietly lowered its isolation. A read-modify-write sequence that MySQL protected with repeatable read is now a lost update waiting for traffic. The reverse hurts too: code that assumes it can see other transactions’ commits partway through a long transaction stops seeing them.

The PostgreSQL manual on transaction isolation ↗ is the clearest primary source on any of this, and SQLite’s own note on isolation ↗ is worth reading for how differently a single-writer engine gets to the same guarantees.

Read Committed: The Sensible Default

Every statement sees a fresh snapshot of committed data. It is cheap, it never blocks readers behind writers in an MVCC engine, and it is right for the overwhelming majority of request handling.

What it does not do is give you a stable view across statements. This is broken:

BEGIN;
SELECT stock FROM items WHERE id = 42;   -- returns 5
-- application computes 5 - 1
UPDATE items SET stock = 4 WHERE id = 42;
COMMIT;

Two concurrent copies of that both read 5 and both write 4. One sale vanished. Read committed did nothing wrong; you asked it for a value and then acted on a stale copy.

The fix is not always a higher isolation level. It is usually to stop round-tripping the value through your application:

UPDATE items SET stock = stock - 1 WHERE id = 42 AND stock > 0;

One statement, atomic, and the stock > 0 guard means an affected row count of zero tells you it failed. Most lost updates are an application design problem wearing a database costume.

Repeatable Read: A Stable Snapshot

The transaction sees one snapshot for its whole duration. Good for reports, reconciliation, and anything that reads many tables and needs them to agree with each other.

The trade is that PostgreSQL will abort your transaction with a serialization failure if it detects a conflicting concurrent update, so anything running at repeatable read or above needs a retry loop. Not a try/catch that logs. An actual retry with backoff, because the transaction is expected to fail occasionally and that is the design working.

Serializable: When You Have a Real Invariant

Serializable guarantees the outcome is equivalent to running the transactions one after another. It is the only level that catches write skew, and write skew is the anomaly that produces the bugs nobody can reproduce.

Use it where an invariant spans rows that may not exist yet:

• The last seat on a flight
• Overdraft checks across multiple accounts
• On-call rotas, room bookings, any “at least one” or “at most one” rule
• Anything where the check and the write are separated by application logic

PostgreSQL implements this with serializable snapshot isolation: no extra locks, so it does not block, but it will abort transactions that would have violated serializability. Your cost is a retry rate, which is measurable. Measure it before deciding the level is unaffordable. Jepsen’s consistency model map ↗ is the reference if you want to see where these guarantees sit relative to each other.

Choosing Without Guessing

Three questions, in order.

1. Does this transaction enforce an invariant across rows? If yes, serializable, with a retry loop. Do not try to be clever with locks.
2. Does it need a consistent view across multiple statements? If yes, repeatable read.
3. Otherwise, read committed, and push the concurrency safety into a single statement or a database constraint.

A unique index beats every isolation level for enforcing uniqueness, and a check constraint beats a serializable read of the same condition. Isolation is what you reach for when the rule cannot be expressed declaratively.

Set the level per transaction. An application that runs everything at serializable is paying for retries on transactions that never needed them, and an application that runs everything at read committed will eventually double-book something.

The Practical Checklist

• Know your engine’s default, and write it down somewhere your team reads.
• Wrap anything above read committed in a retry with backoff, capped at three or four attempts.
• Prefer a single atomic statement to read-modify-write in application code.
• Reach for a constraint before reaching for an isolation level.
• Keep transactions short. Long transactions raise conflict rates at every level and hold back vacuum in PostgreSQL.
• Test concurrency deliberately: two connections, interleaved statements, in an integration test. Nothing else finds these bugs.

If you are working on the layer underneath this, our guides to database indexing and connection pooling cover the other two things that quietly decide how a database behaves under load. For invariants that span services rather than tables, isolation levels stop helping and the saga pattern takes over.