Handling Concurrency in Python Services — Part 2: Choosing an Isolation Level

A descending ladder of the four isolation levels from Serializable down to Read Uncommitted, annotated with the trade-off: stronger guarantees upward, higher throughput downward

This is Part 2 of a three-part series. Part 1 showed why threading.Lock fails across multiple app servers and introduced dirty reads and lost updates. Part 3 covers the locking patterns you'll actually ship.

Part 1 ended with the two anomalies that Serializable isolation prevents: dirty reads and lost updates. There are two more — non-repeatable reads and phantom reads — and understanding all four is what lets you pick a weaker isolation level deliberately, instead of paying for Serializable everywhere.

Non-repeatable reads

A non-repeatable read is when a transaction reads the same row twice and gets different values, because another transaction committed a change in between.

Our bookstore runs a report inside one transaction:

-- Transaction A (report)
BEGIN;
SELECT stock FROM books WHERE id = 1;   -- reads 10

Meanwhile a purchase commits:

-- Transaction B (purchase)
BEGIN;
UPDATE books SET stock = 8 WHERE id = 1;
COMMIT;

Transaction A reads the row again — same transaction, same query:

SELECT stock FROM books WHERE id = 1;   -- now reads 8

Nothing "dirty" happened; B's data was committed. But A's view of the world changed mid-transaction. If A was computing "total inventory value" across two queries, its numbers no longer add up. Note the difference from a dirty read: there, A saw data that was never committed; here, A sees two different committed states.

Phantom reads

A phantom read is the same problem at the level of a set of rows rather than a single row. Transaction A runs a query that matches a set:

-- Transaction A
SELECT count(*) FROM orders WHERE customer_id = 42;   -- 3 orders

Transaction B inserts a new matching row and commits. A re-runs the identical query:

SELECT count(*) FROM orders WHERE customer_id = 42;   -- 4 orders

A new row has appeared — a phantom. Row-level protections can't stop this one, because the offending row didn't exist when A first ran the query. This matters for invariants over sets: "a customer may have at most 5 open orders", "no two bookings may overlap". Checking the set and then inserting is a race unless the isolation level (or a constraint) covers the whole predicate.

The four levels, with all four anomalies

Matrix of the four isolation levels against the four anomalies, showing Read Uncommitted permits all of them, Read Committed prevents only dirty reads, Repeatable Read prevents all but phantoms, and Serializable prevents everything

Read Uncommitted — transactions can see other transactions' uncommitted writes. Everything can go wrong. PostgreSQL doesn't even really implement it: ask for it and you get Read Committed.
Read Committed — you only ever see committed data, so no dirty reads. But each statement gets a fresh snapshot, so non-repeatable reads, phantoms, and lost updates are all still possible. This is PostgreSQL's default.
Repeatable Read — the transaction takes one snapshot and every read within it sees that snapshot. No dirty or non-repeatable reads. In PostgreSQL, a concurrent write to a row you've also written aborts one transaction with a serialization error rather than losing an update. The SQL standard still permits phantoms at this level (engines vary — Postgres's snapshot largely avoids them, MySQL's InnoDB uses gap locks).
Serializable — the result is guaranteed equivalent to some serial order. All four anomalies prevented. In PostgreSQL this is Serializable Snapshot Isolation: instead of blocking, it detects dangerous patterns and aborts one transaction, which your code must retry.

The key mental shift: the default isn't "safe". If you've never set an isolation level, you're on Read Committed (Postgres) or Repeatable Read (MySQL), and the lost-update scenario from Part 1 — two purchases both reading stock = 10 — happens exactly as described.

How the database pulls this off: MVCC

PostgreSQL doesn't implement isolation by making readers wait for writers. Under MVCC — Multi-Version Concurrency Control — every write creates a new version of the row, and every transaction reads the version that was current as of its snapshot. Readers never block writers; writers never block readers. Two writers to the same row still conflict — that's where row locks and serialization errors come in — but the common read-heavy workload flows without contention.

This is why "just use Serializable everywhere" isn't free: the database has to track read/write dependencies between transactions and abort those that would violate serial order. On a hot row — the last copy of a popular book — abort-and-retry rates climb fast.

Setting the isolation level from Python

With SQLAlchemy:

from sqlalchemy import create_engine

# engine-wide default
engine = create_engine(DATABASE_URL, isolation_level="SERIALIZABLE")

# or per-connection, for just the endpoints that need it
with engine.connect().execution_options(
    isolation_level="SERIALIZABLE"
) as conn:
    with conn.begin():
        ...

With asyncpg, set it when opening the transaction:

async with pool.acquire() as conn:
    async with conn.transaction(isolation="serializable"):
        stock = await conn.fetchval(
            "select stock from books where id = $1", book_id
        )
        ...

With Django, the level is configured per database in settings.py (OPTIONS: {"isolation_level": ...} for PostgreSQL via psycopg); transactions themselves are transaction.atomic() blocks.

And the part that's easy to forget — Serializable and Repeatable Read abort transactions under contention, and you must retry:

import asyncpg

async def buy_book_with_retry(pool, book_id, max_attempts=3):
    for attempt in range(max_attempts):
        try:
            async with pool.acquire() as conn:
                async with conn.transaction(isolation="serializable"):
                    return await do_purchase(conn, book_id)
        except asyncpg.SerializationError:
            if attempt == max_attempts - 1:
                raise
            # brief backoff, then re-run the WHOLE transaction

The retry must re-execute the entire transaction from the top — re-reading stock included — not just replay the final write. If your purchase flow has side effects (emails, payment calls), they need to live outside the retried block, which is a preview of Part 3's territory.

Choosing a level in practice

Read Committed + explicit locking or atomic updates is the workhorse. You handle the hot paths (inventory, balances) with row locks or single-statement updates, and everything else enjoys high throughput. This is what Part 3 is about.
Repeatable Read fits multi-read consistency: reports, exports, anything that reads several times and needs one coherent snapshot.
Serializable fits invariants you can't express as a row lock or constraint — especially set-level rules where phantoms bite ("max 5 open orders"). Budget for retries.
Read Uncommitted — no. Postgres won't give it to you anyway.

Summary

Four anomalies — dirty reads, lost updates, non-repeatable reads, phantom reads — and four levels that progressively eliminate them, paid for in throughput and retry logic. The pragmatic production posture is usually not cranking isolation to maximum: it's staying on Read Committed and protecting the few genuinely contended rows explicitly.

How to do that — SELECT ... FOR UPDATE, optimistic version columns, and atomic conditional updates — is Part 3.