Transactions and Isolation Levels — Lost Updates and Write Skew

Here’s a bug I’ve seen in some form at almost every company with a wallet, credits or inventory feature. The code looks like this:

def debit(wallet_id, amount):
    with db.transaction():
        balance = db.fetchval("SELECT balance_paise FROM wallet WHERE id = %s", wallet_id)
        if balance < amount:
            raise InsufficientBalance()
        db.execute("UPDATE wallet SET balance_paise = %s WHERE id = %s", balance - amount, wallet_id)

It’s in a transaction. It checks the balance. It reads clean, and it passes every test. And under concurrent requests, it loses money. Not sometimes in theory; every time two requests overlap.

Let’s prove it, then fix it, and understand what isolation levels actually guarantee.

The Default: Read Committed

PostgreSQL’s default isolation level is read committed. As we saw in Lesson 6, each statement sees a fresh snapshot of everything committed before it started. That’s a good default: no dirty reads, readers never block writers, very few errors.

But “each statement sees committed data” doesn’t mean “the transaction is isolated from other transactions”. Two transactions can still interleave in ways that give wrong results.

Test 1: The Lost Update

Wallet 1 has ₹500 (50,000 paise). Two requests run at the same time: request A debits ₹100, request B debits ₹200. Each does a read-modify-write in the application, like the code above. Expected final balance: ₹200.

I ran exactly this with two real sessions:

• A: BEGIN; SELECT balance_paise ... → 50000. Sleeps 2 seconds (simulating app work).
• B: BEGIN; SELECT balance_paise ... → 50000. UPDATE ... SET balance_paise = 30000. COMMIT.
• A wakes up: UPDATE ... SET balance_paise = 40000 (50000 − 10000, from its old read). COMMIT.

 final_expected_20000
----------------------
                40000

₹400. B’s debit of ₹200 simply disappeared. No error, no warning, no log line. Both transactions committed successfully. In production this shows up as “wallet balances don’t match the ledger”, weeks later.

The transaction didn’t help, because under read committed, A’s UPDATE just overwrote whatever was there with a value computed from stale data.

Fix 1: Do the arithmetic in SQL (best for simple cases)

UPDATE wallet
   SET balance_paise = balance_paise - 10000
 WHERE id = 1 AND balance_paise >= 10000
RETURNING balance_paise;

When two of these run at the same time, the second one waits for the first to commit (row lock), then re-reads the current row and applies its change to that. The WHERE balance_paise >= 10000 check is also re-evaluated on the current value, so you can’t go negative. Real test with ₹200 and ₹100 debits at the same time:

 final
-------
 20000

Correct. If RETURNING gives you no row, the balance was insufficient. This pattern (atomic conditional update) covers most counters, stock quantities and balances.

Fix 2: Lock the row first with SELECT … FOR UPDATE

When the logic is too complex for one SQL statement:

BEGIN;
SELECT balance_paise FROM wallet WHERE id = 1 FOR UPDATE;   -- second request waits here
-- application logic: checks, calculations, maybe other queries
UPDATE wallet SET balance_paise = $new WHERE id = 1;
COMMIT;

FOR UPDATE takes a row lock at read time, so the second transaction waits at its own SELECT ... FOR UPDATE until the first commits, and then reads the new value. Simple and reliable. Keep the transaction short, because everyone else waits for it.

Fix 3: Repeatable read (and retry)

I ran the same race with both sessions in REPEATABLE READ:

ERROR:  could not serialize access due to concurrent update

The second writer failed instead of silently overwriting. Under repeatable read, Postgres refuses to let you update a row that someone else changed after your snapshot was taken. The balance ended at ₹300 (B’s write), and A’s transaction must be retried from the start by your code, which will then read ₹300 and correctly write ₹200.

That’s the key point about stronger isolation levels: they turn silent corruption into errors, and your code must handle those errors by retrying.

Test 2: Write Skew

Lost updates happen when two transactions write the same row. Write skew is sneakier: two transactions read the same data, each makes a decision based on it, and each writes a different row.

The rule in our delivery app: in each zone, at least one rider must stay on duty. Two riders in Koramangala, both on duty. Both tap “go off duty” at the same moment. Each transaction does:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM riders WHERE zone = 'Koramangala' AND on_duty;   -- 2, so OK to leave
UPDATE riders SET on_duty = false WHERE id = <me>;
COMMIT;

Real results:

--- REPEATABLE READ ---
 on_duty_seen_by_rider1: 2
 on_duty_seen_by_rider2: 2
 still_on_duty: 0                 -- rule broken

--- SERIALIZABLE ---
 on_duty_seen_by_rider1: 2
 on_duty_seen_by_rider2: 2
ERROR:  could not serialize access due to read/write dependencies among transactions
 still_on_duty: 1                 -- rule kept

Under repeatable read, both commit. No row was written by both, so there’s no conflict for Postgres to detect. Zero riders on duty.

Under serializable, Postgres tracks what each transaction read as well as what it wrote. It notices that each transaction’s decision depended on data the other one changed, and aborts one of them. Retrying that transaction, it now sees only one rider on duty and refuses to let them go.

Real-world write skew examples:

• Two bookings for the last seat in a bus (checking availability, then inserting a booking row).
• A user creating two accounts with the same username in two simultaneous requests when there’s no unique constraint.
• Two approvals both checking “is total approved below the limit?” before each adds its own approval.
• Doctors, riders or support agents going off shift, as above.

Fixes for write skew:

• SERIALIZABLE isolation for transactions that enforce such rules, with a retry loop.
• Lock the shared thing explicitly: SELECT ... FROM riders WHERE zone = 'Koramangala' FOR UPDATE locks all riders in the zone, so the second transaction waits and then sees the updated count. Or lock a parent row (the zone row) that every such transaction must touch.
• Use a constraint when possible: unique constraints (usernames, one booking per seat), exclusion constraints (no overlapping time slots). The database enforces these no matter what the isolation level is.

Isolation Levels in Postgres, Summarised

• Read committed (default): fine for most simple statements and single-row atomic updates. Dangerous for read-then-write logic in application code.
• Repeatable read: one snapshot per transaction. Great for consistent reports (all queries see the same moment). Prevents lost updates by raising errors. Doesn’t prevent write skew.
• Serializable: the result is as if transactions ran one at a time. Prevents all of these anomalies. Costs some overhead and more retries under contention.

MySQL’s InnoDB defaults to repeatable read, but its behaviour differs in details (for example, locking reads and gap locks). Don’t assume the same code is safe on both databases.

Writing Retry Loops

Serialization failures and deadlocks are normal at repeatable read and serializable levels. They use SQLSTATE 40001 (serialization failure) and 40P01 (deadlock). Your code should retry the whole transaction:

import random
import time
import psycopg
from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)

def run_serializable(conn, fn, attempts: int = 5):
    for attempt in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return fn(conn)
        except RETRYABLE:
            if attempt == attempts - 1:
                raise
            time.sleep(random.uniform(0, 0.05 * 2 ** attempt))   # small backoff with jitter

def go_off_duty(conn, rider_id: int, zone: str):
    on_duty = conn.execute(
        "SELECT count(*) FROM riders WHERE zone = %s AND on_duty", (zone,)).fetchone()[0]
    if on_duty <= 1:
        raise ValueError("At least one rider must stay on duty")
    conn.execute("UPDATE riders SET on_duty = false WHERE id = %s", (rider_id,))

run_serializable(conn, lambda c: go_off_duty(c, 1, "Koramangala"))

Rules for retry loops:

• Retry the entire transaction, including the reads. Never just re-run the failed statement.
• Keep side effects (emails, API calls, payments) outside the transaction, or make them idempotent, because the transaction body can run more than once.
• Cap the attempts and alert if retries are frequent; heavy contention on one row means the design needs another look.

Two More Useful Tools

SKIP LOCKED for job queues

Many apps use a table as a job queue. Multiple workers pick jobs:

BEGIN;
SELECT id, payload FROM jobs
 WHERE status = 'queued'
 ORDER BY created_at
 LIMIT 10
 FOR UPDATE SKIP LOCKED;
-- process the jobs, then
UPDATE jobs SET status = 'done' WHERE id = ANY($1);
COMMIT;

SKIP LOCKED makes each worker skip rows that another worker has already locked, instead of waiting. Workers never get the same job and never block each other. (We used the same trick for the outbox relay in the payments course.) Add a partial index on (created_at) WHERE status = 'queued' and this scales surprisingly far.

Advisory locks

Sometimes you need mutual exclusion for something that isn’t a single row: “only one process should run the daily settlement job”, “only one rebuild of this customer’s cache at a time”. Postgres advisory locks are named locks your application controls:

SELECT pg_try_advisory_lock(hashtext('daily-settlement-2026-10-27'));   -- true if you got it
-- ... do the work ...
SELECT pg_advisory_unlock(hashtext('daily-settlement-2026-10-27'));

Transaction-level versions (pg_try_advisory_xact_lock) release automatically at commit or rollback, which is safer. Be careful with connection poolers in transaction mode (Lesson 11): session-level advisory locks don’t work reliably there.

Pitfalls to Avoid

1. Read-modify-write in application code under read committed. It loses updates silently.
2. Thinking “it’s in a transaction, so it’s safe”. Transactions don’t serialise logic at the default level.
3. Check-then-act on different rows (write skew). Needs serializable, explicit locks, or constraints.
4. Using repeatable read or serializable without retry logic. Serialization errors are expected.
5. Retrying only the failed statement. Retry the whole transaction.
6. Side effects inside retryable transactions. They’ll happen more than once.
7. Long transactions holding FOR UPDATE locks. Everyone else waits.
8. Missing unique and exclusion constraints. They’re the strongest protection, at any isolation level.

Quick Recap

• Read committed is the default and allows lost updates and write skew.
• Real test: two concurrent debits left ₹400 instead of ₹200.
• Fix lost updates with atomic SQL updates, SELECT ... FOR UPDATE, or repeatable read plus retries.
• Write skew needs serializable, explicit locking of shared rows, or constraints.
• Handle SQLSTATE 40001 and 40P01 by retrying whole transactions; use SKIP LOCKED for queues and advisory locks for app-level mutexes.

Next lesson: locks in depth. Table and row locks, why a quick ALTER TABLE can freeze your whole app, deadlocks, and how to run schema migrations safely on a busy database.