Why Consistency and Availability Cannot Always Coexist

The Problem With "Just Make It Consistent"

You're designing a flash sale system. Inventory is limited — 500 units. Your product manager says: "We can't oversell. Make sure we never sell more than we have." Simple enough. You implement a check-then-decrement:

BEGIN;
SELECT stock FROM inventory WHERE product_id = ? FOR UPDATE;
-- If stock > 0, decrement and create order
UPDATE inventory SET stock = stock - 1 WHERE product_id = ?;
COMMIT;

This works correctly with a single database and serializable transactions. Now add a read replica for performance. The SELECT may be routed to the replica, which may be 50-200ms behind the primary. A user reads stock = 5 on the replica and proceeds to order. Between that read and the write, nine other users did the same thing. You've oversold.

This is not a bug in your application logic. It's a consequence of choosing availability (route reads to replicas for throughput) over consistency (ensure every read sees the most recent write).

The Fundamental Tradeoff

Consistency — in the distributed systems sense — means all nodes see the same data at the same time. A read on any node returns the value of the most recent write, regardless of which node that write went to.

Availability means the system responds to every request, even under failure conditions or network partitions.

In a single-node system, you can have both. In a distributed system — a database with replicas, a partitioned cluster, any system where data lives on more than one machine — you cannot unconditionally guarantee both. This is the substance of the CAP Theorem, which we'll treat as established context here.

The practical consequence: choosing to use a read replica, a distributed cache, or a geographically distributed database means accepting that some reads may not reflect the latest writes. This is a deliberate tradeoff, not a bug to be fixed.

The Consistency Models in Practice

Strong consistency (linearizability): Every read returns the result of the most recent write. Achieved with single-node writes, synchronous replication, or consensus protocols (Raft, Paxos). The cost: higher write latency (you wait for quorum acknowledgment) and reduced availability under partition (you cannot accept writes if you can't confirm them with enough nodes).

CockroachDB and Google Spanner provide strong consistency in distributed databases using Raft and TrueTime respectively. They are appropriate when correctness is non-negotiable and you're willing to accept the associated latency and complexity.

Eventual consistency: All nodes will eventually converge to the same value. Reads may return stale data during the convergence window. The cost: your application must handle the possibility that two reads of the same data in quick succession may return different values.

Most replicated databases (MySQL with async replication, PostgreSQL streaming replication in default configuration, DynamoDB with eventual consistency reads) are eventually consistent. This is the right choice for the majority of use cases — user profile reads, product catalog, content feeds — where a small window of staleness is acceptable.

Read-your-writes consistency: After a write, the writer is guaranteed to see that write on subsequent reads. Other readers may not. This is a weaker but often sufficient guarantee — most user-facing inconsistency complaints boil down to "I just saved something and it disappeared," which this consistency level addresses.

Achieved in practice by routing a user's reads to the primary immediately after a write (sticky session to primary, with a timeout), or using a monotonic read token that the database honors.

The Inventory Problem Solved

Back to the flash sale. The options:

Keep the check-and-decrement on the primary: Strong consistency, but you've given up the read scaling benefit of replicas for this path. Acceptable if inventory reads are a small fraction of total reads.

Optimistic locking: Include a version number in the inventory row. The decrement fails if the version has changed since the read. Retry. Under high contention, retry rates spike.

Pessimistic locking (SELECT FOR UPDATE): Serialize access to the row. Works for moderate contention; at very high concurrency, lock wait times become the bottleneck.

Redis atomic operations: Move inventory counters to Redis. Use DECRBY which is atomic and returns the new value. Flush to the database asynchronously. The Redis node is the single source of truth for the live inventory number — consistency is achieved by having one authoritative node.

The right answer depends on your scale, your contention level, and your tolerance for complexity. There is no universally correct solution — only tradeoffs with different properties.

The Practical Takeaway

For every piece of data in your system that requires correctness guarantees, explicitly decide: what consistency level is required, and what are you willing to sacrifice to achieve it? "We need it to be correct" is not a consistency level. "We need read-your-writes consistency and can tolerate 200ms of staleness for other readers" is a consistency level — and it's actionable. Write it down. It will determine which database features you use and how you structure your reads.