System Design Data
Transactions & Isolation Levels
Two fans tap Buy on the last concert ticket at almost the same moment. Each app reads “one seat left,” then both try to claim it. Without isolation rules, both can believe they won, or one can act on a balance that vanished mid-flight.
A groups the steps of one piece of work so they succeed or fail together. An is the safety dial that says how much overlap you allow between pieces of work that run at the same time. Turn it too low and buyers oversell the seat; turn it too high and every request waits or retries. This page shows how to find the setting that protects the money without slowing down everything else.
Ticket-checkout journey
One checkout opens a transaction, reads the seat count, and records its intent to buy.
Concurrent buyers
A second checkout reads the same “one seat left” before the first one commits.
Each read looked fine on its own, yet together the two commits oversell the seat.
Row locks, version columns, or Serializable with retries make one buyer lose cleanly.
What actually happens
The ticket race above is one of a small family of bugs, and they all come from the same source: many clients run at once, and their work interleaves. Without isolation rules you might peek at someone's unfinished transfer (a ), see a balance change between two reads inside one transaction (a ), watch a count shift because new rows appeared that match your query (a ), or lose a seat because two writers each acted on a stale view (a or ).
Databases fight these bugs with isolation levels, and the default matters because most code never changes it. Engines often default to (you only ever see committed data, but it may change if you read again); some default to with extra locking behaviour. is the strongest common SQL target: the result must match running the transactions one after another in some order. Under the hood, (multi-version concurrency control: the engine keeps several versions of a row so readers rarely block writers) and sometimes (serializable snapshot isolation: snapshot reads plus conflict detection) make the stronger guarantees affordable.
Isolation is the letter I in ACID. The full set (Atomicity: all-or-nothing commit scope; Consistency: valid states and constraints, not the CAP theorem kind; Isolation; Durability: committed writes survive crashes) lives on the ACID Properties note. Here we only anchor the I: how tightly your transaction is sealed off from others while it runs. Everything below is a precise answer to “how sealed?”
Your job is not to memorise vendor marketing. Name the anomaly, match it to a level or a lock, and justify the weakest setting that still protects the business rule (the invariant) you care about.
How to choose an isolation level
Knowing the anomaly names is step one; deciding how much to pay to prevent them is the actual design call. Start from the rule that must hold, then work down to the cheapest mechanism that keeps it true.
What must never become false
After concurrent commits, the business invariant still holds: capacity cannot go negative, two buyers cannot own the last seat, and "at least one doctor on call" cannot become zero. Weak isolation is allowed only where the product accepts the anomaly class, never on money, inventory, or exclusive ownership without locks, constraints, or retries.
1. Which invariant is at risk? Uniqueness, capacity, money sums, and ownership usually need stronger protection than dashboards, where a slightly stale number harms nobody.
2. Can a cheaper tool fix it? Sometimes SELECT … FOR UPDATE (lock just the rows you are about to change) or a version column (reject a write if the row changed since you read it) beats raising the whole database to Serializable.
3. Can clients retry? Serializable and paths abort more often on purpose. Design idempotent checkout (safe to run twice) and bounded retries before you flip the dial.
Simple rule of thumb
Start at for ordinary reads. Escalate contested money and inventory paths with locks, versions, or Serializable. Across services, prefer sagas (a chain of local transactions with compensating steps) over one giant distributed transaction. In simple terms: protect the race that loses money or trust; do not pay serializable cost on every analytics query.
Before you raise isolation
- Reproduce the bug at the current level with a concurrent test before changing anything.
- Check whether
SELECT … FOR UPDATEor a version column solves it more cheaply. - Confirm clients handle retries before enabling Serializable or SSI, which abort more often.
- For cross-service flows, ask whether a saga is clearer than one giant distributed transaction.
Numbers to know
- Often ~10–50×: the ballpark gap versus ad hoc application locking; it varies wildly, so measure.
- Often ~2–5×: Read Committed versus heavy-handed mutexes (one-at-a-time locks) inside services.
- 2,000 write QPS (queries per second) at a 5% abort rate: about 100 extra attempts per second.
These are orders of magnitude for intuition only. Measure your own workload before quoting them.
Failure timeline: last-seat race
Here is the hero story slowed down to individual steps, so you can see exactly where lets two valid-looking reads combine into an impossible result.
t=0 seats_left = 1 under Read Committed t=1 Txn A reads seats_left = 1; Txn B reads seats_left = 1 t=2 A writes seats_left = 0; B writes seats_left = 0 (each thinks OK) t=3 Both COMMIT succeed → two sold tickets for one seat t=4 With Serializable / FOR UPDATE / CHECK: one aborts; app retries Outcome Individually valid reads can still break the invariant together (write skew)
Why anomalies happen
The problem is the interleaving of observations and writes. Two buyers can both read “one seat remaining” before either writes, then each create a booking based on that same observation. Wrapping each buyer’s statements in a transaction does not automatically make the combined execution correct. The isolation mechanism or an atomic conditional update must prevent both decisions from committing against the same remaining capacity.
The seat race and the classic on-call example are the same shape of bug, . The diagram below replays it with two doctors: each transaction checks the rule alone, each passes, and together they break it.
Read the diagram definition
sequenceDiagram
participant TxnA as Txn A
participant DB
participant TxnB as Txn B
TxnA->>DB: Read invariant: 2 doctors on call
TxnB->>DB: Read same invariant: 2 doctors on call
TxnA->>DB: Delete Alice from on_call
TxnB->>DB: Delete Bob from on_call
DB-->>TxnA: Commit succeeds at weak isolation
DB-->>TxnB: Commit succeeds at weak isolation
Note over DB: Invariant broken: zero doctors on callHow to fix the pattern
Three families of fix exist, and they map onto the three questions in the chooser above.
Isolation ladder (weak → strict)
The fixes above all involve “more isolation” in some form, so here is the full ladder the SQL standard defines. The bottom rung has the fewest guarantees and is usually cheapest per transaction; the top is closest to serialized execution. Each step up removes more anomaly classes in theory, but engine quirks still matter.
Read Uncommitted
WeakestYou may read rows another txn has written but not committed yet.
Still tricky at this tier: Dirty, non-repeatable, phantom, skew-class races
Read Committed
Common defaultYou only ever see committed values, but those values can change if you read again.
Still tricky at this tier: Non-repeatable reads, phantoms, write skew (unless engine adds tricks)
Repeatable Read
Stable row rereadsRows you already read look the same for the rest of your txn; new matching rows may still appear unless blocked.
Still tricky at this tier: Phantoms / skew on some engines; gap-locking engines narrow phantoms with range locks
Serializable
Strongest (SQL)Effect should match running transactions one after another in some order: no dirty/non-repeatable/phantom/skew in the standard sense.
Still tricky at this tier: Higher latency / abort rate, not a logical “hole,” an operational cost
Matrix: levels vs anomalies
The same ladder as a grid. Possible means the SQL model allows that anomaly unless your specific database adds stronger behaviour; the asterisk marks the most common place engines do exactly that.
| Isolation level | Dirty read | Non-repeatable | Phantom | Write skew |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible* | Possible |
| Serializable | Prevented | Prevented | Prevented | Prevented |
* Some engines reduce phantoms at Repeatable Read using gap or next-key locks (locks on the empty space between index entries, so nobody can insert a new matching row).
Each anomaly: story, SQL sketch, and what stops it
The matrix tells you which level blocks which bug; these cards make each bug concrete. Memorise the one-sentence story and the soundbite; use the SQL only if an interviewer asks for a timeline.
Dirty read
You read another transaction's uncommitted change; they roll back and your decision was based on fiction. A is the one anomaly almost every default already forbids.
“I acted on data that was never real.”
Everyday: approving a loan off a balance someone was about to undo.
Txn A: UPDATE balance SET amt=0 WHERE user='alice';
-- not committed
Txn B: SELECT amt FROM balance WHERE user='alice';
Txn A: ROLLBACK;Stopped by: Read Committed and stronger
Lost update
A lost update commonly comes from computing a replacement value in application code after reading old state. If both callers read 10 and each writes 11, one increment disappears. An atomic increment avoids that particular race; a version-checked update can detect conflicting replacements. For more complex multi-row rules, fixing a single counter is insufficient, so identify the full invariant before selecting a locking or retry strategy.
Two transactions both read the same value, each computes a new value from that stale snapshot, and the last writer silently overwrites the other. A means work disappears with no error anywhere.
“Two writers, one survivor, no warning.”
Everyday: two editors save the same doc; one edit vanishes.
-- Both read count = 10
Txn A: UPDATE hits SET count=11 WHERE id=1;
Txn B: UPDATE hits SET count=11 WHERE id=1;
-- Expected 12 clicks; you may end up at 11Mitigate: Serializable, SELECT … FOR UPDATE, or an optimistic version column plus retry
Non-repeatable read
The same query twice in one transaction returns different column values because someone else committed in between. A is normal under Read Committed, so multi-step reports need a snapshot.
“Same query, different answer mid-flight.”
Everyday: checkout shows price A, refresh mid-payment shows price B.
Txn A: SELECT amt FROM balance WHERE user='alice'; -- $100
Txn B: UPDATE balance SET amt=50 WHERE user='alice'; COMMIT;
Txn A: SELECT amt FROM balance WHERE user='alice'; -- $50Stopped by: Repeatable Read and stronger
Phantom read
Queries that should return the same set of rows suddenly include new matches, because inserts or deletes changed membership. A hits counts and range scans, not single rows.
“The result set shape shifted.”
Everyday: quota “slots left” changes because new rows appeared.
Txn A: SELECT count(*) FROM orders WHERE date='2024-01-15'; -- 100
Txn B: INSERT INTO orders (date) VALUES ('2024-01-15'); COMMIT;
Txn A: SELECT count(*) FROM orders WHERE date='2024-01-15'; -- 101Stopped by: Serializable (often narrowed at Repeatable Read via range locks)
Write skew
Suppose two doctors are on call and each transaction checks that someone else is available before setting its own doctor off call. Both snapshots show two available doctors, and the transactions update different rows, so simple write-write conflict detection may let both commit. The final state has nobody available. Serializable execution, a shared row representing coverage, or appropriate locking of the decision set can enforce the cross-row rule.
Each transaction reads state that passes a rule alone; together their writes violate the rule. is the anomaly from the failure timeline, and the hardest one to spot in code review because every statement looks correct.
“Individually rational reads, collectively impossible.”
Everyday: two nodes each think “I can drain,” and capacity hits zero.
Invariant: at least one doctor on call
Txn A: SELECT count(*) FROM oncall; -- 2
Txn B: SELECT count(*) FROM oncall; -- 2
Txn A: DELETE FROM oncall WHERE doctor='alice'; COMMIT;
Txn B: DELETE FROM oncall WHERE doctor='bob'; COMMIT;Stopped by: true Serializable, or explicit constraints plus locking; not cured by Read Committed alone
Diagnose anomalies from symptoms
In production you rarely see the anomaly name; you see a complaint. This table works backwards from the symptom a user reports to the likely anomaly, the fixes worth considering, and a test that reproduces it.
| Observed symptom | Likely anomaly | Fix options | Test idea |
|---|---|---|---|
| Two users reserve the last seat | Lost update or write skew | Serializable, row lock, capacity constraint, reservation ledger | Run two concurrent checkout transactions against capacity = 1 |
| Quota count says 9, insert makes it 11 | Phantom read | Range lock, serializable transaction, materialized counter with lock | Concurrent insert while another transaction counts matching rows |
| Document edit disappears | Lost update | Version column, compare-and-set update, merge workflow | Two clients read version 5 and save version 6 |
| Balance changes during one report transaction | Non-repeatable read | Repeatable Read snapshot or explicit lock where needed | Read same row twice while another transaction commits between reads |
MVCC: how snapshots usually work
MVCC lets a reader see row versions appropriate to its snapshot while writers create newer versions. Snapshot timing depends on isolation: in PostgreSQL, Read Committed normally takes a new snapshot per statement, while Repeatable Read uses a stable transaction snapshot. A stable snapshot prevents some changing-read anomalies but does not automatically prevent write skew. Long snapshots also retain old versions, increasing cleanup and storage pressure.
Several fixes above rely on a “snapshot”, so it is worth seeing how engines provide one cheaply. Most OLTP databases (online transaction processing: many small reads and writes) avoid blocking every reader whenever someone writes by keeping multiple versions of a row, or an undo chain pointing backwards. That is . Your transaction reads the version that matches its snapshot; garbage collection eventually deletes versions no one can see any more.
Deeper detail (append-only tuples vs undo logs)
Engines implement “multiple versions” in two main ways; the difference shows up in what you have to monitor.
Append-only MVCC model
- Row headers record which transactions created or retired each tuple (a row version).
- Visibility compares those versions against your snapshot of transaction IDs that were active.
- Autovacuum, the background cleaner, reclaims tuples no snapshot can see any more.
Undo-log model
- The primary row holds the latest image; older states live in undo segments.
- Consistent reads walk the undo chain backwards to reconstruct the value they need.
- Very old read views delay purge, so monitor undo growth as an early warning.
Serializable snapshot isolation (SSI)
Snapshots explain fast reads; explains how some engines reach on top of them. It does not lock every row like textbook two-phase locking (2PL). It runs transactions on snapshots, tracks which transactions depend on which, and aborts one transaction if committing everyone would be impossible to serialize: trading rare retries for predictable correctness.
Two-phase locking (pessimistic mental picture)
- Grab locks before touching rows and release them only after commit, so overlap waits.
- Deadlocks are possible; the engine picks a victim and rolls that transaction back.
- Heavy overlap on hot rows turns into queues and lower throughput for everyone.
SSI (optimistic detection)
- Reads stay cheap on snapshots while writers proceed, so nobody waits for locks.
- The engine watches for dependency patterns that imply a non-serializable history.
- On conflict it aborts one transaction; the application backs off and replays idempotent segments.
When SSI shines: read-heavy workloads where occasional retries beat global locking.
Production habit: map SQLSTATE (the standard SQL error code) or driver codes for serialization failures, and retry with jitter (a small random delay). The next section budgets those retries.
Retry and contention budget
When the database reports a serialization failure, retry the complete transaction from fresh reads, not only its last statement. Keep external side effects outside the retrying block unless they have their own idempotency protection. If each attempt independently aborts with probability 5%, expected attempts per successful operation are 1 / 0.95, about 1.053. Real contention is correlated, so repeated retries can worsen the conflict; bound attempts and apply backoff.
SSI and Serializable both hand you aborts on purpose, so the retry path is part of the design, not an afterthought. Take the 2,000 write QPS example from Numbers to know above: a 5% abort rate is roughly 100 extra attempts every second, and if those retries fire immediately they pile onto the same hot rows and create a retry storm. Four habits keep that in check.
Distributed transactions across database nodes
Everything so far assumed one database. Once the seat inventory and the payment live in different services, no single engine can isolate the whole flow, and you choose between four broad approaches.
Two-phase commit (2PC)
Two-phase commit coordinates an atomic decision across participants: first prepare and retain the ability to commit, then apply the coordinator’s commit or abort decision. A prepared participant may need to wait if it cannot learn that decision, potentially holding locks and blocking other work. Consensus replication can make the coordinator’s decision more durable, but it does not make distributed coordination free. Keep the participants and transaction duration small when this guarantee is required.
A coordinator asks every participant to prepare, then tells all of them to commit or abort together. Coordinator or network faults mid-protocol can leave participants stuck until recovery. That is why microservices rarely embrace naive 2PC.
Three-phase commit
Shrinks some of those blocking windows yet stays complex; it is uncommon compared with choreography patterns, where services react to each other's events instead of following one coordinator.
Sagas
A chain of local ACID steps, each with a compensating action that undoes it if a later step fails. Preferred when ownership spans services; isolation becomes a business workflow concern rather than a database setting.
Globally ordered SQL
Global SQL engines invest in clocks plus commit protocols so isolation semantics stretch across regions; you pay latency and cost intentionally for that.
Which to reach for
What real engines default to
The SQL standard defines the ladder, but each engine picks its own starting rung and adds its own quirks. The first table gives a beginner note per engine family; the second turns the same defaults into a rule of thumb and adds stores that are not SQL at all.
| Database | Default isolation | Beginner note |
|---|---|---|
| MVCC relational engine | Read Committed | Great OLTP default; opt into Serializable per txn when money moves. |
| Gap-locking relational engine | Repeatable Read | Gap locks change the phantom story; read engine docs. |
| Commercial SQL engine | Read Committed | Optional snapshot isolation is popular for readers. |
| Undo-based SQL engine | Read Committed | Undo-based MVCC; semantics differ in edge cases. |
| Consensus SQL engine | Serializable | Retries are normal: build app logic for them. |
| Global SQL engine | External consistency | Globally meaningful commit order: latency reflects that. |
External consistency means the commit order is meaningful across the whole planet, not just one machine. The rule-of-thumb view below also covers key-value and wide-column stores, whose “consistency” knobs are quorums (how many replicas must agree), not SQL isolation levels.
| System | Typical default | Rule of thumb |
|---|---|---|
| MVCC relational engine | Read Committed | Escalate isolation only on hot paths that proved buggy. |
| Gap-locking relational engine | Repeatable Read | Watch deadlocks; explain gap locks if interviewer probes RR. |
| Consensus SQL engine | Serializable | Design APIs around optimistic retries. |
| Global SQL engine | External consistency | Latency-sensitive reads may need follower paths: know trade-offs. |
| Managed key-value table | Eventually consistent reads | Strong reads / Transact API when invariants demand it. |
| Leaderless wide-column store | Tunable per query | Match quorum to SLA; not SQL isolation levels. |
Try it: step through isolation anomalies
Choose a classic schedule and an isolation level, then advance one event at a time. Stronger levels block or retry schedules that would break invariants; weaker levels expose the anomaly for teaching.
Schedule timeline
- Tx A reads balance 100.
- Tx B reads balance 100.
- Tx A writes 110 (+10).
- Tx B writes 105 (+5) and commits last.
- Weakest level that blocks
- repeatable read
- Current level sufficient
- No
- Events replayed
- 0 / 4
Tx A reads balance 100.
Match the level to the invariant. Dashboards tolerate Read Committed; seat inventory and paired-row rules usually need Serializable or explicit locks.
Retries have a cost. Serializable aborts show up as serialization failures under load; budget retry backoff on hot rows.
Wrap-up
You have seen the anomalies, the ladder, the machinery underneath, and what changes once the data spans services. This card compresses it into a cheat sheet, three exercises, the primary sources, and the questions to carry into any design review.
Cheat sheet: symptom to prevention
| Symptom | Likely anomaly | Prevention options |
|---|---|---|
| Two saves, one wins silently | Lost update | Row lock, version column, Serializable |
| Doctor/nurse double booking | Write skew | Serializable, exclusion constraint |
| Report count changes mid-scan | Phantom read | Range lock, Serializable snapshot |
Exercises
- Doctors off call. Two doctors both go off call under Read Committed. Name the anomaly, then choose Serializable vs
SELECT … FOR UPDATEvs a constraint, and justify the cost. - Retry policy. Using the 2,000 write QPS and 5% abort figures from Numbers to know, estimate extra attempts per second and design a retry policy that does not storm the hot row.
- Weakest safe level. Pick the weakest isolation that still protects: (a) a public view-count dashboard, (b) seat inventory = 1, (c) a bank transfer debit plus credit. Say what still fails at each.
References
- ANSI SQL isolation levels, and Berenson et al.: A Critique of ANSI SQL Isolation Levels.
- PostgreSQL: Transaction Isolation explains snapshots, anomalies, and whole-transaction retries.
- Atul Adya: Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions (anomaly taxonomy).
What to remember
Contrast , , and . Explain why keeps many reads fast, and why 2PC can block while sagas compensate. Payments and inventory deserve or explicit locks; dashboards often survive plus thoughtful UX.
Questions to ask
- Which invariant is at risk: uniqueness, a sum, capacity, ordering, or ownership?
- How often do concurrent writes hit the same entity or range, creating hot rows?
- Can clients safely retry aborted transactions without duplicating any side effects?
- Is the workflow inside one database, or split across services that need sagas?
Metrics to watch
- Deadlocks, serialization failures, retry success rate, and retry latency, which reveal contention.
- Lock wait time, blocked sessions, long transactions, and vacuum or undo pressure.
- Conflicting updates per hot row and p99 (slowest 1%) transaction duration.
- Business-level invariant violations caught by reconciliation jobs that recount after the fact.
Practice and further reading
Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.
Focus for this lesson: Select isolation by anomaly and invariant, then verify lost updates, non-repeatable reads, phantoms, write skew, deadlocks, and retry behavior.
Explain the decision
How can snapshot isolation allow write skew?
Read the reasoning
Concurrent transactions read the same snapshot and update disjoint rows, so write-write conflict detection misses a cross-row invariant violation.
Apply it to a production scenario
Two doctors independently go off call after both see the other on call. How do you fix it?
Read one approach
Make the invariant conflict through a locked/updated guard row, predicate locking/serializable isolation, or a constrained data model, and retry serialization failures.
Work through the numbers
A transaction retries with 2% serialization failure. Roughly how many attempts for 100,000 logical transactions?
Read the worked calculation
About 102,041 expected attempts using 100,000 / 0.98, before correlated contention effects.
Reason through a failure
What must retry logic for serialization failures guarantee?
Read the recovery reasoning
The entire transaction restarts from a fresh snapshot, side effects are deferred or idempotent, and retry count/deadline is bounded.
Sources and further study
Continue with Concurrency Control Algorithms.