System Design, Step by Step

Part 4 · Databases & Data at Scale

ACID Transactions & Isolation Levels

Two people book the last seat; a transfer crashes halfway. What ACID really promises, the write-ahead log behind durability, the five anomalies isolation levels do (and don't) prevent, and the practical fixes for real race conditions.

Lesson 2112 min readintermediate#database#transactions

On this page

  1. The Problem
  2. The Core Idea
  3. How It Works
  4. Trade-offs
  5. In the Real World
  6. Interview Questions
  7. Key Takeaways
  8. Further Reading

The Problem

A concert has one seat left. Two people click "Book" at the same moment. Both requests ask "is the seat free?", and both get the answer yes. Both book it. Now two people have tickets for the same seat. This kind of bug is called a race condition: the result depends on which request is faster.

Here is another problem. You transfer ₹5,000 to a friend. The money leaves your account. Then the server crashes before the money reaches your friend's account. Where did the money go?

These are not rare cases. With thousands of users, and servers that sometimes crash, they will happen. A transaction is the tool a database gives you to protect against them. But it only helps if you understand what it really promises.

The Core Idea

Think about buying something at a shop. You hand over the money and the shopkeeper hands over the item. Either both happen or neither happens. You would never accept "you paid, but the item got stuck halfway".

A transaction is a group of database operations that the database treats as one all-or-nothing unit. BEGIN starts it. COMMIT saves all of it:

SQL
BEGIN;  UPDATE accounts SET balance = balance - 5000 WHERE id = 'you';  UPDATE accounts SET balance = balance + 5000 WHERE id = 'friend';COMMIT;

If anything fails before COMMIT, the database rolls back. A rollback undoes every change in the transaction, as if nothing had happened.

Isolation answers a second question. When many transactions run at the same time, how much can each one see of the unfinished work of the others? Most of the tricky bugs live here.

How It Works

ACID, letter by letter

A: Atomicity (all or nothing). ACID is a set of four promises that a transaction makes. The first is atomicity. Every change in the transaction is applied, or none is. If the server crashes after the first UPDATE, the database undoes it when it restarts.

C: Consistency (rules stay true). The data moves from one valid state to another valid state. Constraints (rules the database enforces, like balance >= 0, unique emails, valid foreign keys) are never broken. Note: "consistency" here is mostly about your application's rules. You enforce them with constraints and correct transactions. It is not the same "consistency" as in the CAP theorem (Lesson 27).

I: Isolation (transactions that run at the same time do not disturb each other). Ideally each transaction behaves as if it ran alone. In practice, databases offer different levels of isolation (below). A lower level is faster but less safe.

D: Durability (committed means saved). Once the database says "committed", the data stays saved. It survives crashes and power cuts.

How durability works: the write-ahead log

A database does not rewrite its data files directly on every change. That would be slow. A crash in the middle could also damage the files. Instead, it uses a write-ahead log (WAL). The WAL is a file where the database first writes down every change, one after another. Here is the order:

  1. 1
    Append the change to the WAL
    sequential, fast
  2. 2
    Force the log to disk (fsync)
    fsync tells the operating system to really write to disk. Now it survives a power cut
  3. 3
    Reply “COMMIT OK”
    the app can move on
  4. 4
    Apply to data files later
    in the background, in batches
  5. 5
    On crash & restart
    replay the log to restore committed work; uncommitted work is dropped
The write-ahead log. Commit means “the change is safe in the log on disk”, not “the data files are updated”. · flow

Appending to a log is fast, because it is sequential writing (one record after another, see Lesson 10). The same log is also used for replication (copying data to other servers, Lesson 24) and for point-in-time recovery (restoring the database to an exact moment, Part 7).

The problems isolation prevents

An "anomaly" is a wrong result that can happen when transactions run at the same time. Here are the five classic ones. In the examples, T1 and T2 are two transactions.

1. Dirty read: reading data that is not committed yet.

Text
T1: UPDATE price = 500 (not committed yet)T2: SELECT price → 500      ← reads T1's unfinished changeT1: ROLLBACK                ← the 500 never really existed

2. Non-repeatable read: you read the same row twice and get two different values.

Text
T1: SELECT balance → 10,000T2: UPDATE balance = 2,000; COMMITT1: SELECT balance → 2,000   ← same query, different answer

3. Phantom read: you run the same query twice and new rows appear.

Text
T1: SELECT COUNT(*) FROM bookings WHERE show = 7 → 99T2: INSERT booking for show 7; COMMITT1: SELECT COUNT(*) ... → 100   ← a "phantom" row appeared

4. Lost update: two transactions read a value, change it and write it back. One write wipes out the other.

step 1 / 7
T1
Database
T2
  1. read stock
  2. 10
  3. read stock
  4. 10
  5. write stock = 9
  6. write stock = 9
  7. Two items sold, stock shows 9 instead of 8 — fix: UPDATE … SET stock = stock − 1 WHERE stock > 0
A lost update. Two read-modify-write cycles overwrite each other. · sequence · step through

5. Write skew: two transactions each check a rule, then together they break it. This is a classic example from Martin Kleppmann's book. A hospital needs at least one doctor on call. Two doctors are on call and both feel sick:

step 1 / 7
Dr A (T1)
Database
Dr B (T2)
  1. how many on call?
  2. 2
  3. how many on call?
  4. 2
  5. set A off call; COMMIT
  6. set B off call; COMMIT
  7. Zero doctors on call 😱 — only Serializable (or locking both rows with FOR UPDATE) prevents this
Write skew. Each doctor changes a different row, based on a check the other one makes false. · sequence · step through

Each transaction was fine on its own. Together they broke the rule. Each one changed a different row, based on a check that the other one made false. The concert seat and double-booking bugs are often write skew.

Isolation levels

An isolation level is a setting that says which anomalies the database must prevent. The SQL standard defines four levels. A higher level is safer, but it can be slower or need more retries.

LevelDirty readNon-repeatable readPhantomLost update / write skew
Read UncommittedPossiblePossiblePossiblePossible
Read Committed✅ PreventedPossiblePossiblePossible
Repeatable Read / Snapshot✅✅ PreventedDepends on DBWrite skew possible*
Serializable✅✅✅✅ Prevented

*Exact behaviour differs between databases. For example, PostgreSQL's Repeatable Read prevents phantoms and prevents lost updates on the same row (the second writer gets an error). But it still allows write skew.

Default levels in popular databases:

  • PostgreSQL: Read Committed.
  • MySQL (InnoDB): Repeatable Read.
  • SQL Server and Oracle: Read Committed.

So by default, most databases do not prevent all anomalies. Many developers think "I am in a transaction, so I am safe". That is not always true.

How databases implement isolation

Locking (pessimistic). "Pessimistic" means you expect a clash, so you prevent it first. A transaction takes a lock on each row it reads or writes. A lock is a "do not touch" sign. Other transactions must wait until it is removed.

  • The classic full version is two-phase locking (2PL). A transaction takes locks while it works and releases them only at the end. This gives serializable isolation (the same result as if the transactions ran one at a time).
  • It is safe, but it can cause waiting and deadlocks (Lesson 4). A deadlock is when two transactions each wait for a lock that the other one holds, so neither can go on. The database detects deadlocks and cancels one transaction.

MVCC: Multi-Version Concurrency Control. Used by PostgreSQL, MySQL InnoDB, Oracle and others. The database keeps several versions of each row. Each transaction sees a snapshot. A snapshot is a consistent picture of the data as it was at one moment.

Text
Row "balance" versions:  v1: 10,000  (created by T5, visible to snapshots before T9)  v2:  2,000  (created by T9, visible to snapshots after T9)
  • Readers do not block writers, and writers do not block readers. This is great for performance.
  • Old versions must be cleaned up later. In PostgreSQL the cleanup job is called VACUUM.

Serializable Snapshot Isolation (SSI). PostgreSQL's Serializable level uses snapshots and also tracks which transaction read what. If two transactions would cause an anomaly like write skew, the database stops one of them. It fails (often at commit) with a "could not serialize" error. Your app must retry it.

Optimistic concurrency (in the app). "Optimistic" means you expect no clash, so you do not lock. You only check at the end. Add a version column and update the row only if the version has not changed:

SQL
UPDATE productsSET stock = 9, version = 8WHERE id = 991 AND version = 7;-- 0 rows updated? Someone else changed it first → re-read and retry

This is common in web apps and APIs. The HTTP ETag and If-Match headers work the same way.

Practical tools to fix real bugs

1. Let the database do the maths (atomic updates). An atomic operation is one step that cannot be split or interrupted. Do not read a value and then write it back from your app. Use one statement:

SQL
-- ❌ Race-proneSELECT stock FROM products WHERE id = 991;       -- app sees 10UPDATE products SET stock = 9 WHERE id = 991;-- ✅ AtomicUPDATE products SET stock = stock - 1WHERE id = 991 AND stock > 0;-- check "rows affected": 1 = success, 0 = sold out

2. Lock the rows you are about to change (SELECT ... FOR UPDATE).

SQL
BEGIN;SELECT * FROM seats WHERE show_id = 7 AND seat = 'A12' FOR UPDATE;  -- locks the row-- if status = 'free':UPDATE seats SET status = 'booked', user_id = 42 WHERE show_id = 7 AND seat = 'A12';COMMIT;

A second booking request waits for the lock. Then it sees status = 'booked'.

3. Unique constraints as a safety net. A unique index tells the database to refuse duplicates.

SQL
CREATE UNIQUE INDEX one_booking_per_seat ON bookings (show_id, seat);

Even if your code has a bug, the database refuses a second booking for the same seat.

4. Use Serializable for tricky invariants, like the doctors rule. An invariant is a rule that must always be true. Also retry on serialization errors.

5. Keep transactions short. Never keep a transaction open while you call an external API or wait for a user. A long transaction holds locks, blocks others and keeps old MVCC versions from being cleaned up.

Pessimistic — lock first
  • •SELECT … FOR UPDATE, two-phase locking
  • +Certain: the second transaction simply waits
  • −Waiting, and deadlocks under contention
Use whenHigh-conflict hot rows: the last seat, a flash-sale item
Optimistic — check at the end
  • •version column, ETag/If-Match, Serializable (SSI)
  • +No waiting; readers never block
  • −Conflicts surface as failures you must retry
Use whenConflicts are rare: profile edits, most web forms
Two ways to stop concurrent transactions clashing. · compare

Transactions across services

ACID works inside one database. Suppose an order service and a payment service each have their own database. You cannot wrap both in a single BEGIN ... COMMIT.

  • Two-phase commit (2PC) can coordinate them. A coordinator first asks every database "can you commit?". If all say yes, it tells them to commit. But 2PC is slow and fragile. If the coordinator dies, everyone waits.
  • Most microservice systems use sagas instead. A saga is a sequence of local transactions (one per service). If a step fails, compensating actions undo the earlier steps ("payment failed, so cancel the order"). Part 6 covers sagas.

This is one reason people say "do not split your database until you have to".

ACID vs BASE

Some NoSQL systems describe themselves as BASE: Basically Available, Soft state, Eventually consistent. That means they prefer to stay available and fast, instead of giving strict transaction guarantees. "Soft state" means the data may change over time even without new input. "Eventually consistent" means all copies become equal after a short time. Many modern NoSQL databases now offer ACID transactions in limited forms (for example, within one partition, or at extra cost). Always check exactly what is guaranteed.

Trade-offs

  • Higher isolation means fewer bugs. But you get more locking, more cancelled transactions to retry, and sometimes lower throughput (fewer transactions per second).
  • Lower isolation gives better performance. But you must think carefully about race conditions, often with explicit locks or atomic updates.
  • Pessimistic locking is simple and certain. But it can cause waiting and deadlocks when many transactions compete for the same rows (contention).
  • Optimistic concurrency has no waiting, but it needs retries. It is great when clashes are rare. It is poor when they are frequent (a flash sale on one item).
  • Distributed transactions give correct results across systems, but they add complexity and delay. Sagas are usually preferred.

In the Real World

Ticket booking and flash sales. Movie, concert and train booking systems face exactly the "last seat" race when thousands of people try to book popular seats at once. They combine row locks or atomic updates, unique constraints, and often short "seat holds" with a timeout. This way, each seat is sold only once.

Banking and payments. Every card payment, UPI transfer or bank transfer relies on atomic, durable transactions. Payment systems also keep ledgers, where every movement of money is written as entries that always balance. Payment companies are very careful about isolation, idempotency (doing an action twice has the same effect as doing it once, Lesson 34) and reconciliation (checking that two sets of records agree).

Jepsen testing. Kyle Kingsbury's Jepsen project tests databases under network failures and has found cases where databases, including well-known ones, did not give the isolation or consistency that their documentation claimed. It reminds us to understand and test the guarantees that we depend on.

Hermitage. Martin Kleppmann's open-source "Hermitage" project tests which anomalies each database's isolation levels actually prevent. It showed that the same level name (like "Repeatable Read") can mean quite different things in different databases.

Inventory bugs in e-commerce. "Overselling" (selling more items than exist) is a common and costly bug. It is usually a lost update or write skew. The fix is almost always an atomic conditional update or a constraint, not more application code.

Interview Questions

Q1Explain each letter of ACID.

Atomicity: all of a transaction's changes happen, or none do. Consistency: the database moves from one valid state to another, and constraints and invariants stay true. Isolation: transactions that run at the same time do not see each other's unfinished work (up to the isolation level you chose). Durability: once a transaction is committed, its data survives crashes. The write-ahead log gives this.

Q2What is write skew, and which isolation level prevents it?

Two transactions read the same condition. Each one updates a different row because of it. Together they break a rule (the on-call doctors). Snapshot / Repeatable Read allows this. Serializable prevents it. Or you can lock the rows you depend on with SELECT … FOR UPDATE.

Q3How would you stop overselling the last item in stock?

Make the check and the write one atomic statement: UPDATE products SET stock = stock − 1 WHERE id = ? AND stock > 0. Then check the affected row count. For more complex flows, lock the row with SELECT … FOR UPDATE. Also keep a constraint (CHECK stock >= 0) as a safety net.

Q4What is MVCC, and why is it popular?

Multi-Version Concurrency Control keeps several versions of each row, so each transaction reads a consistent snapshot. Readers never block writers and writers never block readers. This gives high concurrency. The cost is that old versions must be cleaned up (VACUUM in PostgreSQL).

Q5What's PostgreSQL's default isolation level, and what does it allow?

Read Committed. It prevents dirty reads. But it allows non-repeatable reads, phantoms, lost updates in read-modify-write code, and write skew. Being inside a transaction does not by itself keep you safe from race conditions.

Q6How do you keep data consistent across two microservices with separate databases?

Not with one ACID transaction. Two-phase commit is possible, but it is slow and fragile. Usually you use a saga. A saga is a sequence of local transactions. Each one publishes an event. If a later step fails, compensating actions (refund, cancel) undo the earlier steps. You also use idempotent handlers (safe to run twice) and an outbox (a table in the same database that holds the events to publish, so the data change and the event are saved together).

Key Takeaways

  • A transaction makes several changes all-or-nothing. ACID stands for Atomicity, Consistency, Isolation and Durability.
  • Durability comes from the write-ahead log. Isolation comes from locking and/or MVCC snapshots.
  • Know the anomalies: dirty reads, non-repeatable reads, phantoms, lost updates and write skew. The default level of most databases does not prevent all of them.
  • Fix races with atomic updates (SET x = x - 1 WHERE x > 0), SELECT ... FOR UPDATE, unique constraints, optimistic versions, or Serializable + retries.
  • Keep transactions short. Across services, use sagas rather than distributed transactions.

Further Reading

  • Designing Data-Intensive Applications by Martin Kleppmann (chapter 7, "Transactions"), the clearest explanation of these anomalies
  • The PostgreSQL documentation chapters "Transaction Isolation" and "Concurrency Control" (MVCC)
  • The paper "A Critique of ANSI SQL Isolation Levels" (Berenson et al., 1995)
  • The Hermitage project on GitHub by Martin Kleppmann
  • CMU 15-445 Database Systems lectures on concurrency control and recovery
  • The Jepsen website's articles on consistency models

Published by Jainik · Last updated · How these lessons are made

Keep learning

Part of the study guide

Related series