Transactions.

A transaction is how you tell a database “these few changes belong together”: either all of them happen or none of them do, and nobody else ever sees the job half-finished.

Any code that reads a number, decides something, and writes a new number back needs this the moment two copies of it run at once. Usage meters, credit balances, quotas on how many browser sessions a customer may open, a count of jobs still running: the bug that breaks them throws no error, passes every test, and shows up weeks later as numbers that don’t add up. Knowing which guarantee you actually have, and which one you only assumed, is the difference between a system that’s correct under load and one that’s correct on your laptop. It’s also a favourite interview topic, because so few people can explain it precisely.

You’ve already met the core bug in Processes, threads and the GIL, where two threads each added one to a counter and one addition vanished. Here it moves into the database. The crash-safety half builds on What happens when you save a file? and Why not a JSON file?, and the tools you’ll meet here come back in The life of an API and Queues and logs.

Today

• Say in plain words what a transaction promises, one letter of the acronym ACID at a time (all-or-nothing, rules always hold, no peeking at half-done work, committed means it survives a crash), and name the failure each letter prevents.
• Make a credits service lose money when many requests hit it at once, prove it by adding up the totals, then fix it four different ways and know when to pick each.
• Name the five classic ways two overlapping transactions can see or produce wrong data, and say which of the database’s protection settings stops which, in Postgres specifically.
• Explain how Postgres lets every transaction see its own consistent picture of the data without making readers wait for writers: it keeps old copies of each row around instead of overwriting them.
• Cause two transactions to wait on each other forever, on purpose, read the error, and explain why people avoid transactions that span two databases, and what they do instead.

Before this, do Processes, threads and the GIL (the lost counter increment; this lesson is the same bug, one floor down) and What happens when you save a file? (what forcing data to disk actually promises). You need Postgres on your Mac or the Spark; Task 0 has three ways to get it. Plan on one evening.

The Problem

Every TinyFish agent run spends credits. Picture the simplest possible version of the code that charges for one step of a run:

def charge(conn, wallet_id, cost):
    balance = conn.execute(
        "SELECT balance FROM wallets WHERE id = %s", (wallet_id,)
    ).fetchone()[0]
    if balance < cost:
        raise InsufficientCredits()
    conn.execute("UPDATE wallets SET balance = %s WHERE id = %s", (balance - cost, wallet_id))
    conn.execute("INSERT INTO ledger (wallet_id, amount) VALUES (%s, %s)", (wallet_id, -cost))
    conn.commit()

Read the balance, check it, write the new balance, record the charge. It is correct. Every test passes. Then a customer launches a batch of agent runs, and eight workers start charging the same wallet at once.

I ran exactly this against Postgres 16 on an M4 Pro: one wallet starting at 100,000 credits, 8 threads, 500 charges of 1 credit each. The ledger ended up with 4,000 rows, one per charge, as it should. The balance went from 100,000 to 99,428. It should have been 96,000. 3,428 of the 4,000 charges never came off the balance. Run it again and you get a slightly different number (99,491 on my second run), never the right one.

Nothing crashed. No error was logged. The service just gave away 86% of the usage for free, and the only way you would ever notice is by adding up the ledger and comparing it to the balance.

Now a second, different way to lose money. A customer moves 500 credits from their personal wallet to their team wallet. That is two writes: subtract 500 here, add 500 there. The process dies between them (a deploy, an OOM kill, a power cut). 500 credits have left one wallet and arrived nowhere.

These are the two problems transactions exist to solve. One is crashing halfway. The other is two pieces of work interleaving. If you remember nothing else, remember that a transaction is the database’s answer to both.

The Idea

A transaction is a bracket

A transactiontransactionA group of database operations that the database treats as one unit: either all of them take effect, or none do, and other users never see it half-done. is a pair of brackets around some work:

BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE id = 'personal';
UPDATE wallets SET balance = balance + 500 WHERE id = 'team';
COMMIT;

BEGIN opens the bracket. COMMITCOMMITThe statement that ends a transaction and makes all of its changes permanent and visible to everyone, at one instant. closes it and says “make all of this real”. ROLLBACKROLLBACKThe statement that ends a transaction by throwing away every change it made, as if it never ran. closes it and says “forget all of it”. Everything inside the bracket gets four promises, and those are the four letters.

One detail that bites Python people: drivers like psycopg open a transaction for you on the first statement and keep it open until you call commit() or rollback(). Your naive charge() above was inside a transaction. It still lost money. Hold that thought: being in a transaction is not the same as being protected from the interleaving problem. That depends on the I, and the I has settings.

A is for atomic: all or nothing

AtomicityAtomicityThe guarantee that a transaction’s changes happen completely or not at all. If anything fails partway, the database undoes the part that already happened. fixes the transfer. If the process dies after the first UPDATE, the database acts as if the transaction never started. The 500 credits are still in the personal wallet.

“Atomic” here means indivisible, like the Greek word, not “about concurrency” (that’s the I). It is about failure: crashes, errors, a constraint that rejects the second write, or your own code deciding to ROLLBACK.

How can a database undo work after the process that was doing it is dead? It keeps a diary. Before changing anything in its data files, the database writes a record of the change to an append-only file, the write-ahead logwrite-ahead logWAL. An append-only file where the database records every change before applying it to the data files. After a crash, replaying it rebuilds exactly what was committed.. The last thing a transaction writes to the log is a commit record. After a crash, the database reads the log: transactions with a commit record are kept, transactions without one are treated as if they never happened.

Databases differ in how they undo. MySQL’s InnoDB keeps an undo logundo logA record of the old value of every row a transaction changed, so the database can put the old values back if the transaction aborts. and copies old values back on rollback. Postgres does something lazier and cleverer that we’ll see in the MVCC section: it never overwrites a row, it writes a new version and marks the transaction that made it as committed or aborted. An aborted transaction’s versions simply stay invisible forever, and get cleaned up later. Either way, the commit record is the single moment of truth.

D is for durable: committed means it survives

DurabilityDurabilityThe guarantee that once the database says COMMIT succeeded, the change survives crashes and power loss. is about the other side of that moment. When COMMIT returns, your data must survive a power cut one microsecond later.

You already know from What happens when you save a file? that write() returning means almost nothing: the bytes are in the OS page cache, in RAM. So Postgres, by default, does not answer your COMMIT until it has written the WAL and called fsync (or an equivalent) and the drive has said the bytes are on flash. The data files themselves can be updated lazily, minutes later, because the log is enough to rebuild them.

That has a price you can now put a number on. Every commit waits for a flush. On a typical NVMe SSD on Linux that’s very roughly tens of microseconds to a millisecond; on a Mac, the files-and-disks lesson showed how different “flushed” can mean (Postgres’s wal_sync_method setting decides which flush call it uses; on my Mac it reported open_datasync). Postgres softens this with group commit: when many transactions commit at once, one flush covers all of their commit records.

And there’s a dial. SET synchronous_commit = off lets COMMIT return before the flush. You might lose the last fraction of a second of commits in a crash (the docs bound it at three times wal_writer_delay, which defaults to 200 ms), but the database never ends up half-done, because the log is still the source of truth. That’s a reasonable trade for, say, analytics events. It is not one you make for a wallet.

C is for consistent: the rules always hold

ConsistencyConsistencyIn ACID: the guarantee that a transaction moves the database from one valid state to another, where “valid” means every rule you declared still holds. means the rules you wrote down hold before and after every transaction. The rules are constraintsconstraintsRules declared in the schema that the database enforces on every write: NOT NULL, UNIQUE, CHECK (some condition), FOREIGN KEY (this id must exist in that table).:

CREATE TABLE wallets (
  id      text PRIMARY KEY,
  balance bigint NOT NULL CHECK (balance >= 0)
);

With that CHECK, no transaction can ever commit a negative balance. Try, and the statement fails, and the transaction can only roll back. The failure it prevents: a bug, or a race, quietly writing nonsense that you discover in a month.

Honest note: C is the odd letter. Martin Kleppmann points out in Designing Data-Intensive Applications that consistency is mostly a property of your application, not the database. The database checks the rules you declared; it can’t know that “the ledger sum must equal the balance change” unless you find a way to declare it. A, I and D are the database’s tools; C is what you build with them.

I is for isolation: no one sees your half-done work

IsolationIsolationThe guarantee that concurrently running transactions don’t interfere with each other: each behaves (to some degree, set by the isolation level) as if it were running alone. is the one that would have saved the credits service, and the one with settings.

The ideal is simple to state. Run a thousand transactions at once, and the result must be the same as if they had run one at a time, in some order. That ideal is called serializableserializableThe strongest isolation: the outcome of running transactions concurrently is always equal to running them one after another in some order., because the outcome matches a serial schedule. It is expensive, so databases offer weaker levels too, and each weaker level lets certain specific bad things through. Those bad things have names. Let’s meet them.

The lost update, again

You’ve seen this bug. In the threads lesson, two threads ran counter += 1, each read 41, each wrote 42, and one increment vanished. The credits bug is the identical shape, one level down: two transactions read the same balance, each subtracts its own cost from the value it read, and the second write erases the first.

A lost updatelost updateTwo transactions read the same value, both compute a new value from it, and both write. The second write silently overwrites the first, so the first change is lost. is the anomaly that cost 3,428 credits. Here are the other four.

The other anomalies

A dirty readdirty readReading a change another transaction has made but not yet committed. If that transaction rolls back, you acted on data that never existed.: T1 deducts 100 credits for a big run, and before it commits, T2 reads the wallet, sees 0, and rejects the user’s next request for “no credits”. Then T1’s payment step fails and it rolls back. T2 refused a customer based on a balance that never officially existed.

A non-repeatable readnon-repeatable readReading the same row twice in one transaction and getting two different values, because another transaction changed it and committed in between.: a billing report reads the wallet balance at the top (“100”), computes a few things, reads it again for the invoice total (“90”), and prints an invoice that disagrees with itself.

A phantomphantomA row that appears in (or disappears from) the result of a query you run twice in one transaction, because another transaction inserted or deleted a matching row in between.: you SELECT count(*) FROM runs WHERE wallet_id = 'w1', get 2, and a moment later the same query in the same transaction returns 3. Nobody changed a row you read; a new row appeared that matches your condition. Row locks can’t stop that, because you can’t lock a row that doesn’t exist yet.

And the subtle one, write skewwrite skewTwo transactions each read some shared facts, each decide a write is safe based on those facts, and each write a different row. Each alone is fine; together they break a rule neither one broke by itself.. The classic example, from Kleppmann’s book: a hospital needs at least one doctor on call. Alice and Bob are both on call, and both feel sick. Each one, at the same moment, opens the app, which runs:

SELECT count(*) FROM doctors WHERE on_call;  -- sees 2
-- 2 >= 2, so it's safe for me to leave
UPDATE doctors SET on_call = false WHERE name = 'alice';  -- (bob's app: name = 'bob')
COMMIT;

Both see 2. Both leave. Nobody is on call. Notice that they wrote different rows, so there was never a moment when both wanted the same row. No row lock was ever contested. That’s why write skew slips past defenses that stop the lost update.

You have a TinyFish-shaped version of this too. “A customer may have at most 3 concurrent browser sessions”: count the open sessions, if fewer than 3, insert a new one. Two requests both count 2, both insert, and the customer has 4. Same shape, with a phantom on the side (the new rows are the thing each check couldn’t see).

How Postgres keeps transactions apart: MVCC

Before the isolation levels make sense, you need the trick Postgres uses to give each transaction its own view of the data. The simple approach is locks: readers lock what they read, writers wait for readers. It’s correct and slow: a long report would block every charge.

Postgres uses MVCCMVCCMulti-version concurrency control. Instead of overwriting a row, the database writes a new version of it and keeps the old one, so each transaction can read the version that was current for it. instead. It never updates a row in place. An UPDATE writes a brand new version of the row and marks the old one as replaced. Every row version carries two hidden fields:

• xmin: the ID of the transaction that created this version.
• xmax: the ID of the transaction that replaced or deleted it (empty if nobody has).

When a transaction reads, it uses a snapshotsnapshotA record of which transactions had committed at a given moment. A reader uses it to decide which row versions it is allowed to see.: essentially a list of which transactions had committed at the moment the snapshot was taken. A version is visible if the transaction that made it had committed in the snapshot and the transaction that replaced it (if any) had not. Everything else is invisible to you, even though it’s sitting right there on disk.

Three consequences fall out of this picture.

1. Readers never block writers, and writers never block readers. The report reads its old version while T3 writes a new one. No waiting.
2. Writers do block writers. Only one transaction at a time may create the next version of a given row. A transaction that updates a row holds a row lockrow lockA lock on a single row, taken automatically by UPDATE, DELETE or SELECT … FOR UPDATE, and held until the transaction commits or rolls back. Other writers of that row wait. on it until it ends, and a second writer waits. That wait is where the interesting decisions in the next section happen.
3. Old versions pile up, and someone has to clean them up. That someone is VACUUMVACUUMPostgres’s garbage collector. It removes row versions that no current or future snapshot can see, and reclaims the space.. A transaction left open for hours holds an old snapshot, which stops VACUUM from cleaning anything newer than it. That’s one reason long-open transactions are bad manners.

You can see these hidden fields yourself: SELECT xmin, xmax, * FROM wallets; works on any Postgres table. You’ll do it in the Build.

The isolation levels, and what Postgres actually does

The SQL standard defines four isolation levelisolation levelA setting, per transaction, that picks how much protection from concurrent transactions you get. Stronger levels prevent more anomalies and cost more (waits or retries).s by which anomalies they forbid. Every database then implements them a little differently, so the names are less useful than knowing what your database does. Here is Postgres, which is what you’ll use at work and here. The only thing that differs between the first three rows is when the snapshot is taken and what happens when you try to write a row someone else changed.

Some notes in plain words.

READ COMMITTED is the default, and it is what your naive charge() ran under. Each statement sees everything committed before that statement started. So it never sees uncommitted data (no dirty reads), but two SELECTs in one transaction can disagree, and nothing stops the read-then-write pattern: your SELECT saw 100, your UPDATE ... SET balance = 75 writes 75, whatever the row says now.

REPEATABLE READ in Postgres is what the research literature calls snapshot isolationsnapshot isolationEach transaction reads from one consistent snapshot of the database taken when it started, and a write conflicts if someone else committed a change to the same row after that snapshot. Coined in a 1995 paper by Berenson et al.. The whole transaction reads one frozen picture. If you try to update a row that someone else changed and committed after your snapshot, Postgres refuses with could not serialize access due to concurrent update rather than let you write over a change you never saw. That turns the silent lost update into a loud error. (Postgres’s version also blocks phantoms, which the standard doesn’t require of this level.)

SERIALIZABLE in Postgres (since 9.1, in 2011) is SSISSISerializable snapshot isolation. Snapshot isolation plus bookkeeping of which transaction read data that another wrote; when those dependencies could form a cycle that no serial order explains, one transaction is aborted.. It runs exactly like REPEATABLE READ, plus it remembers what each transaction read. If T1 read something T2 wrote and T2 read something T1 wrote, there is no serial order that explains both, so one of them gets aborted with could not serialize access due to read/write dependencies among transactions. It doesn’t block extra; it lets transactions run and then vetoes the ones that would break the illusion. It can also abort some transactions that would actually have been fine (false positives). Either way, the deal is: at SERIALIZABLE, any transaction can fail with SQLSTATE 40001, and your code must retry it.

Two warnings about other databases, so the names don’t mislead you later. MySQL’s InnoDB defaults to REPEATABLE READ, with different rules from Postgres’s (its locking reads see the latest data, not the snapshot). And Oracle’s “SERIALIZABLE” has historically been snapshot isolation, which allows write skew. Same words, different guarantees. Kleppmann’s Hermitage project (in Go Deeper) tests exactly these differences.

Now play with it. The figure below is a small simulation of Postgres’s rules: real row versions with xmin and xmax, a snapshot per statement or per transaction depending on the level, row locks, and a simplified version of the serializable check. Pick a scenario and a level, then step T1 and T2 in whatever order you like (or play the bad order). Watch the middle column: every write adds a version, and what each transaction “sees” depends only on its snapshot.

Fixing the lost update: four tools

Back to the credits service. You have four standard fixes. They all work. They differ in who waits, who retries, and how much code you write.

1. Do the arithmetic in the database. Don’t read the balance into Python at all:

UPDATE wallets
   SET balance = balance - 10
 WHERE id = 'w1' AND balance >= 10
RETURNING balance;

This is an atomic updateatomic updateA single UPDATE statement that computes the new value from the current one inside the database (balance = balance - 10), so there is no gap between the read and the write for another transaction to slip into.. Even at READ COMMITTED it can’t lose an update: the UPDATE takes the row lock, and if another transaction holds it, it waits; when the lock is released, Postgres re-reads the newest version of the row and re-checks the WHERE against it before writing. The balance >= 10 is the insufficient-credits check, folded into the same step, so two charges can’t both pass the check against the same old balance. If zero rows come back, the wallet didn’t have enough. This is the fix to reach for first: one round trip, no retries, nothing to get wrong. It only works when the decision fits in one statement.

2. Lock the row before you read it. When the logic is too complex for one statement (look up the price plan, check a spending limit, then charge), take the lock up front:

SELECT balance FROM wallets WHERE id = 'w1' FOR UPDATE;
-- ... any Python logic you like ...
UPDATE wallets SET balance = %s WHERE id = 'w1';
COMMIT;

FOR UPDATE takes the row lock at read time, so a second transaction’s SELECT ... FOR UPDATE on the same row waits until you commit, and then reads your new value. This is pessimistic lockingpessimistic lockingAssume conflicts will happen and prevent them by locking data before using it. Others wait.: it assumes a collision and makes everyone queue. Correct and simple, and the cost is waiting: whatever happens between the SELECT and the COMMIT is time the row is held. Never make a network call (to an LLM, to Stripe) while holding it.

3. Check that nothing changed when you write. Add a version column. Read the balance and the version, compute, and make the write conditional:

UPDATE wallets SET balance = %s, version = version + 1
 WHERE id = 'w1' AND version = %s;   -- the version you read

If someone else wrote in between, the version moved on, the WHERE matches zero rows, and you start over from the read. This is optimistic concurrencyoptimistic concurrencyAssume conflicts are rare; do the work without locks, and at write time check that nothing changed since you read (usually with a version number). If something did, retry., the database version of the compare-and-swapcompare-and-swapA CPU instruction (and by analogy, any operation) that writes a new value only if the current value still equals what you expected, and reports whether it did. instruction. Nobody waits; losers retry. Brilliant when conflicts are rare, and the only option when the read and write happen in different requests (a user opens an edit form, saves it a minute later; HTTP’s ETag and If-Match headers are this idea). Terrible on one hot row, as you’ll measure.

4. Raise the isolation level and retry. Keep the naive code, run it at SERIALIZABLE (or REPEATABLE READ, which is enough for a single-row lost update), and wrap it in a loop that retries on error 40001:

for attempt in range(MAX_TRIES):
    try:
        with conn.transaction():
            ...naive read-check-write...
        break
    except psycopg.errors.SerializationFailure:
        continue  # someone else won; start the whole transaction again

The database detects the conflict for you. You don’t have to think about which rows to lock, which is the real appeal: SERIALIZABLE also catches the write-skew bugs you didn’t notice you had. The costs are retries under contention, and the discipline that every code path that touches the database must be retry-safe.

The rule of thumb: atomic update when the logic fits in one statement; FOR UPDATE when it doesn’t and contention is high; optimistic versions when contention is low or the transaction spans requests; SERIALIZABLE when the invariants are complicated enough that you don’t trust yourself to lock the right rows.

Deadlocks

Locks bring their own failure. Transfer A moves credits from wallet 1 to wallet 2: it locks 1, then wants 2. Transfer B, at the same moment, moves from 2 to 1: it locks 2, then wants 1. Each waits for the other, forever.

A deadlockdeadlockTwo or more transactions each waiting for a lock that another one holds, in a cycle, so none can ever proceed. doesn’t fix itself. Postgres runs a deadlock check when a transaction has been waiting for a lock longer than deadlock_timeout (1 s by default). If it finds a cycle, it picks a victim and aborts it with SQLSTATE 40P01, deadlock detected, and a detail line naming the processes and the row. The other transaction gets its lock and carries on. From the application’s side, a deadlock is just another error that means “retry the whole transaction”.

The prevention is boring and effective: always lock rows in the same order. Sort the two wallet ids and touch the smaller first, or SELECT ... WHERE id IN (1, 2) ORDER BY id FOR UPDATE. A cycle needs someone going the other way around. Short transactions help too, because a deadlock needs two transactions to be holding locks at the same time.

Across two databases: why people avoid distributed transactions

Everything so far happens inside one Postgres. Now suppose the wallet lives in Postgres and the record of the agent run lives in another service with its own database, and you want “charge credits and mark the run paid” to be atomic across both.

The textbook answer is two-phase committwo-phase commit2PC. A coordinator asks every participating database to prepare (do everything except the final commit and promise it can commit), and only if all say yes tells them all to commit.. Phase one, the coordinator asks each database “can you commit?”, and each one writes everything durably, keeps its locks, and votes yes. Phase two, if every vote was yes, the coordinator says commit. It works, and Postgres supports it (PREPARE TRANSACTION, off by default: max_prepared_transactions is 0). People avoid it because of the gap between the phases: a participant that voted yes has promised to commit and so can’t decide on its own anymore. If the coordinator dies right then, every participant sits holding its locks, possibly for a long time, until the coordinator comes back. Your wallet row is frozen because some other service crashed. Throughput is also bounded by the slowest participant and extra round trips. So most systems design around it.

The two tools they use instead:

Idempotency keys. Make every operation safe to repeat. The caller generates a unique key per logical action (“charge for step 7 of run abc”), and the credits service stores it with a UNIQUE constraint in the same transaction as the charge. A retry with the same key finds the row already there and returns the original result instead of charging twice. Now the caller can retry freely after any failure or timeout. Stripe’s API works exactly this way with its Idempotency-Key header. An idempotentidempotentAn operation that has the same effect whether you do it once or many times. “Set balance to 90” is idempotent; “subtract 10” is not, unless you deduplicate it. charge turns “exactly once” (impossible over a network) into “at least once, with duplicates ignored” (easy).

The outbox pattern. Don’t write to two systems. Write to one database, twice, in one local transaction: the charge, and a row in an outbox table saying “tell the runs service that run abc is paid”. A separate relay process reads the outbox and delivers those messages (to a queue or the other service), retrying until each is acknowledged. The local transaction makes the charge and the message atomic; the relay makes delivery eventual; idempotency on the receiving end makes duplicate deliveries harmless.

You’ll meet both again: idempotency keys in The life of an API, and the relay-plus-queue half in Queues and logs.

Build It

Everything goes in one folder, say ~/workbench/txn/. One Postgres, one Python file per experiment, and one shared harness. Python threads are fine for this: psycopg releases the GIL while it waits on the network, so the threads genuinely overlap their transactions (exactly the I/O-bound case from the threads lesson). Each thread needs its own connection, because a connection can only be in one transaction at a time.

# harness.py
import os, threading, time
import psycopg

URI = os.environ.get("PG_URI", "postgresql://postgres:pg@localhost:5432/postgres")
START, WORKERS, N, COST = 100_000, 8, 500, 1

SCHEMA = """
DROP TABLE IF EXISTS ledger, wallets;
CREATE TABLE wallets (
  id      int PRIMARY KEY,
  balance bigint NOT NULL CHECK (balance >= 0),
  version int    NOT NULL DEFAULT 0
);
CREATE TABLE ledger (
  id        bigserial PRIMARY KEY,
  wallet_id int    NOT NULL REFERENCES wallets(id),
  amount    bigint NOT NULL
);
"""

def reset():
    with psycopg.connect(URI, autocommit=True) as c:
        c.execute(SCHEMA)
        c.execute("INSERT INTO wallets (id, balance) VALUES (1, %s)", (START,))

def audit(label, dt, retries=0):
    with psycopg.connect(URI) as c:
        bal = c.execute("SELECT balance FROM wallets WHERE id = 1").fetchone()[0]
        charged = -c.execute("SELECT coalesce(sum(amount), 0) FROM ledger").fetchone()[0]
    lost = (START - charged) - bal
    print(f"{label:14s} ledger says {charged:5d} charged, balance {bal}, "
          f"should be {START - charged}, lost {-lost:5d}, retries {retries:6d}, {dt:.2f}s")

def run(label, charge, workers=WORKERS, n=N):
    """Reset, then run `charge(conn)` n times in each of `workers` threads, all starting together."""
    reset()
    stats = {"retries": 0}
    gate = threading.Barrier(workers)
    def worker():
        with psycopg.connect(URI) as conn:
            gate.wait()
            for _ in range(n):
                stats["retries"] += charge(conn) or 0
    threads = [threading.Thread(target=worker) for _ in range(workers)]
    t = time.perf_counter()
    for th in threads: th.start()
    for th in threads: th.join()
    audit(label, time.perf_counter() - t, stats["retries"])

# naive.py
from harness import run, COST

def charge_naive(conn):
    with conn.transaction():
        ...  # SELECT balance; if too low, return; UPDATE to the computed value; INSERT ledger row

if __name__ == "__main__":
    run("naive", charge_naive)

# fixes.py
import psycopg
from psycopg import errors
from harness import run, COST

def charge_atomic(conn):
    """One UPDATE ... SET balance = balance - %s WHERE id = 1 AND balance >= %s RETURNING balance.
    Insert the ledger row only if a row came back. Same transaction."""

def charge_for_update(conn):
    """SELECT balance ... FOR UPDATE, then the same Python logic as the naive version."""

def charge_optimistic(conn) -> int:
    """Read balance and version. UPDATE ... WHERE id = 1 AND version = <what you read>.
    cur.rowcount tells you whether it matched. If 0, the transaction did nothing useful:
    leave it and loop. Return how many times you looped."""

def charge_serializable(conn) -> int:
    """The naive body, at SERIALIZABLE, in a retry loop that catches errors.SerializationFailure.
    The retry must restart the WHOLE transaction, not just the failed statement."""

if __name__ == "__main__":
    for name, fn in [("atomic", charge_atomic), ("for update", charge_for_update),
                     ("optimistic", charge_optimistic), ("serializable", charge_serializable)]:
        run(name, fn)

# skew.py
import psycopg
from harness import URI

def setup():
    """doctors(name text primary key, on_call bool not null), with alice and bob both on call."""

def attempt(level: str):
    setup()
    a, b = psycopg.connect(URI), psycopg.connect(URI)
    # 1. both: SET TRANSACTION ISOLATION LEVEL <level>
    # 2. a counts on-call doctors, then b counts (both should see 2)
    # 3. a takes alice off call, b takes bob off call
    # 4. a.commit(), then b.commit(): wrap each in try/except and print what happened
    # 5. print how many doctors are on call now

deadlock detected
DETAIL:  Process 46616 waits for ShareLock on transaction 58528; blocked by process 46617.
Process 46617 waits for ShareLock on transaction 58527; blocked by process 46616.
CONTEXT:  while updating tuple (0,2) in relation "wallets"

Measure

Fill this in. Run each configuration at least twice; timings are noisy (my Mac was busy with other work and the same run varied by 2-5x), but correctness and retry counts are stable.

My reference runs: M4 Pro, Postgres 16.2 (via pgserver), Python 3.12, psycopg 3, 8 threads × 500 charges of 1 credit, one wallet. Yours will differ in time; the shape should not.

What the numbers should tell you:

Naive loses most charges, not a few. A charge is three statements and a commit, each a round trip, so the window between read and write is a good fraction of a millisecond. Eight workers on one row nearly always overlap. Note what didn’t happen: no error, no constraint violation, ledger perfectly intact. Only the audit catches it.

REPEATABLE READ with no retry is correct but useless. Around 80% of charges fail with could not serialize access due to concurrent update. The database refused to lose the update, and handed the problem back to you. An isolation level without a retry loop just converts silent data loss into loud request failures.

Atomic UPDATE is the fastest correct option. No retries, the shortest lock hold (the lock is taken by the UPDATE and released at commit, with no Python in between). Workers queue on the row lock, but each one holds it very briefly.

FOR UPDATE is correct with zero retries, a bit slower, because the lock is held across a Python round trip (read, think, write). On one hot row, waiting in a queue beats retrying.

The retry-based fixes are slowest here, and that’s the point of the experiment. One row, eight workers is the worst case for optimism: most attempts lose and redo all their work. Roughly 4-5 failed attempts per success. Retry-based methods shine when conflicts are rare. Check it: change the harness so each charge picks one of 100 wallets at random (sum the ledger per wallet in the audit). Predict first: retries should collapse to near zero and the optimistic and SERIALIZABLE versions should catch up with the others. Measure it yourself.

Scaling question to keep in mind. All of these fixes funnel every charge for one wallet through one row lock, one at a time. If each charge holds the lock for about 0.25 ms, one wallet tops out around 4,000 charges a second, no matter how many servers you add. That’s back-of-envelope arithmetic; measure your own number by running atomic UPDATE with more workers until throughput stops rising. Explain It Back asks what you do when one customer needs more than that.

Break It

1. Do something non-repeatable inside the retry loop. Add a print("charged!") (stand-in for sending an email or calling Stripe) inside charge_serializable’s transaction, before the commit. Count the lines printed versus charges made: you’ll see thousands of extra “charges”. The database rolled back the failed attempts; the outside world did not. Side effects belong after the commit succeeds, or in an outbox row written in the same transaction (Figure 006).

2. Hold a lock while you wait on the network. In charge_for_update, add time.sleep(0.05) between the SELECT ... FOR UPDATE and the UPDATE, standing in for a 50 ms call to a pricing service or an LLM. Predict the throughput first: with the lock held for 50 ms, one wallet can do at most 20 charges a second, however many workers you add. Run with 8 workers and fewer charges. Then look at SELECT pid, state, wait_event_type, query FROM pg_stat_activity; from another session while it runs: you’ll see sessions waiting on a lock, and the holder as idle in transaction. That state is one of the most common causes of production Postgres slowdowns.

3. Remove the guard and the constraint. Drop the AND balance >= %s from the atomic version, start the wallet at 1,000 credits, and run 4,000 charges. Catch psycopg.errors.CheckViolation inside the charge function (otherwise the worker thread dies on the first one). With the CHECK (balance >= 0) in place, every charge after the money runs out fails with that error, its transaction rolls back (ledger row included), and the balance stops at exactly 0: consistency (the C) and atomicity doing their jobs together. Now recreate the table without the CHECK and run it again. The balance goes to -3,000, and nothing complains.

4. Run SERIALIZABLE on half the code. SSI only protects transactions that run at SERIALIZABLE. Run the doctors test with a at SERIALIZABLE and b at READ COMMITTED. Does write skew come back? It does: I ran it both ways round (a serializable, b read committed, and the reverse) and both commits succeeded with 0 doctors on call. SSI only tracks reads made by serializable transactions, and it can only abort a transaction that is itself serializable. The lesson: if you rely on SERIALIZABLE, set it as the database default (default_transaction_isolation), not per call site.

Explain It Back

Answer out loud, in plain words, as if in an interview. Then check.

Go Deeper

• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 7 “Transactions” (and Chapter 9 for distributed transactions). The best explanation of isolation levels and anomalies anywhere, including the doctors example. If you read one thing, read this.
• PostgreSQL docs, Chapter 13 “Concurrency Control”, especially Transaction Isolation and Explicit Locking. Precise about what each level does in Postgres, including the “re-check the WHERE on the newest version” behaviour of READ COMMITTED.
• Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O’Neil, Patrick O’Neil, “A Critique of ANSI SQL Isolation Levels”, SIGMOD 1995. Shows the SQL standard’s definitions were ambiguous, defines snapshot isolation, and names write skew.
• Dan Ports and Kevin Grittner, “Serializable Snapshot Isolation in PostgreSQL”, VLDB 2012. How SERIALIZABLE actually works in the database you use, by the people who built it. Builds on Cahill, Röhm and Fekete, “Serializable Isolation for Snapshot Databases”, SIGMOD 2008.
• Jim Gray, “The Transaction Concept: Virtues and Limitations”, VLDB 1981. Short, readable, and the origin of the whole idea. Gray and Reuter’s book Transaction Processing: Concepts and Techniques (1992) is the deep reference.
• Martin Kleppmann, Hermitage (github.com/ept/hermitage). Hand-run tests of every anomaly against many databases, showing how differently “REPEATABLE READ” behaves across Postgres, MySQL, Oracle and others.
• Brandur Leach, “Implementing Stripe-like Idempotency Keys in Postgres” (on brandur.org). A practical, detailed walkthrough of idempotency keys and transactional state machines, from someone who built them at Stripe.
• Kyle Kingsbury, Jepsen: Consistency Models. A map of every isolation and consistency guarantee and how they relate, from the person who tests whether databases actually keep their promises.

Connections

This lesson is Processes, threads and the GIL again, one floor down: the lost update is counter += 1 with a network round trip in the middle, and SELECT ... FOR UPDATE is a lock around the critical section. Durability is What happens when you save a file? applied to a log: the database is a very careful user of fsync.

It leans forward to Why not a JSON file? (build your own log and recovery, and see why the commit record is the moment of truth), Indexes and B-trees (Postgres’s SERIALIZABLE tracks reads using index ranges, so a missing index makes it abort more), and Queues and logs, where the outbox relay lives. Idempotency keys come back in The life of an API, and the “one hot row” ceiling is a How does it scale? problem: a lock is a queue, and a queue has a maximum throughput.