Consistency and concurrency

Once data lives on many machines, you cannot have everything at once. This lesson covers the trade-offs distribution forces, the machinery that keeps a database correct, and how modern distributed SQL squares the circle.

The CAP theorem

A distributed system juggles three properties:

Consistency - every read sees the latest write.
Availability - every request gets a (non-error) response.
Partition tolerance - the system keeps working when the network between nodes breaks.

CAP says that during a network partition you can keep only two - and since partitions are a fact of life in distributed systems, P is non-negotiable. So the real choice, when a partition happens, is C or A:

CP systems refuse some requests to avoid returning stale data (favour consistency).
AP systems keep answering, accepting that some replicas are briefly out of date (favour availability) - they become eventually consistent, converging once the partition heals.

PACELC: the rest of the time

CAP only describes behaviour during a partition. PACELC completes it: if there is a Partition, trade A vs C; Else (normal operation), trade Latency vs Consistency. Even with a healthy network, stronger consistency costs round trips. Many systems let you tune this per query - e.g. a quorum read for correctness, or a fast local read that may be slightly stale.

Staying correct under concurrency

Underneath, the mechanisms from Stage 3 are what make this work:

MVCC keeps multiple versions of each row so readers see a consistent snapshot without blocking writers.
Locks serialize conflicting writes where needed.
The write-ahead log (WAL) records every change before applying it. The WAL is the unsung hero: it provides durability (replay it to recover after a crash), and it is also the stream that feeds replicas - replication is largely "ship the WAL to another node and replay it."

NewSQL: SQL and ACID at scale

For years the trade-off was framed as "scale out, but give up SQL and ACID." NewSQL (distributed SQL) broke that. Systems like CockroachDB, TiDB, and Google Spanner shard data across nodes yet present one logical SQL database with full ACID transactions. They achieve it with consensus protocols (Raft or Paxos) to agree on each write across replicas, and - in Spanner's case - synchronized clocks (TrueTime) to order transactions globally. The practical result for 2026: you can have horizontal scale and SQL and strong consistency, where you once had to pick.

Quick quiz