Somewhere in almost every backend there's a check that looks harmless. Before an order goes through, the app makes sure the item is in stock. Before a seat is booked, it counts the seats left. Before an expense is approved, it adds up what the team has already spent and makes sure the new amount still fits. Read, check, write: three lines of code that never fail in development.
Now picture a corporate-card product. The design team has a $5,000 monthly budget, and late on a busy afternoon two managers each approve a $3,000 expense within the same second. Each request adds up the month's approvals and gets $0, because neither has saved its row yet. Each sees that $3,000 fits, inserts its expense and returns 200. The books now show $6,000 approved against a $5,000 budget.
No log shows an error, and every test still passes, because tests send one request at a time. Each transaction was correct on its own. The trouble is what PostgreSQL let them do together, and that's set by the transaction's isolation level: the rule for how much one transaction can see of others running at the same time.
PostgreSQL's default, READ COMMITTED, allows the overspend. The obvious upgrade, REPEATABLE READ, doesn't stop it either. It gives each transaction a frozen snapshot of the data, and two transactions that read the same total and insert different rows pass it cleanly.
There are two ways to make the rule hold. Run the check at SERIALIZABLE and retry whenever PostgreSQL stops one of the transactions, or move the rule onto a single row so that requests for the same team take turns.
In this tutorial, you'll learn what each isolation level does in plain words. Then you'll reproduce three concurrency bugs in Python with psycopg 3, fix each one, and load-test the fixes with 400 approvals: a lost update, a report that doesn't add up, and that overspent budget. Everything was tested with PostgreSQL 18.6, Python 3.14.8 and psycopg 3.3.6.
PostgreSQL Isolation Levels in Plain Words
A transaction is a group of statements that succeed or fail together. While yours runs, other transactions run beside it and change the same tables. The isolation level answers one question: while my transaction runs, how much of their work do I see?
Picture the team's numbers in a shared spreadsheet that several managers edit at once:
- READ COMMITTED , PostgreSQL's default: every statement looks at the latest saved data. It's like refreshing the spreadsheet before every glance. You never see someone's half-typed edit, but two glances a second apart can show different numbers.
- REPEATABLE READ : the transaction takes a snapshot at its first query and reads only that snapshot until it ends. It's like working from a printout. The numbers never shift under you, but you don't see anything saved after you printed. If you try to change a row that someone else changed after your printout, PostgreSQL stops you with an error.
- SERIALIZABLE : the same printout, plus a referee. PostgreSQL keeps track of what each transaction read. If two transactions together produce a result that couldn't happen had they run one after the other, it cancels one of them, and your code runs it again.
The SQL standard also defines READ UNCOMMITTED, which would let you see other people's unsaved changes. PostgreSQL accepts the name but runs it as READ COMMITTED, so you can ignore it.
Each level shuts out more of the bugs you're about to meet:
| Bug | What goes wrong | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
|---|---|---|---|---|
| Lost update | Two requests read the same total, each adds to it, and the second write erases the first | Happens | Stopped with an error | Stopped with an error |
| Read skew | One transaction reads two numbers from two different moments, so they don't agree | Happens | Stopped | Stopped |
| Write skew | Two requests check the same data, each decides it's safe, and each writes a different row | Happens | Happens | Stopped with an error |
How each level treats the three bugs in this tutorial, per the PostgreSQL 18 isolation chapter. "With an error" means your code has to retry.
Stronger levels aren't free: they stop bugs by raising errors that your code must handle. So you'll watch each bug happen first, then pick the cheapest fix that stops it.
Prerequisites
To follow along, you need:
- Docker with Docker Compose. PostgreSQL and Python both run in containers, so you don't install either locally.
- Working knowledge of Python and of SQL transactions (
BEGIN,COMMIT,ROLLBACK). - One terminal, open in an empty folder.
Step 1: Set Up the Expense Approval Lab
You need a database with a budget in it and a Python image to run the demos. Each script plays two or more managers, each on its own connection, and runs their statements in a fixed order, so you get the same result every time.
Create the project folder:
mkdir isolation-lab && cd isolation-lab Create the Compose file:
docker-compose.yml
services: db: image: postgres:18.6 environment: POSTGRES_USER: app POSTGRES_PASSWORD: app POSTGRES_DB: spend volumes: - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro healthcheck: test: ["CMD-SHELL", "pg_isready -h localhost -U app -d spend"] interval: 1s timeout: 3s retries: 30 app: build: . environment: DATABASE_URL: postgresql://app:app@db:5432/spend volumes: - .:/lab working_dir: /lab depends_on: db: condition: service_healthy The db service runs schema.sql on its first start, through the official image's /docker-entrypoint-initdb.d folder, and only reports healthy once the schema has loaded. The app service mounts the project folder, so it runs the scripts you write.
The image installs one library:
Dockerfile
FROM python:3.14.8-slim WORKDIR /lab COPY requirements.txt . RUN pip install --no-cache-dir -r requirements.txt ENV PYTHONUNBUFFERED=1 requirements.txt
psycopg[binary]==3.3.6 The schema has two tables. teams holds each team's budget and a running total of what it has spent, and expenses holds one row per approved expense. Amounts are whole dollars, so the output stays easy to read:
schema.sql
CREATE TABLE teams ( id text PRIMARY KEY, name text, budget integer NOT NULL, spent integer NOT NULL DEFAULT 0 ); CREATE TABLE expenses ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, team_id text REFERENCES teams, amount integer NOT NULL, approved_by text NOT NULL, created_at timestamptz DEFAULT now() ); CREATE INDEX expenses_team_id_idx ON expenses (team_id); -- The design team has a $5,000 monthly budget. INSERT INTO teams (id, name, budget) VALUES ('design', 'Design', 5000); common.py holds the helpers every script shares:
common.py
import os import time import psycopg from psycopg import IsolationLevel DSN = os.environ.get("DATABASE_URL", "postgresql://app:app@localhost:5432/spend") LEVELS = { "read-committed": IsolationLevel.READ_COMMITTED, "repeatable-read": IsolationLevel.REPEATABLE_READ, "serializable": IsolationLevel.SERIALIZABLE, } def connect(name: str, level: IsolationLevel = IsolationLevel.READ_COMMITTED) -> psycopg.Connection: """One manager's session. The first statement opens a transaction at `level`, and it stays open until commit() or rollback().""" conn = psycopg.connect(DSN, application_name=name) conn.isolation_level = level return conn def reset(budget: int = 5_000) -> None: """Back to the start: design team, nothing spent.""" with psycopg.connect(DSN, autocommit=True) as conn: conn.execute("TRUNCATE expenses RESTART IDENTITY") conn.execute("UPDATE teams SET budget = %s, spent = 0 WHERE id = 'design'", (budget,)) def wait_until_blocked(name: str, timeout: float = 5.0) -> None: """Wait until session `name` is stuck waiting for a lock.""" with psycopg.connect(DSN, autocommit=True) as conn: deadline = time.monotonic() + timeout while time.monotonic() < deadline: row = conn.execute( "SELECT wait_event_type FROM pg_stat_activity WHERE application_name = %s", (name,), ).fetchone() if row and row[0] == "Lock": return time.sleep(0.01) raise TimeoutError(f"{name} never blocked") def log(who: str, message: str) -> None: print(f"[{who}] {message}", flush=True) def usd(amount: int) -> str: return f"${amount:,}" connect() opens one session per manager. psycopg sets the level through conn.isolation_level, and only between transactions: once a statement has run, the transaction is open and its level is fixed until commit() or rollback(). wait_until_blocked() watches pg_stat_activity, PostgreSQL's live list of sessions, until a manager is stuck waiting for a lock. reset() puts the data back, so you can run the scripts in any order.
Start PostgreSQL, waiting until it's healthy, and build the image:
docker compose up -d --wait db docker compose build app Check that the schema loaded:
docker compose exec db psql -U app -d spend -c "SELECT id, budget, spent FROM teams" The output will look similar to:
Output
id | budget | spent --------+--------+------- design | 5000 | 0 (1 row) One team, a $5,000 budget, nothing spent. Compose prints container status lines before each script's output; add 2>/dev/null to a docker compose run command to hide them.
Step 2: Lose an Update, Then Let PostgreSQL Do the Math
Start with the simplest way to lose money. Say every approval adds its amount to teams.spent. Alice approves $1,200 and Bob approves $800 at the same moment. Each reads the total, adds their amount in Python and writes the result back. lost_update.py stages that read-modify-write:
lost_update.py
"""Lost update: two managers add to the team's running total at the same time. Usage: python lost_update.py read-committed | repeatable-read | atomic """ import sys import threading from psycopg.errors import SerializationFailure from common import LEVELS, connect, log, reset, usd, wait_until_blocked ALICE_AMOUNT = 1_200 BOB_AMOUNT = 800 def read_modify_write(level_name: str) -> None: level = LEVELS[level_name] alice = connect("alice", level) bob = connect("bob", level) # Both managers read the total before either writes. a_spent = alice.execute("SELECT spent FROM teams WHERE id = 'design'").fetchone()[0] log("alice", f"read spent = {usd(a_spent)}") b_spent = bob.execute("SELECT spent FROM teams WHERE id = 'design'").fetchone()[0] log("bob", f"read spent = {usd(b_spent)}") # Each adds in Python and writes the result back. alice.execute("UPDATE teams SET spent = %s WHERE id = 'design'", (a_spent + ALICE_AMOUNT,)) log("alice", f"set spent = {usd(a_spent + ALICE_AMOUNT)}") alice.commit() log("alice", "commit") try: bob.execute("UPDATE teams SET spent = %s WHERE id = 'design'", (b_spent + BOB_AMOUNT,)) log("bob", f"set spent = {usd(b_spent + BOB_AMOUNT)}") bob.commit() log("bob", "commit") except SerializationFailure as e: log("bob", f"UPDATE failed: SQLSTATE {e.sqlstate}: {e.diag.message_primary}") bob.rollback() def atomic() -> None: alice = connect("alice") bob = connect("bob") # Let the database do the adding, on whatever the row holds right now. new = alice.execute( "UPDATE teams SET spent = spent + %s WHERE id = 'design' RETURNING spent", (ALICE_AMOUNT,), ).fetchone()[0] log("alice", f"add {usd(ALICE_AMOUNT)} -> spent = {usd(new)} (not committed yet)") def bob_adds() -> None: new = bob.execute( "UPDATE teams SET spent = spent + %s WHERE id = 'design' RETURNING spent", (BOB_AMOUNT,), ).fetchone()[0] log("bob", f"add {usd(BOB_AMOUNT)} -> spent = {usd(new)}") bob.commit() log("bob", "commit") t = threading.Thread(target=bob_adds) t.start() wait_until_blocked("bob") log("bob", "waiting for alice's row lock") log("alice", "commit") alice.commit() t.join() def main() -> None: variant = sys.argv[1] if len(sys.argv) > 1 else "read-committed" reset() print(f"--- lost update, {variant} ---") if variant == "atomic": atomic() else: read_modify_write(variant) with connect("check") as conn: spent = conn.execute("SELECT spent FROM teams WHERE id = 'design'").fetchone()[0] print(f"final spent = {usd(spent)} (expected {usd(ALICE_AMOUNT + BOB_AMOUNT)})") if __name__ == "__main__": main() Run it at READ COMMITTED first:
docker compose run --rm app python lost_update.py read-committed The output will look similar to:
Output
--- lost update, read-committed --- [alice] read spent = $0 [bob] read spent = $0 [alice] set spent = $1,200 [alice] commit [bob] set spent = $800 [bob] commit final spent = $800 (expected $2,000) Both managers approved, and the total says $800. Bob added his $800 to the $0 he had read, which was already out of date by the time he wrote, so his write erased Alice's $1,200. That's a lost update. PostgreSQL saw nothing wrong, because SET spent = 800 is a perfectly valid statement.
Now run the same code at REPEATABLE READ:
docker compose run --rm app python lost_update.py repeatable-read The output will look similar to:
Output
--- lost update, repeatable-read --- [alice] read spent = $0 [bob] read spent = $0 [alice] set spent = $1,200 [alice] commit [bob] UPDATE failed: SQLSTATE 40001: could not serialize access due to concurrent update final spent = $1,200 (expected $2,000) This time Bob's UPDATE fails with SQLSTATE 40001, a serialization failure. Bob is working from his printout, and the row changed after he printed it, so PostgreSQL refuses to let him overwrite it. The documentation says to retry the whole transaction from the start; on the retry Bob would read $1,200 and write $2,000. Step 5 builds that retry.
You don't need a stronger level for this bug, though. The atomic() variant lets the database do the adding with spent = spent + %s, at plain READ COMMITTED:
docker compose run --rm app python lost_update.py atomic The output will look similar to:
Output
--- lost update, atomic --- [alice] add $1,200 -> spent = $1,200 (not committed yet) [bob] waiting for alice's row lock [alice] commit [bob] add $800 -> spent = $2,000 [bob] commit final spent = $2,000 (expected $2,000) Bob's UPDATE found the row locked by Alice's uncommitted change and waited. Once she committed, PostgreSQL ran his + 800 against the new value, $1,200, not the $0 from before, as the READ COMMITTED documentation describes.
So the bug was never the isolation level. It was doing the math in Python, where the database can't tell that the number is stale.
Step 3: Read a Consistent Report with REPEATABLE READ
Readers have their own trap. At month-end, finance runs a report that prints the team's running total next to the sum of its expense rows. Every approval writes both in one transaction, so they should always match. read_skew.py runs the report while Carol approves $250 between its two queries:
read_skew.py
"""Read skew: a month-end report reads two numbers that should always agree. Usage: python read_skew.py read-committed | repeatable-read """ import sys import psycopg from common import LEVELS, connect, log, reset, usd def approve(conn: psycopg.Connection, amount: int, approver: str) -> None: """Record an expense and add it to the team's total, in ONE transaction.""" conn.execute( "INSERT INTO expenses (team_id, amount, approved_by) VALUES ('design', %s, %s)", (amount, approver), ) conn.execute("UPDATE teams SET spent = spent + %s WHERE id = 'design'", (amount,)) conn.commit() def main() -> None: level_name = sys.argv[1] if len(sys.argv) > 1 else "read-committed" reset() print(f"--- read skew, report at {level_name} ---") carol = connect("carol") approve(carol, 1_000, "carol") log("carol", "approved $1,000 (earlier this month)") report = connect("report", LEVELS[level_name]) spent = report.execute("SELECT spent FROM teams WHERE id = 'design'").fetchone()[0] log("report", f"teams.spent = {usd(spent)}") approve(carol, 250, "carol") log("carol", "approved $250 and committed") total = report.execute( "SELECT coalesce(sum(amount), 0) FROM expenses WHERE team_id = 'design'" ).fetchone()[0] log("report", f"sum(expenses.amount) = {usd(total)}") report.commit() verdict = "agree" if spent ==Source：https://dev.to/shahidyousuf/postgresql-isolation-levels-explained-with-python-12e8Related posts
SOLID Principles Explained: 5 Essential Principles for Writing Clean, Maintainable Code
How to Build a Professional Resume in 2026: Best Resume Formats, Templates, and ATS Tips
How to Explain Your Project in a Technical Interview
Stop Calling It "Refactoring" When You Are Just Moving The Bugs Around
PostgreSQL migrations that lock a busy table
