ACID Transactions and Isolation Levels
The concurrency bugs that only appear in production. What each isolation level actually prevents, why the default is weaker than you think, and how to pick locking strategy.
Covers: Atomicity, consistency, isolation, durability; read phenomena; MVCC; optimistic and pessimistic locking; deadlocks
Isolation levels are the most commonly misunderstood topic in databases, largely because the defaults are weaker than most engineers assume. Postgres defaults to Read Committed. MySQL defaults to Repeatable Read. Neither is Serializable - so anomalies your code does not handle are, by default, possible.
30-second answer
Atomicity means all-or-nothing. Consistency means the transaction moves the database from one valid state to another, respecting constraints. Isolation means concurrent transactions do not observe each other's partial work. Durability means a committed write survives a crash. In a distributed system, isolation is by far the hardest - atomicity across nodes is solvable with two-phase commit, and durability with replication, but providing serializable isolation across partitions requires global coordination that costs latency on every operation.
| Property | Guarantee | Mechanism | Distributed difficulty |
|---|---|---|---|
| Atomicity | All operations commit or none do | Write-ahead log, undo log | Medium - 2PC works but blocks on coordinator failure |
| Consistency | Constraints and invariants hold before and after | Constraints, triggers, application logic | Medium - cross-node constraints are expensive to enforce |
| Isolation | Concurrent transactions do not see partial work | Locks, MVCC, snapshots | Hardest - needs global ordering, so global coordination |
| Durability | Committed data survives crashes | fsync to WAL, replication | Low - replicate to a quorum and it is stronger than single-node |
How durability is actually implemented
On commit, the database appends the change to a write-ahead log and calls fsync before acknowledging. The data pages themselves are written later, lazily. If the process dies, recovery replays the log. This is why WAL disks are the ones you buy fast, and why fsync=off turns a durable database into a fast, lying one.
Say these points
- A = all-or-nothing, C = constraints hold, I = no visible partial work, D = survives crashes.
- ACID's C is constraint validity; CAP's C is linearizability. Different things.
- Isolation is the hardest property to provide across nodes.
- Durability is a spectrum: buffer → fsync → quorum replication, each costing latency.
Avoid these mistakes
- Conflating ACID consistency with CAP consistency.
- Assuming 'the database is ACID' means serializable by default.
- Treating durability as binary rather than as a chosen failure envelope.
Expect these follow-ups
- How does two-phase commit provide atomicity, and what is its failure mode?
- What exactly do you lose with asynchronous replication when the primary dies?
- Why is group commit a throughput win for durability?
30-second answer
Read Uncommitted allows dirty reads and is essentially never useful. Read Committed prevents dirty reads but allows non-repeatable reads and phantoms - it is the Postgres default. Repeatable Read gives a stable snapshot for the transaction's duration, preventing non-repeatable reads; MySQL's InnoDB also prevents most phantoms via gap locks, and it is MySQL's default. Serializable behaves as if transactions ran one at a time, preventing everything, at the cost of aborts or lock contention. Default to Read Committed, and use Serializable specifically for transactions that read data and then write based on what they read.
| Level | Dirty read | Non-repeatable read | Phantom | Lost update | Write skew |
|---|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible | Possible |
| Repeatable Read (snapshot) | Prevented | Prevented | Possible* | Prevented* | Possible |
| Serializable | Prevented | Prevented | Prevented | Prevented | Prevented |
*MySQL InnoDB's Repeatable Read prevents phantoms via gap locks; Postgres's snapshot isolation does not, but does abort on concurrent update conflicts. Write skew survives everything below Serializable.
The anomalies, concretely
- Dirty read - you read a value another transaction wrote but has not committed. It then rolls back, and you acted on data that never existed.
- Non-repeatable read - you read a row twice in one transaction and get different values, because someone committed in between.
- Phantom read - you run the same
WHEREquery twice and new rows appear, because someone inserted matching rows. - Lost update - two transactions read the same value, both compute a new one, and the second silently overwrites the first. The classic counter bug.
- Write skew - two transactions read an overlapping set, each checks an invariant that holds, and each writes a different row. Individually valid; jointly they break the invariant. This is the one that survives Repeatable Read.
-- ❌ Read-modify-write in application code: classic lost update
-- tx A: SELECT balance -> 100 tx B: SELECT balance -> 100
-- tx A: UPDATE balance = 90 tx B: UPDATE balance = 80 (A's change is gone)
-- ✅ 1. Atomic in-database update - no read-modify-write at all. Prefer this.
UPDATE accounts SET balance = balance - 10 WHERE id = 42 AND balance >= 10;
-- ✅ 2. Pessimistic lock: serialise the readers explicitly.
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -- blocks other lockers
UPDATE accounts SET balance = ? WHERE id = 42;
COMMIT;
-- ✅ 3. Optimistic concurrency: detect the conflict on write and retry.
UPDATE accounts SET balance = ?, version = version + 1
WHERE id = 42 AND version = :version_i_read;
-- 0 rows affected => someone else won; re-read and retry.Optimistic or pessimistic?
Pessimistic (FOR UPDATE) | Optimistic (version column) | |
|---|---|---|
| Assumes | Conflicts are common | Conflicts are rare |
| Cost when uncontended | Lock acquisition overhead | Effectively zero |
| Cost when contended | Waiting, possible deadlock | Wasted work plus retries |
| Holds locks across user think-time | Dangerous - never do it | Safe by design |
| Good for | Hot rows: inventory, seat booking | Low-contention edits: profiles, documents |
MVCC in one paragraph
Rather than locking readers out, MVCC keeps multiple versions of each row tagged with transaction IDs. A reader sees the snapshot as of its start, so readers never block writers and writers never block readers. The costs are storage for old versions and a background process - vacuum in Postgres, purge in InnoDB - to reclaim them. A long-running transaction pins old versions and prevents cleanup, which is precisely why an idle-in-transaction session can bloat a database into an outage.
Say these points
- Read Committed is the common default and permits non-repeatable reads and phantoms.
- Write skew survives everything below Serializable - know the on-call doctors example.
- Prefer atomic in-database updates over read-modify-write.
- Optimistic for low contention, pessimistic for hot rows; never hold a lock across user think-time.
- MVCC means readers and writers do not block each other, at the cost of vacuum pressure.
Avoid these mistakes
- Assuming the default isolation level is Serializable.
- Read-modify-write in application code without version checks or locks.
- Holding a `FOR UPDATE` lock while waiting for a user to click something.
- Ignoring that Serializable can abort transactions, so callers must retry.
- Long-running transactions that block vacuum and bloat the database.
Expect these follow-ups
- Show me a write-skew bug in a booking system and three ways to fix it.
- Why can Serializable in Postgres abort a transaction that took no locks?
- How do deadlocks arise, and how do you make them rare?
Showing 2 of 2 questions for acid-transactions-and-isolation-levels.
Check your understanding
4 questions · no sign-up, nothing stored
Hands-on challenge
Build it - this is what you talk about in a deep-dive round.
Break and then fix a concurrency bug
Reproduce a real anomaly against a real database, then fix it three different ways and compare.
Requirements
- Create an `accounts` table and reproduce a lost update with two concurrent sessions at Read Committed.
- Fix it with an atomic UPDATE, then with SELECT FOR UPDATE, then with an optimistic version column.
- Benchmark all three under 50 concurrent workers on one hot row and record throughput and error rates.
- Reproduce write skew with a two-row invariant, and demonstrate that Repeatable Read does not stop it.
- Show Serializable preventing it, and handle the resulting serialization failure with a retry.
Stretch goals
- Induce a deadlock deliberately and capture the database's deadlock report.
- Measure how much table bloat a 10-minute idle-in-transaction session causes.