Isolation levels: five anomalies in two psql windows and the 50,000 that vanished
Both requests committed, no errors, and part of the money is gone. We reproduce every anomaly from dirty read to write skew in two psql sessions and retry serialization failures properly in Django.
Picture a wallet holding 100,000 sum. Two requests land at the same moment. One is a payment provider webhook: 50,000 has been credited. The other is a purchase: 30,000 must be debited. Both handlers read the balance, both see 100,000. One writes 150,000, the other writes 70,000. Whoever writes last wins. If 70,000 is what remains, 50,000 just vanished: no error, no exception in the logs, everything committed.
The usual reaction is "but it was all inside transaction.atomic()". A transaction guarantees atomicity, all or nothing. How much concurrent transactions see of each other is a separate question, answered by the isolation level. PostgreSQL's default is Read Committed, and it does not protect you from the scenario above.
In this post we reproduce five classic anomalies by hand in two psql windows, look at what PostgreSQL does at each level, and then write Django code that sets the isolation level and retries a transaction on serialization failure.
Setting up the lab
Open two terminals and connect both to the same database with psql. Call them session A and session B. Run the statements in exactly the order shown, alternating between windows. First, the tables:
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Ali', 100), (2, 'Vali', 100);
CREATE TABLE orders (
id serial PRIMARY KEY,
status text NOT NULL
);
INSERT INTO orders (status) VALUES ('new'), ('new'), ('paid');
CREATE TABLE doctors (
id int PRIMARY KEY,
name text NOT NULL,
shift_id int NOT NULL,
on_call boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Dilnoza', 1, true), (2, 'Jasur', 1, true);
Each example continues from the state the previous one left behind, so balances change along the way.
How isolation works: snapshots and MVCC
PostgreSQL is built on MVCC (multi-version concurrency control). An UPDATE does not modify a row in place: it writes a new row version and marks the old one as deleted by a given transaction. Each version stores the ID of the transaction that created it (xmin) and the one that deleted it (xmax).
When a query runs, PostgreSQL takes a snapshot: which transactions had committed at that moment and which were still in flight. A row version is visible only if the transaction that created it had committed as of the snapshot. The core difference between isolation levels is when the snapshot is taken:
- Read Committed: a fresh snapshot at the start of every statement.
- Repeatable Read: one snapshot for the whole transaction, taken at its first query (not at
BEGIN). - Serializable: the Repeatable Read snapshot plus SSI (Serializable Snapshot Isolation), which tracks dangerous read/write dependencies between transactions.
Reads never block writes and writes never block reads. But when two transactions want to modify the same row, the second waits for the first one's row lock. What happens after the wait depends on the level too, as we'll see.
1. Dirty read: not in PostgreSQL
A dirty read is reading another transaction's uncommitted changes. The SQL standard allows it at Read Uncommitted, but in PostgreSQL Read Uncommitted behaves exactly like Read Committed:
-- Session A
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- not committed yet
-- Session B
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: B does not see A's uncommitted change
COMMIT;
-- Session A
ROLLBACK;
Under MVCC an uncommitted version is simply invisible to other snapshots, so there is no isolation level that "turns on" dirty reads in PostgreSQL.
2. Non-repeatable read: same query, two answers
In Read Committed every statement gets its own snapshot. Read the same row twice in one transaction and you'll see a commit that happened in between:
-- Session A
BEGIN; -- READ COMMITTED (default)
SELECT balance FROM accounts WHERE id = 1;
-- 100.00
-- Session B (autocommit)
UPDATE accounts SET balance = 40 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- 40.00: same query, same transaction, different answer
COMMIT;
Often harmless, but if a report or a validation consists of several queries, the numbers stop adding up: the first query sees one state, the second another. In Repeatable Read the whole transaction works off one snapshot:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- 40.00 (the snapshot is taken here, at the first query, not at BEGIN)
-- Session B (autocommit)
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- still 40.00
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: new transaction, new snapshot
Note that the snapshot is taken at the first query, not at BEGIN. Sitting idle after BEGIN does not freeze anything.
3. Phantom read: new rows show up
A phantom is a new (or vanished) row when you re-run a query with the same condition. Read Committed allows that as well:
-- Session A
BEGIN; -- READ COMMITTED
SELECT count(*) FROM orders WHERE status = 'new';
-- 2
-- Session B (autocommit)
INSERT INTO orders (status) VALUES ('new');
-- Session A
SELECT count(*) FROM orders WHERE status = 'new';
-- 3: a "phantom" row appeared
COMMIT;
The SQL standard permits phantoms at Repeatable Read, but PostgreSQL's Repeatable Read is snapshot-based and doesn't show them: rerun the example with BEGIN ISOLATION LEVEL REPEATABLE READ; and the second count(*) also returns 2.
4. Lost update: the problem from the intro
Now the dangerous one. The application reads the balance, computes the new value in Python, and writes it back (read-modify-write). Under Read Committed:
-- Session A
BEGIN; -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 + 50
-- Session B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 - 30
-- Session A
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1; -- 70.00: A's +50 is gone
Session B's UPDATE raised nothing; A's change was silently overwritten. Under Repeatable Read PostgreSQL notices: if a row the transaction wants to modify was changed and committed by someone else after its snapshot, you get an error:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session A
UPDATE accounts SET balance = 120 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 40 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
The SQLSTATE is 40001 (serialization_failure), meaning "run this transaction again from the start". If A hasn't committed yet, B's UPDATE waits: if A commits, B errors; if A rolls back, B proceeds.
You don't need Repeatable Read to avoid lost updates, though. In Read Committed an UPDATE behaves specially: if the row is locked it waits, then takes the latest committed version of the row, re-checks the WHERE clause against it, and evaluates the expression on that version. So when the read and the write are a single statement, everything is correct:
-- Safe even under READ COMMITTED: the read and the write are one statement
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Withdrawal with a check, also one statement
UPDATE accounts SET balance = balance - 30
WHERE id = 1 AND balance >= 30;
-- "UPDATE 0" means insufficient funds
5. Write skew: Repeatable Read can't help
Write skew is the sneakiest anomaly. Two transactions read the same data, make a decision based on it, and modify different rows. No row is written twice, so Repeatable Read sees no conflict.
The rule: at least one doctor must stay on call for the shift. Dilnoza and Jasur press "go off call" at the same time:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave too
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT; -- succeeds, no error
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 0
Each transaction did the right thing within its own snapshot, yet the rule is broken: nobody is on call. In money systems the same pattern shows up with rules like "the combined balance of two accounts must not go negative" or "one active promo code per user".
At Serializable PostgreSQL catches the dependency:
UPDATE doctors SET on_call = true; -- reset
-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
-- Session A
COMMIT; -- succeeds
-- Session B
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT: The transaction might succeed if retried.
How does SSI do it? A serializable transaction places a predicate lock (SIRead lock) on what it reads. It blocks nobody; it's just a marker saying "I read this". When another transaction modifies that data, an rw-dependency is recorded between them. Two such dependencies in a row (A → B → C, where A and C may even be the same transaction) form a dangerous structure, and PostgreSQL cancels one of the transactions with 40001. The error can surface at the UPDATE or at COMMIT, so your code must handle both.
Comparison table
| Anomaly | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|
| Dirty read | no | no | no |
| Non-repeatable read | yes | no | no |
| Phantom read | yes | no (in PostgreSQL) | no |
| Lost update (read-modify-write) | yes | no, 40001 error |
no, 40001 error |
| Write skew | yes | yes | no, 40001 error |
A stricter isolation level doesn't quietly fix an anomaly — it turns it into an error. Catching that error and retrying the transaction is your code's job.
In Django: fix the query first, then the isolation
Models for the examples:
# models.py
from django.db import models
class Account(models.Model):
owner = models.CharField(max_length=100)
balance = models.DecimalField(max_digits=12, decimal_places=2)
class Doctor(models.Model):
name = models.CharField(max_length=100)
shift_id = models.IntegerField()
on_call = models.BooleanField(default=True)
The classic Django lost update:
# Wrong: read-modify-write in Python, a lost update under READ COMMITTED
def deposit(account_id, amount):
account = Account.objects.get(pk=account_id)
account.balance += amount
account.save(update_fields=["balance"])
Often you don't need a different level at all. Folding the read and the write into one UPDATE, or locking the row up front, is enough:
# services.py
from decimal import Decimal
from django.db import transaction
from django.db.models import F
from .models import Account
class InsufficientFunds(Exception):
pass
def deposit(account_id: int, amount: Decimal) -> None:
# UPDATE accounts SET balance = balance + %s WHERE id = %s
Account.objects.filter(pk=account_id).update(balance=F("balance") + amount)
def withdraw(account_id: int, amount: Decimal) -> None:
updated = Account.objects.filter(pk=account_id, balance__gte=amount).update(
balance=F("balance") - amount
)
if updated == 0:
raise InsufficientFunds(account_id)
def withdraw_with_fee(account_id: int, amount: Decimal, fee: Decimal) -> None:
# When the logic does not fit into one UPDATE: lock the row first
with transaction.atomic():
account = Account.objects.select_for_update().get(pk=account_id)
total = amount + fee
if account.balance < total:
raise InsufficientFunds(account_id)
account.balance -= total
account.save(update_fields=["balance"])
F() compiles to UPDATE ... SET balance = balance + ..., and select_for_update() to SELECT ... FOR UPDATE. In the second case a concurrent transaction waits on its SELECT until the first one finishes.
Setting the isolation level in Django
With PostgreSQL, Django runs at Read Committed by default. You can change the level for the whole connection via OPTIONS:
# settings.py
import os
from django.db.backends.postgresql.psycopg_any import IsolationLevel
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "shop",
"PASSWORD": os.environ["DB_PASSWORD"],
"HOST": "localhost",
"PORT": "5432",
"OPTIONS": {
# default is READ COMMITTED; change it only if you mean it
"isolation_level": IsolationLevel.REPEATABLE_READ,
},
}
}
Moving the entire project to Serializable is rarely the right call, though: now any writing transaction can get 40001, and every one of them needs a retry. Usually only a handful of critical operations need Serializable. Django's atomic() has no isolation-level argument, so we send SET TRANSACTION ISOLATION LEVEL as the first statement inside the transaction and put the retry logic right there:
# db.py
import functools
import random
import time
from django.db import OperationalError, connection, transaction
# 40001 = serialization_failure, 40P01 = deadlock_detected
RETRYABLE_SQLSTATES = {"40001", "40P01"}
def _sqlstate(exc: Exception) -> str | None:
cause = exc.__cause__ # the original psycopg exception
# psycopg 3 uses .sqlstate, psycopg2 uses .pgcode
return getattr(cause, "sqlstate", None) or getattr(cause, "pgcode", None)
def serializable(retries: int = 5, base_delay: float = 0.02):
"""Run the function in its own SERIALIZABLE transaction, retrying on 40001/40P01."""
def decorator(func):
@functools.wraps(func)
def wrapper(*args, **kwargs):
if connection.in_atomic_block:
raise RuntimeError(
f"{func.__name__}() must start the outermost transaction"
)
for attempt in range(retries + 1):
try:
with transaction.atomic():
with connection.cursor() as cursor:
# must be the first statement of the transaction
cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
return func(*args, **kwargs)
except OperationalError as exc:
if _sqlstate(exc) not in RETRYABLE_SQLSTATES or attempt == retries:
raise
# exponential backoff with jitter
time.sleep(base_delay * (2**attempt) * random.uniform(0.5, 1.5))
return wrapper
return decorator
A few things matter here:
- The retry must wrap the whole transaction. Catching the error inside the transaction and repeating just the last statement is pointless: PostgreSQL has already put the transaction into the aborted state.
- That's why the decorator refuses to run inside an outer
atomic(). A nestedatomic()only creates a savepoint, andSET TRANSACTIONwon't work there. - Django wraps the psycopg error in
django.db.OperationalErrorand keeps the original in__cause__; that's where we read the SQLSTATE from. - The delay grows with each attempt and gets random jitter; otherwise two colliding requests retry in lockstep and collide again.
Write skew in Django:
# services.py
from django.db import transaction
from .db import serializable
from .models import Doctor
class LastDoctorError(Exception):
pass
@serializable()
def go_off_call(doctor_id: int, shift_id: int) -> None:
on_call = Doctor.objects.filter(shift_id=shift_id, on_call=True).count()
if on_call < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
# Alternative without SERIALIZABLE: lock the rows the decision depends on
def go_off_call_locked(doctor_id: int, shift_id: int) -> None:
with transaction.atomic():
doctors = list(
Doctor.objects.select_for_update()
.filter(shift_id=shift_id, on_call=True)
.order_by("pk") # same lock order everywhere, fewer deadlocks
)
if len(doctors) < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
If both doctors call go_off_call() at once, one succeeds, the other gets 40001, retries, sees a single on-call doctor in its new snapshot, and stops with LastDoctorError. The second variant (select_for_update) also works here, because the decision depends on concrete existing rows. If the decision depends on a row not existing (say, no other booking overlapping a time range), FOR UPDATE cannot lock a row that isn't there; you need Serializable, a unique constraint, or an exclusion constraint.
Production pitfalls
Side effects inside the retry. The function runs again. If it calls a payment provider over HTTP or does task.delay(), that happens twice. Move external effects into transaction.on_commit(): callbacks from a failed attempt are discarded with the rollback and only fire after a successful commit.
ATOMIC_REQUESTS = True. Every view is already inside a transaction, so the decorator above won't run. Mark such views with @transaction.non_atomic_requests, or do the retry outside the view, in a service layer.
Tests. Django's TestCase wraps each test in a transaction, so serializable code and concurrency scenarios have to be tested with TransactionTestCase, using separate threads and separate connections.
Only Serializable transactions are protected. SSI only tracks dependencies among transactions running at Serializable. If one code path writes a table at Serializable and another at Read Committed, the guarantee is gone.
Indexes and false positives. Predicate locks may be taken on the index pages that were read, or on the whole table. A sequential scan locks the entire table and you'll see more 40001 errors without any real conflict. Use EXPLAIN to confirm that critical queries use an index. Record retry counts as a metric, so you measure the problem instead of guessing.
Long read-only transactions. At Serializable even a transaction that only reads can be cancelled. For big reports there's DEFERRABLE:
-- Long read-only report under SERIALIZABLE that is never cancelled
-- with a serialization error (it may wait a bit at start instead)
BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
SELECT owner, sum(balance) FROM accounts GROUP BY owner;
COMMIT;
Long transactions in general. At Repeatable Read and Serializable, the longer a transaction stays open, the higher the chance of a conflict and the longer VACUUM can't remove old row versions. Don't wait on an external API inside a transaction.
Practical checklist
- Hunt down read-modify-write:
obj.field += x; obj.save()is suspect number one. - Where possible, merge read and write into one
UPDATE(F(),filter(...).update(...), check the row count). - For more complex logic, lock the row with
select_for_update()and keep lock order consistent. - If the decision depends on a row's absence, use a constraint or Serializable.
- At Serializable/Repeatable Read, retry the whole transaction on
40001and40P01, with backoff and jitter. - Move external side effects into
transaction.on_commit(). - Test concurrency with
TransactionTestCaseand two connections; reproducing by hand in twopsqlwindows is the fastest check. - Monitor retry counts and the rate of
40001.
Takeaway
The isolation level answers "how much do concurrent transactions see of each other", and PostgreSQL's default, Read Committed, allows lost updates and write skew. Most problems go away with one correctly written UPDATE or a select_for_update(). When a decision spans several rows or depends on a row not existing, Serializable is the right tool, but only together with a retry. Break every new critical flow once in two psql windows: it takes a few minutes and keeps money from disappearing.
// comments
Loading comments...