A distributed DBMS stores and coordinates data across failure boundaries. This lesson is for learners who know local transactions and need to reason about partitioning, replication, consistency, and multi-node commits without assuming the network is reliable or instantaneous.
You will be able to choose a partition key, explain replica lag and quorum trade-offs, apply CAP only during a network partition, trace an in-doubt two-phase commit, and decide when a saga or transactional outbox is a better fit than a global transaction.
A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network. Users interact with it as if it were a single database, but data is physically stored across multiple sites (nodes).
Key advantages:
Fragmentation divides a relation into smaller pieces stored at different sites:
| Type | Description | Example |
|---|---|---|
| Horizontal Fragmentation | Rows are divided among sites (like partitioning) | Customers in US stored at US site; EU customers at EU site |
| Vertical Fragmentation | Columns are divided among sites | Employee name/dept at HQ; salary/benefits at HR site |
| Mixed Fragmentation | Combination of horizontal and vertical | US customers' names at US site; US customers' orders at order site |
Replication stores copies of data at multiple sites to improve availability and read performance.
| Strategy | Description | Trade-off |
|---|---|---|
| Full Replication | Every site has a complete copy of the database | Best read performance; expensive writes (update all copies) |
| No Replication | Each fragment stored at exactly one site | No redundancy; site failure = data unavailable |
| Partial Replication | Some fragments replicated, others not | Balance between availability and update cost |
| Synchronous Replication | All replicas updated before transaction commits | Strong consistency; higher latency |
| Asynchronous Replication | Primary commits first; replicas updated later | Lower latency; possible stale reads |
The Two-Phase Commit protocol ensures atomicity of distributed transactions - either all sites commit or all abort.
Phase 1 - Prepare (Voting):
Phase 2 - Commit/Abort:
Problem: 2PC can block after participants vote yes because they cannot independently decide to abort. Three-phase commit changes the protocol under stronger timing assumptions, but it is not a universal cure for asynchronous networks or partitions; practical systems rely on durable coordinator state, consensus-backed decisions, timeouts for detection, and operational recovery.
CAP describes what happens while a network partition prevents some nodes from communicating. A system cannot simultaneously guarantee linearizable consistency and a successful response from every non-failing node for every request during that partition.
CAP is not a permanent instruction to choose any two labels. When communication is healthy, a design can provide both consistency and availability. During a partition, an operation must either reject or delay some requests to preserve the chosen consistency guarantee, or accept operations that may expose stale or conflicting state to preserve availability.
| Property | Description |
|---|---|
| Consistency (C) | Every read receives the most recent write or an error. All nodes see the same data at the same time. |
| Availability (A) | Every request receives a response (not necessarily the latest data). The system is always operational. |
| Partition Tolerance (P) | The system continues to operate even when network partitions (communication failures between nodes) occur. |
ACID describes transaction properties, while eventual consistency describes how replicas may converge over time. They are not strict opposites: a distributed database can provide ACID transactions within a scope and still replicate some results asynchronously. BASE is a loose design slogan meaning basically available, soft state, and eventual consistency; it does not define one testable isolation or conflict-resolution model.
| Question | Transactional Focus | Replication Focus |
|---|---|---|
| What is protected? | Atomic changes and declared invariants within transaction scope | How copies expose and reconcile versions |
| What must be specified? | Isolation level, constraints, durability boundary | Read guarantee, conflict rule, convergence condition |
| Can both coexist? | Yes; transactions can commit locally or through coordination | Yes; committed state can be copied asynchronously |
| Design evidence | Invariant and transaction anomaly tests | Lag, conflict, session, and partition tests |
Horizontal partitioning, often called sharding, assigns rows to nodes by a partition key. Hash partitioning spreads well-distributed keys across buckets and supports direct routing when the key is known. Range partitioning keeps neighboring values together and supports range scans, but a growing range such as the newest timestamp can concentrate writes on one node. Directory-based routing adds flexibility at the cost of another metadata dependency.
A useful partition key distributes storage and request rate, appears in common access paths, and gives the system a manageable unit for movement. Tenant ID may make tenant-local queries efficient but creates a large-tenant hotspot. A random ID spreads writes but makes queries by customer or region scatter to every shard. Model key frequency and query routing before data volume makes repartitioning expensive.
Adding a node does not automatically balance existing data. Rebalancing copies partitions while writes continue, catches up changes, changes routing ownership, and eventually removes the old copy. Capacity planning must include transfer bandwidth, temporary duplicate storage, cache warming, and the failure case where migration pauses halfway through.
In leader-based replication, one leader orders writes and followers apply the resulting log. Synchronous acknowledgement from one or more followers reduces the window of acknowledged data loss but adds network latency and can reduce write availability. Asynchronous followers keep the write path fast but can lag, serve stale reads, or lose recently acknowledged work if failover chooses an outdated replica.
Multi-leader and leaderless designs accept writes in more locations, which can improve locality or partition availability but introduces conflict handling and more complex ordering. Last-write-wins is simple yet can silently discard a valid update when clocks differ. Domain-aware merges, version vectors, conditional writes, or conflict records preserve more intent, but applications must define what convergence means for each data type.
In a simplified replicated system with N copies, a write quorum W and read quorum R overlap when R + W is greater than N. That overlap can let a read encounter at least one copy that accepted the latest write, but freshness still depends on version comparison, failure handling, membership, sloppy quorums, and the exact protocol. The arithmetic alone is not a complete consistency proof.
Replica lag should be measured in both time and log position. A read-after-write requirement can route the user to the leader, wait until a follower reaches the write position, or carry a session token that prevents reading an older version. Each method trades latency, locality, and availability. State the consistency needed by the operation rather than applying the strongest setting to every endpoint.
| Operation Need | Possible Technique | Trade-off |
|---|---|---|
| User sees own update | Leader read or session token | Less follower flexibility |
| Monotonic session reads | Pin session to sufficiently advanced replica | Routing state |
| Low-latency catalog browse | Nearby asynchronous replica | Possibly stale results |
| Inventory reservation | Conditional write on authoritative owner | May reject during failure |
Linearizability makes each completed operation appear at one instant between invocation and response and respects real-time order. Serializability gives transactions an outcome equivalent to some serial order but does not by itself require that order to match wall-clock completion. A system can therefore be serializable without being strictly serializable. Eventual consistency only promises convergence after updates stop and communication succeeds; it does not define what intermediate reads may observe.
Session guarantees are often the practical middle ground: read-your-writes, monotonic reads, monotonic writes, and writes-follow-reads. Choose the model from an invariant. A social counter may tolerate delayed convergence; a uniqueness decision, ownership transfer, or account balance may require conditional operations or serializable coordination. Calling every stale read a CAP trade-off hides these application-specific requirements.
During 2PC prepare, a participant verifies that it can commit, durably records that promise, and usually retains locks or other resources. Once it votes yes, it cannot safely abort by itself because the coordinator may already have decided commit. If the decision is temporarily unreachable, the participant is in doubt and other transactions can remain blocked behind its resources.
Timeout is evidence of delay, not proof that a participant or coordinator failed. A retry must use the same transaction identity so messages are idempotent and the durable decision can be rediscovered. Operators need visibility into prepared transactions, coordinator records, participant state, and a documented resolution procedure. Guessing a decision independently can violate atomicity.
A saga breaks a long business operation into local transactions connected by messages. If a later step fails, compensating actions attempt to reverse earlier business effects. Compensation is not database rollback: a refund differs from erasing a charge, a released seat may already have been observed, and an email cannot be unsent. Design explicit states, idempotent handlers, retries, deadlines, and manual repair for compensation that also fails.
The transactional outbox solves a narrower dual-write problem. The service changes its local business data and inserts an outbox record in the same local transaction. A relay later publishes the record and marks progress. Publication is commonly at least once, so consumers deduplicate by event ID or make processing idempotent. The outbox does not create a global transaction; it ensures that committed local state has a durable publication intent.
| Need | Better Starting Point | Reason |
|---|---|---|
| Atomic change inside one database | Local transaction | Smallest failure surface |
| Immediate atomic decision across compatible stores | 2PC when supported and justified | Single commit decision, blocking risk |
| Long workflow across independent services | Saga | Local commits with business compensation |
| Publish after local commit | Transactional outbox | Avoids database-plus-broker dual write |
Test the system at the boundaries its design assumes: isolate a leader from followers, delay replication, stop a coordinator after prepare, lose a node during rebalancing, and replay the same message. Observe request outcomes, durable decisions, lag, lock duration, duplicate effects, and recovery time. A healthy-node benchmark cannot validate behavior during the failures that motivated distribution.
Use globally unique operation IDs and preserve causal evidence in logs and traces. Wall-clock timestamps alone cannot establish event order when clocks skew; consensus log indexes, database LSNs, monotonic sequence numbers, or causal metadata provide stronger ordering within their scope. Alerts should describe learner-relevant and operator-relevant consequences such as stale-read age, unavailable shard ranges, unresolved prepared transactions, and failed compensations.
1. Coordinator sends PREPARE to inventory and payment.
2. Both participants durably record YES and retain resources.
3. Coordinator durably records COMMIT.
4. Network partition hides the decision from inventory.
Inventory is in doubt. It must discover the durable decision;
timeout alone does not authorize an independent abort.BEGIN;
UPDATE orders
SET status = 'confirmed'
WHERE order_id = 9001 AND status = 'pending';
INSERT INTO outbox(event_id, aggregate_id, event_type, payload)
VALUES ('evt-9001-confirmed', 9001, 'OrderConfirmed', '{\"orderId\":9001}');
COMMIT;
-- A relay publishes the committed outbox row.
-- Consumers deduplicate event_id because delivery may repeat.The write may have reached the leader or one replica while another replica has not applied it yet.
A shard key determines how records and traffic are distributed. A monotonically increasing timestamp or a low-cardinality tenant field can send most new writes to one shard while others remain idle.
A distributed transaction coordinates participants across network boundaries, where messages can be delayed, nodes can fail independently, and locks may be held while agreement is reached. Protocols such as two-phase commit add round trips and failure states.
Explore 500+ free tutorials across 20+ languages and frameworks.