Transactions, isolation levels and locking
ACID explained for interviews, the anomalies each isolation level allows, MVCC, optimistic versus pessimistic concurrency control, and how to stop double bookings and lost updates.
Reading is half of it. See this used in a real interview: walk through Design Ticketmaster →
Most correctness bugs in system design answers are concurrency bugs: two requests read the same value, both decide, both write. Transactions and isolation levels are the database’s tools for this, and knowing which anomaly each level allows is what separates "wrap it in a transaction" from an answer that actually prevents a double booking.
ACID in one sentence each
- Atomicity: all of a transaction’s writes happen, or none do.
- Consistency: constraints (unique keys, foreign keys, checks) hold before and after. This is mostly your schema’s job.
- Isolation: concurrent transactions do not see each other’s partial work, to a degree set by the isolation level.
- Durability: once committed, the data survives a crash (it is in the write-ahead log on disk, and usually on replicas).
Isolation is where the subtlety lives.
The anomalies
| Anomaly | What happens | Example |
|---|---|---|
| Dirty read | read another transaction’s uncommitted write | showing a payment that is then rolled back |
| Non-repeatable read | the same row read twice gives different values | a report sums a balance that changes mid-transaction |
| Phantom | a range query returns different rows the second time | "count bookings for this room tonight" changes |
| Lost update | two read-modify-write cycles overwrite each other | two increments of a counter, only one survives |
| Write skew | two transactions read overlapping data, each writes something different, together they break a rule | two doctors both go off call because each saw the other on call |
Isolation levels
| Level | Prevents | Still allows | Default in |
|---|---|---|---|
| Read uncommitted | (almost nothing) | dirty reads and everything else | rarely used |
| Read committed | dirty reads | non-repeatable reads, phantoms, lost updates, write skew | Postgres, Oracle, SQL Server |
| Repeatable read / snapshot | dirty and non-repeatable reads (and phantoms in Postgres) | write skew; lost updates in some engines | MySQL InnoDB |
| Serializable | all of the above | nothing: the result equals some serial order | CockroachDB, opt-in elsewhere |
Two practical points to say out loud. First, defaults differ: Postgres defaults to read committed, MySQL to repeatable read. Second, names differ from behaviour: Postgres’s repeatable read is snapshot isolation and detects lost updates; MySQL’s does not detect write skew.
MVCC: how snapshots work
Most databases implement isolation with multi-version concurrency control. Each write creates a new version of the row stamped with its transaction; each transaction reads the versions that were committed when its snapshot started. Readers never block writers and writers never block readers. Old versions are cleaned up later (vacuum in Postgres, purge in InnoDB), and long-running transactions hold back that cleanup, which is why a forgotten open transaction can bloat a database.
Preventing lost updates and double bookings
The patterns, from simplest to strongest:
1. Atomic operations. Let the database do the read-modify-write in one statement: UPDATE counters SET n = n + 1 WHERE id = ?. No race, no lock held by your code. Use this whenever the logic fits in one statement.
2. Conditional writes (compare-and-set). Make the write conditional on what you read: UPDATE seats SET held_by = ? WHERE id = ? AND held_by IS NULL. If zero rows changed, someone else won. This is optimistic concurrency and the backbone of most booking systems. DynamoDB condition expressions and Redis SET … NX are the same idea.
3. Unique constraints. Model the thing that must not happen twice as a row with a unique key: one row per (listing, night) makes double booking a constraint violation. See Design Airbnb.
4. Version columns (optimistic locking). Read version, then UPDATE … SET …, version = version + 1 WHERE id = ? AND version = ?; retry on conflict. Good when conflicts are rare and the logic is too complex for one statement.
5. Pessimistic locks. SELECT … FOR UPDATE locks the rows until commit, so the second transaction waits. Correct and simple, but under contention requests queue on the lock, and holding locks across a network call (a payment provider) is a classic outage.
6. Serializable isolation. The database detects conflicts and aborts one transaction, which the application retries. Strongest guarantee with the least thinking, at the cost of retries under contention.
| Contention | Good default |
|---|---|
| Low (most rows rarely touched at once) | optimistic: conditional writes or version columns |
| High on a few hot rows (a flash sale) | atomic operations, or queue the writes; avoid long locks |
| Complex invariants across rows (write skew risk) | serializable, or a single row that encodes the invariant |
Write skew deserves its own example
Rule: at least one doctor must be on call. Alice and Bob are both on call. Each starts a transaction, sees two doctors on call, and takes themself off. Under snapshot isolation both commits succeed, and nobody is on call. No row was written twice, so lost-update detection does not help.
Fixes: run at serializable; lock the rows you read (FOR UPDATE on both doctors); or materialise the invariant into one row (an "on-call count" both transactions must update, which turns write skew into an ordinary conflict).
Keep transactions short
- Never call another service inside a database transaction; locks and snapshots are held for the whole network round trip.
- Do the slow work first, then open a short transaction that checks and writes.
- For workflows that span services, use a saga with an outbox rather than a long transaction; see distributed transactions and idempotency.
In the interview
Whenever two users can act on the same thing (a seat, a night, a balance, a username), say how the race is resolved: "a conditional update on the seat row, so exactly one hold succeeds", or "a unique constraint on (listing, night)". Name the isolation level only when it matters, and mention write skew if the invariant spans more than one row.
Checklist
- Every contended resource and how the race is resolved.
- Atomic statements or conditional writes before explicit locks.
- Unique constraints for "must not happen twice".
- Isolation level when invariants span rows.
- No network calls inside transactions.
- Retries for optimistic failures, with a cap.