Your transactions are almost certainly not fully isolated, and the bugs this causes are rare, non-reproducible, and appear only under load. Here’s exactly what your database does and doesn’t guarantee.
Prerequisites: Transactions Time to read: ~24 minutes
Full isolation — serializability — means concurrent transactions produce the same result as if they’d run one at a time. It’s what everyone assumes they have.
It’s also expensive. Enforcing it requires either extensive locking (killing concurrency) or significant bookkeeping. So databases offer weaker levels that allow specific anomalies in exchange for throughput.
🚨 And they don’t default to the strong one:
| Database | Default isolation level |
|---|---|
| PostgreSQL | Read Committed |
| MySQL (InnoDB) | Repeatable Read |
| Oracle | Read Committed |
| SQL Server | Read Committed |
| CockroachDB | Serializable |
So unless you changed it, your transactions are not serializable. Most engineers don’t know this, and it’s the source of a category of bug that’s genuinely miserable to debug: it happens once a week, only in production, only under load, and never reproduces.
Learn these by their symptoms. Each is a specific way concurrent transactions can produce a result that no serial ordering could.
T1: UPDATE accounts SET balance = 400 WHERE id = 1; -- not committed
T2: SELECT balance FROM accounts WHERE id = 1; -- reads 400
T1: ROLLBACK; -- that 400 never existed
T2 acted on data that was never real. Prevented by every level above Read Uncommitted, so you will essentially never see this in practice.
T1: SELECT balance FROM accounts WHERE id = 1; -- 500
T2: UPDATE accounts SET balance = 400 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1; -- 400 ← different!
🚨 This is allowed at Read Committed — the default in Postgres and Oracle. If your transaction reads a value, does some logic, and reads it again, the two reads can disagree. Any report that sums a column twice, or any validation that re-checks a value, can behave inconsistently.
T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 10
T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 11 ← a new row appeared
Not a changed row — a new row matching your predicate. Harder to prevent, because you’d have to lock rows that don’t exist yet (predicate locks or range locks).
T1: SELECT quantity FROM inventory WHERE id=1; -- 10
T2: SELECT quantity FROM inventory WHERE id=1; -- 10
T1: UPDATE inventory SET quantity = 9 WHERE id=1; COMMIT;
T2: UPDATE inventory SET quantity = 9 WHERE id=1; COMMIT;
-- Two items sold, quantity went from 10 to 9. One sale vanished.
🚨 The most common real-world transaction bug, and it happens at Read Committed.
Three fixes:
UPDATE inventory SET quantity = quantity - 1 WHERE id=1 AND quantity > 0.
The database does the read-modify-write internally. Prefer this.SELECT ... FOR UPDATE before reading.WHERE clause.
→ Transactions🚨 The anomaly that catches out even experienced engineers, because each transaction is individually valid.
Rule: at least one doctor must always be on call.
Currently: Ayesha and Bilal are both on call.
T1 (Ayesha): SELECT COUNT(*) FROM doctors WHERE on_call = true; -- 2, fine
T2 (Bilal): SELECT COUNT(*) FROM doctors WHERE on_call = true; -- 2, fine
T1: UPDATE doctors SET on_call = false WHERE name = 'ayesha'; COMMIT;
T2: UPDATE doctors SET on_call = false WHERE name = 'bilal'; COMMIT;
-- Zero doctors on call. Both transactions individually respected the rule.
Note they wrote to different rows, so there’s no lost update and no lock conflict. Each read a premise that the other invalidated.
Where it appears in real systems:
Only Serializable isolation prevents write skew automatically. Otherwise you need a materialized conflict (lock a shared row that both transactions must touch) or a database constraint that makes the invalid state impossible.
| Level | Dirty read | Non-repeatable read | Phantom | Lost update | Write skew |
|---|---|---|---|---|---|
| Read Uncommitted | ❌ possible | ❌ | ❌ | ❌ | ❌ |
| Read Committed | ✅ prevented | ❌ | ❌ | ❌ | ❌ |
| Repeatable Read | ✅ | ✅ | ❌ (mostly) | ✅ (mostly) | ❌ |
| Serializable | ✅ | ✅ | ✅ | ✅ | ✅ |
🚨 “Mostly” is doing real work in that table, because implementations differ significantly:
PostgreSQL’s Repeatable Read is actually snapshot isolation — it prevents phantoms too (you see one consistent snapshot for the whole transaction) and detects lost updates, aborting one transaction with a serialization error. It’s stronger than the SQL standard requires. But it still allows write skew.
MySQL’s Repeatable Read uses next-key locking, which prevents phantoms for locking reads but has
different semantics for plain SELECT. It’s a genuinely different behaviour from Postgres under the
same name.
🚨 The lesson: the standard’s names are unreliable. Know what your database does. Saying that in an interview is a strong signal — it’s the kind of thing you only learn by being burned.
Two-phase locking (2PL) — the classic. Acquire read locks for reads and write locks for writes,
hold them all until commit, and use predicate/range locks to prevent phantoms. Correct, and slow:
readers block writers, contention is high, and deadlocks are frequent. Used by SQL Server and MySQL’s
SERIALIZABLE.
Serializable Snapshot Isolation (SSI) — the modern approach, used by PostgreSQL and CockroachDB. Optimistic: run transactions on snapshots with no locking, but track read/write dependencies. At commit, if the database detects a pattern that couldn’t have arisen from any serial ordering, it aborts one transaction.
✅ No locking, so readers never block. Performance is close to snapshot isolation when conflicts
are rare.
❌ Transactions can fail at commit with a serialization error — 🚨 your application must retry
them. Code that doesn’t handle 40001 will fail intermittently.
📐 SSI’s overhead is often only 5–20% under low contention, which makes SERIALIZABLE far more
practical in Postgres than most people assume. It’s worth considering as a default for
correctness-critical workloads, with retry logic in place.
def with_retry(fn, attempts=3):
for i in range(attempts):
try:
with db.transaction(isolation='serializable'):
return fn()
except SerializationFailure:
if i == attempts - 1:
raise
time.sleep(0.05 * (2 ** i) * random.random()) # backoff + jitter
| Workload | Level | Reasoning |
|---|---|---|
| Reporting, analytics, dashboards | Read Committed | Perfect isolation isn’t needed; throughput matters |
| Ordinary CRUD | Read Committed + atomic updates | Handle lost updates explicitly per operation |
| Reading multiple related tables consistently | Repeatable Read / Snapshot | One consistent view for the whole transaction |
| Money, inventory, booking, any invariant across rows | Serializable (+ retries) | Write skew is a real risk and the cost of being wrong is high |
🎙️ The nuanced answer interviewers like: “I’d use Read Committed as the default for throughput, but Serializable for the booking transaction specifically — that’s a check-then-write pattern, which is exactly where write skew bites, and double-booking a room costs more than the isolation overhead. It means handling serialization failures with a retry, which we’d need anyway for deadlocks.”
Per-transaction isolation is normal. You don’t pick one level for the whole application:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- just the booking logic
COMMIT;
Preventing lost updates — prefer the atomic update:
-- ✅ One statement, no race possible
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;
-- 0 rows affected → out of stock. No lock, no retry, no isolation level needed.
Preventing double-booking — use a constraint, not application logic:
-- The database makes the invalid state impossible
ALTER TABLE bookings ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
🚨 A constraint beats an isolation level every time. It’s enforced regardless of transaction level, application bugs, or which service wrote the row. If you can express an invariant as a constraint, do.
Materializing a conflict when you can’t use a constraint: have both transactions update a shared
row (e.g. a room_locks row) so they genuinely conflict and one is forced to wait or abort.
Uniqueness — UNIQUE constraints work at any isolation level. Don’t write
SELECT ... IF NOT EXISTS THEN INSERT; that’s a write skew waiting to happen. Use
INSERT ... ON CONFLICT DO NOTHING and handle the result.
| Level | Gain | Cost |
|---|---|---|
| Read Committed | Highest throughput, no retries | Non-repeatable reads, phantoms, lost updates, write skew |
| Repeatable Read / Snapshot | Consistent view; lost updates detected | Still allows write skew; more version retention |
| Serializable (2PL) | Full correctness | Locking, blocking, deadlocks, low concurrency |
| Serializable (SSI) | Full correctness, no locking | Transactions abort at commit; must implement retries |
| Constraints | Enforced always, regardless of level | Not every invariant can be expressed as one |
SERIALIZABLE meant 2PL and was avoided. Their documentation on it is
excellent and worth reading.UPDATE would do.SELECT ... IF NOT EXISTS THEN INSERT instead of a unique constraint.UPDATE inventory SET qty = qty - 1 WHERE id = 42 AND qty > 0 is atomic in one statement, so
there’s no lost update to worry about and no lock to hold.”These are quick and they’ll change how you write transactions permanently. Open two psql sessions.
1. Non-repeatable read at the default level:
-- Session 1 -- Session 2
BEGIN;
SELECT balance FROM accounts WHERE id=1; -- 500
UPDATE accounts SET balance=400 WHERE id=1;
COMMIT;
SELECT balance FROM accounts WHERE id=1; -- 400 ← changed mid-transaction
COMMIT;
Repeat with BEGIN ISOLATION LEVEL REPEATABLE READ and watch it return 500 both times.
2. Write skew — the important one:
CREATE TABLE doctors (name TEXT PRIMARY KEY, on_call BOOLEAN);
INSERT INTO doctors VALUES ('ayesha', true), ('bilal', true);
-- Session 1 -- Session 2
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call; -- 2 SELECT count(*) FROM doctors WHERE on_call; -- 2
UPDATE doctors SET on_call=false UPDATE doctors SET on_call=false
WHERE name='ayesha'; WHERE name='bilal';
COMMIT; COMMIT;
SELECT count(*) FROM doctors WHERE on_call; -- 0 ← invariant violated
Now run the exact same sequence with SERIALIZABLE and watch the second transaction abort with
could not serialize access due to read/write dependencies. Seeing that error fire is the moment
this chapter becomes real.
3. Lost update, and the one-line fix. Two sessions doing read-then-write on a counter. Watch one
update vanish. Then use UPDATE t SET n = n - 1 and watch it become correct with no isolation change.