One transaction, many machines

When one atomic transaction must span several nodes, the classic protocol is Two-Phase Commit (2PC) — it runs the transaction in a prepare phase and a commit phase, coordinated by one node.

1. Prepare phase. A designated coordinator asks every participant node whether it can carry out the transaction — can it acquire the needed locks, does it have capacity? Each node does everything needed for success except the final commit, and replies ready.
2. Commit phase. Once all nodes confirm readiness, the coordinator tells them to commit. Each commits and sends an acknowledgment; when all acks arrive, the coordinator completes the transaction. Any failure in any phase → the whole thing rolls back and locks release.

Write-ahead log (WAL)

Every participant records its activity to a write-ahead log in stable storage before committing. If a node crashes mid-transaction, on recovery it consults the log to learn whether its last operation succeeded, partially ran, or never ran. Logs are append-only (all do/undo/redo entries in chronological order), so no record is lost across failures; checkpoints mark where updates are permanently written and the log is cleared.

Saga runs a distributed transaction as a sequence of local transactions, each with a compensating (undo) transaction that's idempotent. Either all steps succeed in order, or on failure the compensating transactions roll back from the point it broke. The bookstore purchase flow:

1. Create order — compensate: cancel order.
2. Process payment (from card info) — compensate: refund.
3. Update inventory — compensate: restock.
4. Kick off delivery.

Each step is a separate transaction with its own compensating counterpart. A coordinator logs every step; on failure it triggers the compensating transactions — and because a compensating call might itself fail and be retried, those transactions must be idempotent. Unlike 2PC (a database protocol plus custom code), Saga is a pattern the developers design and implement — you own the rollbacks and compensations. Frameworks like Apache Camel help.

Two ways to implement Saga

Once you shard, the application can no longer pretend there's one database — the code must know where data lives and route each query to the right shard. As nodes are added/removed and data is resharded and moved, that routing logic must keep up. Sharding often means real schema changes and refactoring, and demands you design around your frequent queries.

The banking cross-shard query problem

A bank's MoneyTransfer table has ~45M entries/day across 200 shards. Query: "fetch ≥9000 transactions with transfer amount below $50 for a given day." Each shard is independent and knows nothing of the others. Two patterns handle this:

Dropbox stores petabytes of metadata behind a system called Edgestore — an abstraction over thousands of MySQL instances across data centers, holding trillions of entries, serving millions of QPS with strongly consistent low-latency reads/writes. Edgestore hides the physical sharding from the app.

Their tweaks to 2PC

• External transaction log. The 2PC coordinator writes all protocol events to a separate log. Even if every shard commits and acks, the transaction isn't considered committed until the log records it. So any concurrent read/write only checks the log to learn transaction state, instead of contacting each shard — cutting lock contention and distributed-deadlock risk.
• Copy-on-write at commit staging. In the staging phase (participants ready but not committed), changes are written via copy-on-write to a dedicated MySQL table, not the primary tables; only at final commit does the data move to the primary tables. This "shadowing" handles concurrent writes efficiently — like a snapshot in snapshot isolation.

They also co-locate frequently accessed data so only 5–10% of transactions span multiple shards. Edgestore itself acts as a shard manager, owning the operational overhead — resharding under load, managing slow queries, and more.

Vitess is the open-source shard manager (built by YouTube, run by Square, Slack, Pinterest, Flipkart, Airbnb, GitHub, Roblox, Twitter, and more). It sits between the app and MySQL shards, adding built-in sharding with minimal app-side logic, plus connection pooling, automatic failover/backup, query rewriting, and live resharding.

Slack

MySQL is Slack's primary store; Vitess serves 99% of query load — 2.3M QPS peak (2M reads, 300K writes, ~2ms latency). They started on a LAMP stack with three clusters — Shards (customer data partitioned by workspace id, so all of a workspace's data lives together), a Metadata cluster (maps workspace id → shard), and a Kitchen sink cluster (non-workspace data). The problem: some workspaces outgrew a single shard, creating hotspots — a few big customers overloaded their shards while other hardware sat idle, and a single shard failing meant an outage for that customer. The fix: move all sharding logic out of the monolith into Vitess (now split by keyspace, not workspace id), making the logic reusable across services.

Square (Cash App)

The Cash App grew from a hack-week single MySQL instance to half a million lines of code, forcing a shard — also on Vitess. Because it processes money, ACID and strong consistency were vital, so they had to avoid cross-shard money transactions. Their solution: entity groups — the customer is the main entity group, and Vitess is configured so all members of an entity group stay on the same shard. Transactions inside one entity group land on one shard and are ACID-compliant; those spanning entity groups aren't. To enable this, they remodeled from a normalized schema to a partitionable one.

Square's entity-group model has a root entity (customer) and child entities that carry the customer id to stay associated — e.g. a customer row (root) and its credit-card rows (children, each with a CustomerId column). A query with the customer id routes cleanly:

SELECT * FROM Customer WHERE CustomerId = 752
                

But what about a query that only has a credit-card id?

SELECT * FROM CreditCard WHERE CardId = 24
                

Vitess can't know which shard holds it, and scanning all shards is unacceptable. Adding CustomerId to every query works for some but breaks when you have only card ids and need to find their owners — and ~25% of Cash App's DB traffic had no customer id, too much to fix by hand.

The through-line across Dropbox, Slack, and Square: understanding your application's queries is the prerequisite to sharding well.

Distributed SQL is a single relational database whose data is replicated across nodes worldwide, using consensus algorithms (Raft, Paxos) to agree on values and offering an SQL-compatible distributed query layer. Examples: Google Spanner, CockroachDB, YugabyteDB, TiDB, Amazon Aurora. Architecturally similar to a Vitess setup — the key difference is these are built from the ground up to scale rather than layered onto an existing engine (Vitess for MySQL, Citus for Postgres).

Google Spanner was the first public distributed SQL database and popularized the term. Since most of these are proprietary, a vendor lock-in factor exists — and they're not like-for-like swappable (each has design intricacies you hit later), so the advice is to run a POC and a small production feature before committing fully.

NoSQL databases can do ACID and strong consistency in distributed transactions — but the same way relational shards do: with locks, which kills on-the-fly horizontal scaling.

The lessons cite many external links. Below are the ones that resolve live, grouped by topic. The author's own course cross-links and one untested framework link (AxonIQ) are omitted.

Saga frameworks

Sharding at scale — real engineering write-ups

ACID in NoSQL