What it is
Concurrent transactions touch the same data, and isolation is the promise about what one transaction can see of the others. Stronger isolation hides more anomalies and costs more coordination. MVCC (multi-version concurrency control) is the standard implementation: instead of overwriting a row, keep multiple timestamped versions, so readers read an old consistent version and never block writers. In a distributed database, “which version is current” depends on clocks, which is where hybrid logical clocks and TrueTime enter.
Important
Key takeaway: Isolation is a ladder, not a switch, and the default level in most databases is weaker than serializable. MVCC lets readers and writers run without blocking each other; in a distributed system it also needs comparable timestamps, which is a clock problem solved by bounded-uncertainty clocks.
When you care
“What isolation level, and what anomaly could happen?” is a standard database follow-up, and “how do you get a consistent snapshot across shards?” is the distributed version. Naming write skew is the senior signal.
Note
Interview signal: Most candidates know dirty reads and non-repeatable reads. Describing write skew (two transactions each read a condition, each write something that invalidates the other’s read) and saying snapshot isolation does not prevent it separates experience from textbook recall.
The isolation ladder
| Level | Prevents | Still allows | Typical default |
|---|---|---|---|
| Read uncommitted | Nothing | Dirty reads (seeing uncommitted data) | Rare |
| Read committed | Dirty reads | Non-repeatable reads (a row changes between two reads in one transaction) | PostgreSQL, Oracle |
| Repeatable read / snapshot isolation | Dirty and non-repeatable reads; each transaction sees a fixed snapshot | Write skew and some phantoms | MySQL (InnoDB), and PostgreSQL’s “repeatable read” |
| Serializable | All of the above: the result equals some one-at-a-time order | Nothing (at the cost of aborts or blocking) | Opt-in |
Terminology varies by database (PostgreSQL’s repeatable read is snapshot isolation; MySQL’s differs in details), so say the guarantee, not just the name.
Worked example: write skew. A hospital requires at least one doctor on call. Alice and Bob are both on call. Each runs: read the count of doctors on call (2), see it is at least 2, so mark myself off call. Under snapshot isolation both transactions see the same snapshot (count = 2), neither writes the other’s row, so both commit and zero doctors are on call. Serializable isolation (or an explicit lock on the rows read, or a constraint) prevents it. The pattern: a transaction decides based on data that another concurrent transaction changes without writing the same rows.
MVCC
Each write creates a new version of the row stamped with the transaction’s timestamp, instead of updating in place. A reader reads as of its timestamp: for each key, the newest version at or before it.
| Property | Effect |
|---|---|
| Readers never block writers, and writers never block readers | Long reports do not stall writes |
| A consistent snapshot is free | A transaction sees one point in time across all its reads |
| Writers conflict only on real write-write overlap | Concurrent writes to the same row: one waits or aborts |
| Old versions accumulate | A background process (vacuum, garbage collection) removes versions no transaction can still see |
Why MVCC and not locks, in a distributed database? On a single machine, a read lock is cheap. Across machines, every lock is a network conversation and every reader-writer conflict a cross-node stall. MVCC turns “a consistent snapshot of 100 ranges” into “read each range at timestamp T”, with no coordination between readers and writers.
Transactions across shards
A transaction that writes to several shards needs one atomic decision. A production pattern layers two-phase commit on top of consensus:
- The first write creates a provisional version (a write intent) and a transaction record
PENDINGon the same shard. - Writes to other shards create intents that point back at the transaction record. Each intent is itself consensus-committed.
- Commit is one write: flip the transaction record to
COMMITTED. At that instant the transaction is durably and atomically decided. - The client is acknowledged; intents are rewritten as ordinary versions asynchronously. A reader that meets an intent checks the record and resolves it on the spot.
Because the protocol state (intents, votes, the decision) all lives in replicated shards, no single machine’s death leaves the system unsure. The record is the transaction.
Clocks: making timestamps comparable
“The newest version at or before T” only means something if timestamps are comparable across machines, and perfectly synchronized clocks do not exist (NTP keeps clocks within roughly milliseconds, not microseconds).
| Approach | Idea | Cost |
|---|---|---|
| TrueTime (Spanner) | Hardware (GPS and atomic clocks) bounds the clock uncertainty to a few milliseconds; the system waits out the uncertainty at commit so timestamp order matches real-time order | Special hardware; every commit pays the wait |
| Hybrid logical clocks (HLC) | A timestamp combining physical time and a logical counter, plus an uncertainty window; a read that finds a version inside its uncertainty window restarts at a higher timestamp | No special hardware; pays only on real close calls |
| A timestamp oracle | One service hands out increasing timestamps | A single bottleneck, simple |
Same invariant, different payment: TrueTime pays at every commit, HLC pays only when two events are close together.
Follower reads. The leader of a range periodically publishes a closed timestamp: a promise that no new writes will land at or below T. Any follower can then serve reads at or below T locally. This makes a remote region’s read fast and slightly stale by contract, which is the disciplined version of replication lag.
Warning
Production reality: Under serializable isolation, conflicts surface as client-visible retries (aborts). Applications must catch the retryable error and rerun the transaction, which must therefore be idempotent. Higher isolation is not free; it moves cost from anomalies to retries.
When to pick what
| Need | Choose | Why |
|---|---|---|
| Default OLTP, tolerate some anomalies | Read committed or snapshot isolation | Low overhead |
| An invariant across rows (at least one on call, no overdraft) | Serializable, or lock the rows read, or a constraint | Prevents write skew |
| Long reports without blocking writers | MVCC snapshot reads | Readers never block |
| A consistent snapshot across shards | Timestamped MVCC reads | Read each shard at T |
| Cross-shard atomic writes | 2PC layered on consensus | No coordinator whose death strands anyone |
| A remote region’s reads, staleness acceptable | Follower reads at a closed timestamp | Local and bounded staleness |
Caution
Common mistake: Assuming “repeatable read” or “snapshot” means serializable. They prevent the familiar anomalies and still allow write skew. If a correctness invariant spans several rows, name the level you need and how you enforce it.
Related
- Walkthrough: Designing a Distributed Database — MVCC, write intents, and follower reads in one design.
- Walkthrough: Designing a Flash Deal System — an invariant across rows (no oversell) under extreme write contention.
- Reference: Distributed Transaction Patterns — 2PC, sagas, and when to give up on cross-service atomicity.