Isolation levels, and the anomalies each one allows

Isolation is the only one of ACID's four letters with a dial on it. The dial has settings, each allows certain things to go wrong, and PostgreSQL's default is not the strictest. Knowing which setting you are on, and what it permits, is the difference between a system that is correct under load and one that is correct when you test it alone.

The anomalies

Four things that can happen when transactions overlap. Each has a name because each is a specific failure.

Dirty read — you see another transaction's uncommitted change, which may then be rolled back. PostgreSQL never allows this at any level.

Non-repeatable read — you read a row twice in one transaction and get different values, because somebody committed a change in between.

Phantom read — you run the same query twice and get different rows, because somebody inserted or deleted rows matching your condition.

Serialization anomaly — the end state could not have been produced by running the transactions one after another in any order. The lost update is the everyday example, and it is the next lesson.

The four levels

BEGIN ISOLATION LEVEL READ COMMITTED;     -- PostgreSQL's default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

Two notes on that table, both PostgreSQL-specific:

Read uncommitted does not exist here. Ask for it and you get read committed. The dash means the standard permits dirty reads at that level and PostgreSQL never does.

Repeatable read prevents phantoms, which the SQL standard does not require. PostgreSQL's implementation is stricter than the name promises, because it works by giving the transaction a consistent snapshot.

Read committed: what it actually means

The default, and the one you are using unless you said otherwise.

Each statement sees a snapshot taken at the moment that statement began. Not when the transaction began — each statement.

BEGIN;                                      -- read committed
SELECT copies FROM books WHERE id = 1;      -- 3
-- somebody else commits copies = 2
SELECT copies FROM books WHERE id = 1;      -- 2   ← different answer, same transaction
COMMIT;

That is a non-repeatable read, and it is allowed. Two identical queries in one transaction, two different answers.

It is fine for most work. It is not fine when you read a value, decide something based on it, and then write — which is exactly the pattern in the next lesson.

There is a subtlety worth knowing: if an UPDATE finds a row that another transaction has changed since your statement began, PostgreSQL waits for that transaction, then re-evaluates your WHERE against the new version. That behaviour is what makes the compare-and-swap fix in the next lesson work.

Repeatable read

The whole transaction sees one snapshot, taken when the first statement ran. The same query gives the same answer every time, however much the world changes.

The price:

ERROR:  could not serialize access due to concurrent update

If you try to update a row that has changed since your snapshot, PostgreSQL cannot let you — your snapshot is stale — so it aborts your transaction.

This is not a bug. It is the level working. Your application must catch that error and retry the whole transaction. Code that does not retry will simply fail under load.

Use it for: a multi-statement report that must be internally consistent, or any read-then- write where you would rather retry than lose data.

Serializable

The strictest. The result is guaranteed to be the same as if the transactions had run one at a time, in some order. Every anomaly is impossible, including ones nobody has a name for.

PostgreSQL implements this with Serializable Snapshot Isolation — it does not lock everything; it tracks read-write dependencies and aborts a transaction if it detects a cycle that could not have happened serially.

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.

The hint is an instruction. Every serializable transaction needs a retry loop, and without one you have made things worse, not better.

The costs: more aborts under contention, some bookkeeping overhead, and it does not work across prepared cross-database transactions.

When it is worth it: logic where getting it wrong is unacceptable and writing the locking by hand would be error-prone — financial ledgers, inventory with complex rules, anything with an invariant across several rows. It is a genuinely good option that people avoid out of vague fear.

The retry loop

Not optional at the stricter levels:

for attempt in range(3):
    try:
        with conn:                       # BEGIN ... COMMIT
            cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
            ...
        break
    except SerializationFailure:
        if attempt == 2:
            raise
        time.sleep(0.05 * (2 ** attempt))   # back off a little

Three things it must do: retry the whole transaction (not just the failed statement, because everything was rolled back), back off so contending transactions do not collide again immediately, and give up eventually rather than spinning forever.

PostgreSQL uses SQLSTATE 40001 for serialization failure and 40P01 for deadlock. Both are retryable; most other errors are not, and retrying them is how you turn one failure into many.

Choosing

Ordinary reads and writes                       read committed (the default)
A multi-statement report that must be consistent repeatable read
Read-then-write where losing a write is bad     repeatable read + retry, or a lock
Invariants across several rows, must be right   serializable + retry

Most applications should stay on read committed and handle the specific read-then-write cases with the tools in the next lesson. Turning the whole application up to serializable without retry loops makes it fail under load.

Set it per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Or for a session, or in postgresql.conf — but per transaction is the right granularity, because it is a property of what that piece of work needs.

Read-only and deferrable

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

A long analytical query that must see a consistent world. DEFERRABLE makes it wait until it can run without any risk of serialization failure — so it never aborts and never causes anybody else to. It may wait a while to start, which is the trade.

Good for a nightly report that must be exactly right.

Check your work

The four anomalies. Dirty read, non-repeatable read, phantom, serialization anomaly.

Which one PostgreSQL never allows. Dirty reads, at any level.

What read uncommitted gives you here. Read committed.

What read committed snapshots. Each statement, not the transaction.

What that permits. Non-repeatable reads, phantoms and lost updates.

What read committed does on a concurrent update. Waits, then re-evaluates the WHERE against the new version.

What repeatable read snapshots. The whole transaction.

What it does instead of allowing a lost update. Aborts with could not serialize access due to concurrent update.

What serializable guarantees. The same result as running the transactions one at a time.

How PostgreSQL implements it. Tracking read-write dependencies, not locking everything.

What every stricter-level transaction needs. A retry loop.

The three properties of a good retry loop. Retry the whole transaction, back off, and give up eventually.

The two retryable SQLSTATEs. 40001 and 40P01.

Where most applications should stay. Read committed, handling specific cases explicitly.

Practice

1. Check your current isolation level with SHOW transaction_isolation.
2. In two sessions, demonstrate a non-repeatable read at read committed: read, have the other session commit a change, read again.
3. Do the same at repeatable read and confirm the value does not change.
4. At repeatable read, try to update a row the other session changed. Read the error.
5. Ask for READ UNCOMMITTED and then SHOW transaction_isolation.
6. Demonstrate a phantom at read committed: count rows matching a condition, have the other session insert one, count again.
7. Try the same at repeatable read.
8. Write a retry loop in Python or JavaScript for SQLSTATE 40001.
9. Make it fail three times in a row and confirm it gives up rather than spinning.
10. Run two serializable transactions that read each other's rows and write, and get a read/write dependency error.
11. Run a long SERIALIZABLE READ ONLY DEFERRABLE query and observe when it starts.
12. For a project of yours, list the read-then-write sequences and decide the level each needs.

Official documentation

• PostgreSQL — Transaction isolation — The authoritative page: every level, every anomaly, and the worked examples. Read it once in full.
• PostgreSQL — SET TRANSACTION — Setting the level, and READ ONLY DEFERRABLE.
• PostgreSQL — Error codes — 40001 and 40P01, and which others are worth retrying.
• PostgreSQL wiki — Serializable — How Serializable Snapshot Isolation works, with examples of what it catches.

Next: the lost update, demonstrated and then fixed.