Consistency in System Design: A Simple Guide with Real Examples

The best way to understand consistency in system  design is through a real outage, so I'll start with one.

On October 21, 2018, engineers at GitHub were doing routine work: replacing some faulty network equipment. While they did it, the connection between two of GitHub's sites dropped for 43 seconds. That's it. Less than a minute.

But in those 43 seconds, GitHub's automatic failover system decided the main data center on the US East Coast was gone. It made database servers on the West Coast the new "main" copy. When the network came back, there was a problem. Both sides had saved some data the other side didn't have. On one busy database cluster alone, 954 writes existed only on the East Coast.

By the time engineers understood what had happened, the West Coast databases had been taking new writes from users for more than 30 minutes. GitHub now had a hard choice:

• Option 1: Switch back to the East Coast right away. The site would be fast again, but more than 30 minutes of new writes made on the West Coast would be thrown away.
• Option 2: Keep the West Coast as the main copy, protect all of that data and rebuild the East Coast databases from backups. The catch: GitHub's East Coast app servers now had to send most database calls across the country and their apps weren't built for that much delay. The site would be slow and partly broken for hours.

They chose option 2. According to GitHub's own post-incident report, the site ran in a degraded state for 24 hours and 11 minutes. During that time, some users saw out-of-date information because the database copies they were reading from were hours behind. and when GitHub started processing the backlog of queued work, about 200,000 webhook messages turned out to have waited longer than an internal time limit and were dropped.

That one incident shows you what consistency is really about:

1. Copies of data can get out of sync. Even at a company like GitHub. Even for a few seconds.
2. You must decide ahead of time what to do when that happens. Keep serving and risk showing wrong data? Or stop and protect correctness?
3. The pain hides in small details. Old copies, retries, queues and code that assumed the data was always right.

In this guide, I'll explain consistency in system design in plain English, step by step. We'll start with the basics, then go into the different types of consistency, the CAP theorem, how real databases handle it, real incidents from companies like Amazon, Facebook, Google and Starbucks, the bugs developers write by mistake and how to choose the right level for your own system. Every code example in this article was actually run and the outputs you see are real.

Consistency in 60 seconds

• What it is: a promise about what data you'll see when you read, after someone has written something.
• Why it matters: big systems keep many copies of the same data. Copies can be out of date for a while.
• Strong consistency: you always see the latest data, as if there were only one copy. Safer, but slower.
• Eventual consistency: you might see old data for a short time, but all copies catch up in the end. Faster and more available.
• The trade-off: more consistency costs more time (latency) and during network problems, it can cost availability too.
• Real systems mix both: strong for money, bookings and stock. Eventual for likes, views and search results.

What Is Consistency in System  Design?

Consistency in system design means the rules for what data you get when you read it. In a strongly consistent system, every read gives you the most recent saved value. In a weakly consistent system, a read might give you an older value for a while.

An everyday example helps.

Imagine a family that keeps a shared shopping list in a notes app like Google Keep or Apple Notes. Mom adds "milk" to the list on her phone. A few seconds later, Dad opens the same list in the supermarket.

• If Dad always sees "milk," that's strong consistency.
• If Dad sometimes doesn't see "milk" yet, but it shows up a few seconds later, that's eventual consistency.

For a shopping list, eventual is fine. Worst case, Dad gets a reminder call. But now imagine the "list" is your bank balance or the last seat on a flight. Suddenly, seeing old data isn't fine at all.

That's the whole topic in one sentence: how fresh does the data need to be and what are you willing to pay to get it?

One word, three meanings

Something confuses almost everyone at first: the word "consistency" is used for three different things in tech. If you mix them up, a lot of articles stop making sense.

There's also a fourth, everyday meaning: are two systems in sync? For example, "Is the cache consistent with the database?" or "Does the search index match the product table?" That's really the same idea applied to copies of data.

When someone in a system design interview says "this needs strong consistency," they almost always mean the second one: users must never see stale data for this feature.

Simple glossary

You'll see these words a lot. Here they are in plain English.

Why Is Consistency Hard?

If all your data lives on one computer, consistency is easy. You save a value. You read it back. You get what you saved.

The trouble starts when you keep more than one copy. and almost every real system does, for three good reasons:

• Safety: if one server dies, another one still has the data.
• Speed for reads: many copies can handle many more readers.
• Closeness to users: a copy in Mumbai serves Indian users faster than a copy in Virginia.

But when you write new data, it has to travel to every copy. Travel takes time. Networks can be slow, drop messages or break completely.

  You update a product price to 499
            │
            ▼
   ┌─────────────────┐   copy sent      ┌─────────────────┐
   │     Primary     │ ───────────────► │    Replica A    │  price = 499 ✓
   │   price = 499   │                  └─────────────────┘
   └────────┬────────┘
            │  copy delayed (slow network, busy server)
            ▼
   ┌─────────────────┐
   │    Replica B    │  price = 449   ✗ still the old price
   └─────────────────┘

  If a customer's request goes to Replica B right now, they see 449.

That delay is called replication lag. On a normal day, it's often tiny. But under heavy load, during a failover or after an outage, it can grow to minutes or even hours. That's exactly what happened to GitHub's read copies in 2018.

Two more things make this tricky:

• You can't tell "slow" from "dead." If a server doesn't reply, maybe it crashed. Maybe the network is just slow. Any timeout you choose is a guess.
• Clocks don't agree. Every computer's clock drifts a little. So if two servers stamp two writes with times, you can't always trust which one really happened first.

Every type of consistency is simply a different answer to this question: with all these problems, what can we safely promise the person reading the data?

Types of Consistency: From Strong to Eventual

Think of consistency as a slider. On one end, you get the strongest promises: easy to reason about, but slower. On the other end, you get the weakest promises: fast and always up, but your code has to deal with old data.

STRONGER ◄──────────────────────────────────────────────────────► WEAKER
safer, simpler to code against                      faster, more available

 Strong           Sequential        Causal          Session           Eventual
 (linearizable)                                     guarantees
     │                 │               │                │                 │
 always the       everyone sees    causes always    each user's       all copies
 latest value,    the same order   show up before   own view makes    agree in the
 like one copy                     their effects    sense             end

Each one is easier to understand with an example.

time ──────────────────────────────────────────────────────────►

Client A:  |──── buys last seat (seats_left = 0) ────|
                                                      ↑ purchase finished
Client B:                                                |── read ──| → must see 0
Client C:        |──── read ────|  → may see 1 or 0
                  (it happened during the purchase, so either is OK)

All consistency types at a glance

Strong vs Eventual Consistency: What's the Difference?

This is one of the most searched questions on this topic, so here's a direct comparison.

Strong consistency guarantees that every read returns the latest saved value. Eventual consistency allows reads to return older values for a while, but all copies catch up over time.

Neither one is "better." The right answer depends on what goes wrong if someone sees old data.

The CAP Theorem in Simple Words

The CAP theorem says that when the network between your servers breaks, you must choose: either keep data correct (consistency) or keep answering every request (availability). You can't fully have both during that break.

Eric Brewer suggested this idea in 2000. Seth Gilbert and Nancy Lynch proved it formally in 2002.

You've probably heard it as "pick 2 out of 3: Consistency, Availability, Partition tolerance." That version is misleading, for three reasons:

1. Network breaks aren't optional. They happen. You don't get to "skip" partition tolerance. You only get to choose how you behave when a break happens. GitHub's 43-second outage was exactly this kind of break.
2. CAP uses very strict meanings. "Consistency" means strong consistency (linearizability). "Availability" means every working server must answer. Most real systems sit somewhere in between and make different choices for different operations.
3. CAP says nothing about normal days. and normal days are 99.9% of the time.

                    The network between servers breaks
                 ┌──────────────┴──────────────┐
                 ▼                             ▼
     ┌───────────────────────┐     ┌────────────────────────┐
     │  Choose CONSISTENCY   │     │  Choose AVAILABILITY   │
     ├───────────────────────┤     ├────────────────────────┤
     │ Servers that can't    │     │ All servers keep       │
     │ reach the majority    │     │ answering              │
     │ stop answering        │     │                        │
     │                       │     │ Some users may see old │
     │ Users see errors, but │     │ data and conflicting  │
     │ never wrong data      │     │ changes need fixing    │
     │                       │     │ later                  │
     │ e.g. etcd, ZooKeeper, │     │ e.g. Dynamo-style      │
     │ Spanner               │     │ systems, DNS           │
     └───────────────────────┘     └────────────────────────┘

GitHub's story fits right into this picture. When the network broke, their failover tool picked a new main database on the side that had the majority. That part worked as designed. The problem was that the old main database had quietly accepted a few seconds of writes that never crossed over. After the network came back, there were two different versions of history. GitHub then chose to protect data over keeping the site fully usable. In CAP terms, they gave up availability to keep consistency. Latency made it worse: GitHub's app servers in the East depended on low-latency database calls, so sending every write across the country made much of the site unusable. One of their follow-up fixes was to stop the failover tool from promoting databases across regions at all.

PACELC: the trade-off that exists every day

Daniel Abadi suggested a more complete version in 2012, called PACELC. It sounds scary, but it's simple:

If there's a network Partition, choose Availability or Consistency. Else (on a normal day), choose Latency or Consistency.

The second half matters more in daily life. Even with a perfect network, a strongly consistent write has to wait for other servers to confirm. If those servers are on another continent, every write waits for a round trip across the world. Systems that want speed answer from the nearest copy and accept that it might be a little out of date.

The short version: you pay for consistency with speed every day and with availability on bad days. The real question is whether a feature is worth that price.

How Do Real Systems Provide Consistency?

Now for what happens under the hood. You don't need to build any of this yourself, but knowing how it works helps you choose the right tool and explain it in interviews.

Single-leader replication (one main copy)

This is the most common setup. One server, the leader or primary, accepts all writes. It sends changes to followers or replicas. PostgreSQL and MySQL usually work this way.

There are two ways to send changes:

• Synchronous: the leader waits for a replica to confirm before telling you "saved." No data loss if the leader crashes, but writes are slower.
• Asynchronous: the leader says "saved" right away and copies the data in the background. Fast, but if the leader crashes, the last few writes might be lost and replicas can be behind.

Reads from the leader are strongly consistent. Reads from async replicas are eventually consistent.

The key point: a huge number of consistency bugs in normal web apps come from writing to the primary and then reading from a replica that hasn't caught up yet. GitHub's apps, for example, send writes to the primary but most reads to replicas. That's why lagging replicas showed users stale data during the 2018 incident.

The most dangerous moment is failover, when a replica gets promoted to leader. If the old leader accepted writes that never reached the new one, those writes are lost or create conflicts. That's the exact mess GitHub had to clean up.

Multi-leader replication

In a multi-leader setup, more than one server accepts writes, often one leader per region. Each leader sends its changes to the others in the background. Users in every region get fast writes and a region can keep working if the link to other regions breaks.

The price is write conflicts. If a user in Mumbai and a user in Frankfurt edit the same record at the same moment, two leaders accept two different values and the system needs a rule to merge them (see "Handling conflicts" below). GitHub's 2018 incident shows the same problem by accident: for a short time, two sites had accepted writes the other didn't have and untangling them was the hard part.

Leaderless replication and quorums

Some systems have no single leader. Any copy can accept writes. Amazon's 2007 Dynamo paper made this design famous and it inspired databases like Apache Cassandra.

To get useful guarantees, these systems use a quorum, which just means "enough servers agree." There are three numbers:

• N = how many copies of each piece of data exist
• W = how many copies must confirm a write
• R = how many copies a read asks

The rule is: if W + R is greater than N, every read overlaps with every write in at least one copy. So the read will find the newest value. With 3 copies, a common setting is W = 2 and R = 2.

Why does that work? Think of 3 friends. You tell your news to 2 of them. Later, someone asks 2 of them. At least one person in the second group must also be in the first group, because there are only 3 people total. So the news can't be missed.

A small simulation makes the rule concrete. It's deliberately pessimistic: each write reaches exactly W random copies, the rest stay old and then a read immediately asks R random copies.

import java.util.*;

// Toy model of leaderless replication: N replicas, each write reaches W of them,
// each read asks R of them and keeps the value with the highest version.
public class QuorumSim {
    static final int N = 3;

    static int[] versions = new int[N];   // version stored on each replica

    static void write(int version, int w, Random rnd) {
        List<Integer> ids = shuffled(rnd);
        for (int i = 0; i < w; i++) versions[ids.get(i)] = version;   // only W replicas get it
    }

    static int read(int r, Random rnd) {
        List<Integer> ids = shuffled(rnd);
        int best = 0;
        for (int i = 0; i < r; i++) best = Math.max(best, versions[ids.get(i)]);
        return best;
    }

    static List<Integer> shuffled(Random rnd) {
        List<Integer> ids = new ArrayList<>(List.of(0, 1, 2));
        Collections.shuffle(ids, rnd);
        return ids;
    }

    public static void main(String[] args) {
        int[][] configs = {{1, 1}, {2, 1}, {1, 2}, {2, 2}, {3, 1}, {1, 3}};
        int trials = 100_000;
        for (int[] c : configs) {
            int w = c[0], r = c[1];
            Random rnd = new Random(2026);
            Arrays.fill(versions, 0);
            int stale = 0;
            for (int v = 1; v <= trials; v++) {
                write(v, w, rnd);
                if (read(r, rnd) != v) stale++;   // did we see the write we just made?
            }
            System.out.printf("N=%d W=%d R=%d  W+R>N=%-5b stale reads: %6d / %d (%.1f%%)%n",
                    N, w, r, (w + r > N), stale, trials, 100.0 * stale / trials);
        }
    }
}

Real output:

N=3 W=1 R=1  W+R>N=false stale reads:  66735 / 100000 (66.7%)
N=3 W=2 R=1  W+R>N=false stale reads:  33266 / 100000 (33.3%)
N=3 W=1 R=2  W+R>N=false stale reads:  33418 / 100000 (33.4%)
N=3 W=2 R=2  W+R>N=true  stale reads:      0 / 100000 (0.0%)
N=3 W=3 R=1  W+R>N=true  stale reads:      0 / 100000 (0.0%)
N=3 W=1 R=3  W+R>N=true  stale reads:      0 / 100000 (0.0%)

With W = 1 and R = 1, a read has only a 1 in 3 chance of hitting the copy that got the write, so about two-thirds of reads are stale. Once W + R is greater than N, stale reads drop to zero.

Two honest warnings, so this doesn't mislead you:

• Real systems are far less stale than this. In real life, copies usually catch up within milliseconds. Leaderless databases like Cassandra use several repair tools for this: read repair (fixing an old copy noticed during a read), hinted handoff (a stand-in server holds writes for a server that's down and passes them on when it returns) and anti-entropy repair (replicas compare their data in the background; Cassandra uses Merkle trees for this). This model shows only the worst moment: reading right after a write, before any repair.
• W + R > N doesn't make a system perfectly strong. Things like two writes happening at once, temporary "stand-in" servers during failures and clock-based conflict rules can still cause surprises. Quorums make fresh reads very likely. They don't make them guaranteed.

Cassandra lets you pick this per query, using consistency levels like ONE, QUORUM, LOCAL_QUORUM and ALL. A team could use QUORUM for a payments table and ONE for an activity feed, in the same database.

Consensus: Raft and Paxos

When you truly need strong consistency, the standard tool is a consensus algorithm, such as Raft or Paxos. In simple terms:

• A small group of servers (usually 3 or 5) picks a leader.
• A write only counts as saved when more than half of the servers have it.
• Any two "more than half" groups always share at least one server and servers only vote for a candidate whose log is at least as up to date as their own. Together, those two rules mean a new leader always has every saved write.

A group of 3 can survive 1 server failing. A group of 5 can survive 2.

If the network splits, the smaller side can't get a majority, so it can't save anything. That's the "choose consistency" side of CAP, in action.

etcd, the data store behind Kubernetes, uses Raft and gives strongly consistent reads by default. It also offers a faster read mode that might return slightly old data.

The GitHub incident has an interesting detail here. Their database failover tool, Orchestrator, itself runs on Raft. So the decision to fail over was made correctly by consensus. But the MySQL data underneath was copied asynchronously, so the data itself wasn't protected. The lesson: using consensus in one part of your system doesn't make your whole system consistent.

Time and clocks

Many systems decide "which write wins" using timestamps. That sounds reasonable, but it's risky. Clocks on different servers drift. If server A's clock runs 50 milliseconds fast, its writes can "win" over newer writes from server B. With a rule called last-write-wins, the losing write just vanishes. No error. No warning.

Google Spanner solves this in a clever way. Its TrueTime system uses GPS receivers and atomic clocks in Google's data centers. Instead of saying "it's exactly 10:00:00," it says "it's somewhere between 10:00:00.001 and 10:00:00.005." When saving a transaction, Spanner waits until it's sure the timestamp is in the past everywhere. This wait is called commit wait. It's the price Spanner pays for strong consistency across the world.

Most of us don't have atomic clocks. So other systems use logical clocks instead, like Lamport timestamps or vector clocks. These track the order of events by counting, not by real time and can tell when two writes truly happened at the same time.

Handling conflicts

If two copies can accept changes to the same data at once, you need a rule for combining them:

The Dynamo paper's shopping cart is the famous "keep both versions" example. When copies disagreed, Dynamo returned all versions and the cart merged them by combining items. The paper is honest about the result: adding an item to your cart is never lost, but an item you deleted can come back. For a shopping cart, that's an acceptable trade. For a bank account, it obviously isn't.

Consistency vs Isolation: A Common Point of Confusion

This trips up even experienced developers and interviewers love asking about it.

• Isolation (the "I" in ACID) is about transactions. A transaction is a group of database steps that should act like one step. The strongest isolation level, serializable, means the result is the same as if transactions ran one at a time, in some order.
• Linearizability (strong consistency) is about single reads and writes on a single piece of data and it follows real-world time.

So serializable doesn't care about real time and linearizable doesn't care about multi-step transactions. When a system gives you both, it's called strict serializability. Google Spanner calls its version external consistency.

Isolation levels in plain English

Most databases don't use the strongest level by default, because it's slower. According to their official documentation, PostgreSQL defaults to Read Committed, and MySQL's InnoDB engine defaults to Repeatable Read.

Two problems matter most in real apps:

Lost update. Two requests read the same value. Both change it in code. Both save it. One change quietly disappears. We'll reproduce this with real code later in the article.

Write skew. Two requests each check a rule, both see it's OK and both make a change to different rows. Together, they break the rule.

For example, a team workspace must always have at least one admin. It has two admins, Meera and Kabir. At the same moment, both remove their own admin rights. Each request checks, "Is there at least one other admin?" Each sees yes. Both go through. Now there are zero admins.

Snapshot isolation won't catch this, because the two requests changed different rows. In PostgreSQL, the Serializable level detects it and cancels one request. At lower levels, you need to lock the rows you're checking, using SELECT ... FOR UPDATE.

Real-World Examples of Consistency

Everything in this section comes from public documents, research papers or official incident reports, linked in the references. I've kept "what the source says" separate from "the lesson."

Amazon Dynamo: never lose an "add to cart"

Amazon's 2007 Dynamo paper describes a database built for a shopping cart that must always accept writes. The reasoning was business-driven: if the cart refuses "add to cart" during a failure, Amazon loses a sale. So Dynamo accepts the write and sorts out conflicts later.

Lesson: eventual consistency works well when the business can live with the side effect (a removed item reappearing) and the merge logic is designed around the business.

Amazon S3: from eventual to strong consistency

For years, S3 (Amazon's file storage service) was eventually consistent for some operations. Developers who treated it like a normal hard drive hit strange, random bugs: save a file, list the folder and the file isn't there yet. Big data tools like Spark and Hadoop needed extra workarounds.

In December 2020, AWS announced that S3 now gives strong read-after-write consistency for saves, overwrites and listings, in all regions, at no extra cost. If your code still has workarounds written for the old behavior, they're safe to keep, but you may no longer need them.

Amazon DynamoDB: strong consistency has a price tag

DynamoDB reads are eventually consistent by default. You can ask for a strongly consistent read by setting ConsistentRead to true.

According to the DynamoDB documentation, a strongly consistent read of an item up to 4 KB uses one read unit. An eventually consistent read uses half a unit. So strong consistency costs twice as much per read. That's one of the clearest real prices for consistency you'll find anywhere. Also, queries on global secondary indexes only support eventually consistent reads.

Lesson: choose consistency per feature, not per database. A product page can use cheap eventual reads. A "check the order before charging" step can pay for a strong read.

Facebook (Meta): keeping a giant cache fresh

A 2013 research paper, "Scaling Memcache at Facebook," is mostly about one problem: keeping a huge cache from showing old data without overloading the databases behind it. Two ideas stand out:

• Leases stop a slow server from writing an old value into the cache after a newer update has already cleared it. Leases also stop a "thundering herd," where thousands of requests miss the cache at once and all hit the database. The paper reports that leases dropped the peak database load for one workload from 17,000 to 1,300 queries per second.
• Remote markers help users in other regions. After a write, a marker tells later reads of that key to go to the main region until copies catch up. That's basically read-your-writes, built on top of a cache. Both ideas come from treating the cache as just another copy of the data, with its own consistency rules.

Google Spanner: global strong consistency

Spanner (described in a 2012 research paper and now a Google Cloud product) gives strong consistency for transactions across data centers around the world, using TrueTime and commit wait. It proves strong consistency across the globe is possible, but it takes special infrastructure and adds some delay to every save, which is why most teams buy it as a service rather than build it.

MongoDB: safer defaults over time

Starting in MongoDB 5.0, the default write setting for most setups is w: "majority" (docs). That means a write is confirmed only after most servers have it. (Some setups with "arbiter" nodes still default to w: 1.) Before that, the common default confirmed a write once only the main server had it, so a crash could undo confirmed writes.

Lesson: always check your database's defaults for the exact version you run. "We use MongoDB" or "we use PostgreSQL" tells you very little about your real consistency.

Consistency in Popular Databases: Quick Reference

This table summarizes what each database gives you by default and how to ask for more. Defaults change between versions, so check the docs for the version you run.

Azure Cosmos DB deserves a special mention because it puts the whole spectrum on a single dial (docs). Bounded staleness means reads can lag by at most a set number of versions or a set amount of time. Session gives read-your-writes and monotonic reads within a client session, which is why it's the default: it matches what most apps need. Consistent prefix means you never see writes out of order, even if you're behind. It's a good concrete way to see that consistency isn't just "strong or eventual."

Consistency Bugs That Cost Real Money

Most system design articles skip this part. Your database can give you perfect guarantees and your app code can still break consistency. Why? Because many apps read data, make a decision and write data back in separate steps. Something can change in between.

Real incidents

Starbucks gift cards (2015). Security researcher Egor Homakov wrote about buying three $5 Starbucks gift cards. He used the website's "transfer balance" feature from two browser sessions at the exact same time. The same $5 moved twice. He ended up with $20 of balance from $15 of cards. He spent a little in a store to prove it was real, then added money back. The cause: the "do you have enough money?" check and the "move the money" step weren't done as one action.

Flexcoin (2014) and the ACIDRain study. In March 2014, the Bitcoin exchange Flexcoin was hit by an attack on its transfer feature using the same kind of timing trick and it shut down afterward. In 2017, researchers Todd Warszawski and Peter Bailis published a paper called "ACIDRain." They tested 12 popular open-source e-commerce apps and found 22 bugs where sending requests at the same time could do things like overspend gift cards or mess up inventory.

The pattern is always the same:

Request A                          Request B
─────────                          ─────────
read balance = 5
                                   read balance = 5
check: 5 >= 5 ✓
                                   check: 5 >= 5 ✓
set balance = 0, give +5
                                   set balance = 0, give +5   ← money created from nothing

This kind of bug is called a race condition. Two requests "race," and the result depends on who gets there first.

Reproducing the bug, then fixing it

To show this is real, here's a small program that runs two requests against a real SQLite database. Each request uses its own database connection. The steps are interleaved in a fixed order so you get the same result every time.

The scenario: an online store sells one product. Scenarios 1 and 2 start with 1 unit in stock. Scenario 3 starts with 5 units, where A wants to sell 2 and B wants to sell 1. Scenario 4 starts with 3 units. The full runnable program is in the appendix at the end of this article.

// Request A and B each have their own connection (a and b).

// 1. The bug: read, decide in application code, write back
int sold = 0;
int qa = readQty(a);                      // request A sees 1
int qb = readQty(b);                      // request B also sees 1
if (qa > 0) { setQty(a, qa - 1); sold++; } // A sells the last unit
if (qb > 0) { setQty(b, qb - 1); sold++; } // B sells the same unit again

Now three fixes. Each one moves the decision into the database, so it can't be split apart.

// Fix 1: conditional atomic update. The check and the write are one statement.
static int sell(Connection c) throws SQLException {
    try (PreparedStatement ps = c.prepareStatement(
            "UPDATE stock SET qty = qty - 1 WHERE sku = 'SKU-1' and qty >= 1")) {
        return ps.executeUpdate();   // 1 = sold, 0 = out of stock
    }
}

// Fix 2: optimistic locking with a version column.
static int updateIfVersion(Connection c, int newQty, int expectedVersion) throws SQLException {
    try (PreparedStatement ps = c.prepareStatement(
            "UPDATE stock SET qty = ?, version = version + 1 WHERE sku = 'SKU-1' and version = ?")) {
        ps.setInt(1, newQty);
        ps.setInt(2, expectedVersion);
        return ps.executeUpdate();   // 0 means someone else changed the row first
    }
}

// Fix 3: idempotency key, so a retried request is applied only once.
static boolean sellOnce(Connection c, String key) throws SQLException {
    c.setAutoCommit(false);
    try {
        int inserted;
        try (PreparedStatement ps = c.prepareStatement(
                "INSERT INTO processed_requests (idempotency_key) VALUES (?) ON CONFLICT DO NOTHING")) {
            ps.setString(1, key);
            inserted = ps.executeUpdate();
        }
        if (inserted == 1) sell(c);   // only the first delivery does real work
        c.commit();
        return inserted == 1;
    } catch (SQLException e) {
        c.rollback();
        throw e;
    } finally {
        c.setAutoCommit(true);
    }
}

Real output from running all four:

1. read-modify-write:    qty=0, units sold=2  <- oversold
2. conditional UPDATE:   A rows=1, B rows=0, qty=0
3. optimistic locking:   A rows=1, B rows=0 (B must re-read and retry), qty=3
4. idempotency key:      first applied=true, retry applied=false, qty=2

What each line tells you:

1. The bug: one item was sold to two customers. Notice the stock shows 0, which looks correct. That's why this bug often goes unnoticed until the warehouse complains.
2. Conditional update: only one request succeeded. The second one got "0 rows changed," which the app can turn into "Sorry, sold out."
3. Optimistic locking: each row has a version number. B's update was rejected because the version had already changed. B simply reads again and retries. This is a good fit when your update logic is too complex for one SQL line.
4. Idempotency key: the client sent the same request twice (for example, after a timeout) and only the first one counted. Payment APIs commonly use this idea to prevent double charges.

A note for real databases: SQLite handles one writer at a time, so this demo uses a fixed order instead of real threads. But the same bug happens under real traffic in PostgreSQL (at Read Committed) and MySQL InnoDB (at Repeatable Read) if you do a plain SELECT and then an UPDATE with a fixed value. In both databases, the conditional UPDATE ... WHERE qty >= 1 is safe. The second update waits for the first to finish, then checks the condition again against the latest data. The ON CONFLICT DO NOTHING syntax works in SQLite and PostgreSQL. In MySQL, use INSERT IGNORE or ON DUPLICATE KEY UPDATE instead.

There's a fourth option when you need to read several values before deciding: pessimistic locking. You lock the row first, so nobody else can change it until you're done.

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- other writers to this row now wait
-- check the balance in application code
UPDATE accounts SET balance = balance - 500 WHERE id = 42;
COMMIT;

It's simple and correct. But locks slow things down and can cause deadlocks if two transactions lock rows in different orders. Keep locked transactions short and always lock rows in the same order.

How to Make Users See Their Own Changes (Read-Your-Writes / Read-After-Write)

The most common consistency complaint from real users isn't about fancy distributed databases. It's this: "I just changed it, but the page still shows the old value." That happens when the app reads from a replica that hasn't caught up yet.

Here are the usual fixes, from simplest to most precise:

1. Read from the primary for data the user can edit. Easy, but it puts more load on the primary.
2. Send the user to the primary for a few seconds after they write. Easy to do with a cookie or session flag. But it's a guess. If replicas fall further behind than your window, the bug comes back.
3. Track how far the replica has caught up. After a write, remember the primary's position in its change log. When reading, only use a replica that has reached that position. Otherwise, read from the primary.

Option 3 is the precise one. This small, runnable program shows the idea:

// Read-your-writes on top of an async replica, using a log position (LSN) as a session token.
public class ReadYourWrites {
    static long primaryLsn = 0;      // position of the latest write on the primary
    static long replicaLsn = 0;      // how far the replica has replayed
    static String primaryValue = "old avatar", replicaValue = "old avatar";

    static final class Session { long lastWriteLsn = 0; }

    static void write(Session s, String value) {
        primaryValue = value;
        primaryLsn++;
        s.lastWriteLsn = primaryLsn;          // remember where our write landed
    }

    static void replicate() { replicaValue = primaryValue; replicaLsn = primaryLsn; }

    static String read(Session s, boolean useToken) {
        if (useToken && replicaLsn < s.lastWriteLsn) {
            return primaryValue + "   (routed to primary: replica is behind)";
        }
        return replicaValue + "   (served by replica)";
    }

    public static void main(String[] args) {
        Session alice = new Session(), bob = new Session();
        write(alice, "new avatar");                       // replica hasn't caught up yet
        System.out.println("Alice, naive routing:  " + read(alice, false));
        System.out.println("Alice, with LSN token: " + read(alice, true));
        System.out.println("Bob,   with LSN token: " + read(bob, true));
        replicate();
        System.out.println("After replication, Alice: " + read(alice, true));
    }
}

Real output:

Alice, naive routing:  old avatar   (served by replica)
Alice, with LSN token: new avatar   (routed to primary: replica is behind)
Bob,   with LSN token: old avatar   (served by replica)
After replication, Alice: new avatar   (served by replica)

Look at Bob's line. He still sees the old avatar. Is that a bug? No. Read-your-writes only promises that Alice sees her own change. Bob didn't make the change. If Bob must also see it instantly, that's a stronger requirement (strong consistency) and it costs more.

Real databases give you the tools for this. In PostgreSQL, pg_current_wal_lsn() on the primary gives the current log position and pg_last_wal_replay_lsn() on a replica shows how far it has caught up. MySQL with GTIDs has WAIT_FOR_EXECUTED_GTID_SET(), which makes a replica wait until it has applied certain transactions. PostgreSQL can also solve it at write time: with synchronous_commit = remote_apply, a commit waits until the synchronous standbys have applied it, so reads from those standbys see the write. The cost is slower commits. Facebook's remote markers, mentioned earlier, are another version of the same idea.

For monotonic reads (never seeing data go backwards), the trick is even simpler: always send the same user to the same replica, for example by hashing their user ID. Their view can't jump backwards between copies with different delays.

Consistency in Microservices

Inside one database, transactions do a lot of the hard work for you. Across many services, there's no shared transaction. Keeping data in sync becomes your job.

The dual-write problem

A classic bug: the order service saves an order in its database, then sends an OrderPlaced message to Kafka so other services know about it.

save order to database   ✓
send message to Kafka    ✗  (Kafka unreachable or the app crashed)

Now the order exists, but the payment, inventory and email services never hear about it. Swapping the order doesn't help. Send the message first and crash before saving and other services act on an order that doesn't exist.

Two-phase commit (2PC)

Two-phase commit coordinates one transaction across several systems. First, everyone promises they can commit. Then, everyone commits. It works, but it's slow, it holds locks while waiting and if the coordinator fails at the wrong moment, everyone can get stuck. Most modern microservice designs avoid 2PC between services for these reasons.

The outbox pattern

The standard fix for dual writes is the transactional outbox. You save the message into an outbox table in the same database transaction as the order. A separate process reads the outbox and sends the messages to Kafka.

BEGIN;
INSERT INTO orders (id, user_id, total, status)
VALUES ('ord_981', 'u_42', 1499, 'PLACED');

INSERT INTO outbox (id, aggregate_id, event_type, payload, created_at)
VALUES ('evt_5531', 'ord_981', 'OrderPlaced', '{"orderId":"ord_981","total":1499}', CURRENT_TIMESTAMP);
COMMIT;
-- A relay process polls the outbox (or tails the database log) and publishes each event,
-- marking it sent only after the broker acknowledges it.

Either both rows are saved or neither is. The relay might send a message twice (for example, if it crashes after sending but before marking it as sent). So the services receiving messages must be idempotent: they should ignore a message ID they've already handled, just like the idempotency key earlier.

Sagas

A saga splits a big business process into small steps. Each step has an "undo" step, called a compensating action.

Booking a trip might look like: reserve flight → reserve hotel → charge card. If the card charge fails, the saga runs "cancel hotel" and then "cancel flight."

Sagas give you eventual consistency across services, but not isolation. While a saga is running, other parts of the system can see the in-between state, like a hotel booked for a trip that's about to be cancelled. A common trick is to mark such records as "pending" so users and other services know they're not final.

Message queue settings matter too

Your message broker's settings are consistency settings. In Kafka, a producer using acks=all waits until all in-sync copies have the message. For those producers, the topic setting min.insync.replicas decides how many copies must be in sync before a write is accepted. If too few are in sync, they get an error instead of quietly writing to just one copy. (This setting has no effect on producers using acks=0 or acks=1.) That's a CAP choice written as configuration: refuse writes rather than risk losing them. Since Kafka 3.0, producers default to acks=all with idempotence turned on.

Cache consistency

A cache is a copy of your data without any built-in syncing, which makes it a common source of stale data. The popular "cache-aside" pattern has a well-known race:

Reader R                         Writer W
────────                         ────────
cache miss for the key
read database → old value
                                 update database → new value
                                 delete the cache key
put old value into cache    ← old value now stuck in cache until it expires

How to reduce it:

• Delete the cache key on write, don't update it. Let the next reader refill it.
• Use short expiry times (TTLs) so old data can't stay around for long.
• Use leases or version checks, like Facebook's memcache, to reject a refill that started before the latest change.
• Don't trust the cache for important decisions. For example, check stock and price from the database at checkout, even if the product page came from the cache.

How to Choose the Right Consistency Level

This is how I'd think about a typical online store with payments, feature by feature. This is an example design, not a description of any specific company.

A simple decision guide

Ask yourself one question for every feature: what happens if someone acts on old data here?

What happens if someone acts on old data for this feature?
│
├── Money lost, item oversold, a rule broken (unique, limit, quota)
│     └──► Use STRONG consistency at the moment of decision.
│          Put the check inside the database (conditional update,
│          unique constraint, lock) and make writes idempotent.
│
├── A user gets confused because THEIR OWN change "disappeared"
│     └──► Use SESSION guarantees: read-your-writes, monotonic reads.
│          Route by replica position or stick users to one replica.
│
├── Replies or events show up in the wrong order and look broken
│     └──► Use CAUSAL consistency (or attach ordering info and
│          sort on the client).
│
└── Numbers are a bit off for a few seconds and nobody is harmed
      └──► Use EVENTUAL consistency. Cache it, copy it, batch it.
           Just put a limit on how stale it can get (TTL, lag alerts).

My honest advice: start every feature at eventual consistency. Then ask, "What's the worst thing old data could cause here?" Make it stronger only where the answer is genuinely bad. It's much easier to justify the cost of strong consistency for the five places that need it than to run a system that pays that cost everywhere.

How to Monitor and Test Consistency

Consistency problems are quiet. Nothing crashes. Users just see wrong numbers. So you have to go looking for them.

Watch replication lag. In PostgreSQL, the pg_stat_replication view on the primary shows write_lag, flush_lag and replay_lag for each replica. In MySQL 8.0.22 and later, SHOW REPLICA STATUS shows Seconds_Behind_Source (older versions use SHOW SLAVE STATUS and Seconds_Behind_Master). Set alerts for lag that stays high, not just short spikes. A useful detail from GitHub's report: they estimated recovery time using a straight-line projection, but replicas actually caught up more slowly as daytime traffic grew. Lag doesn't always shrink at a steady pace, so leave yourself room.

Run reconciliation jobs. For important data that lives in two places (payments vs orders, ledger vs balance, database vs search index), compare them regularly and report differences. A daily job that says "17 orders have no payment record" catches bugs that no unit test will.

Check rules in production. Simple scheduled SQL checks like "no account has a negative balance" or "stock is never below zero" can catch bugs early.

Test with many requests at once. In your integration tests, send the same request from many threads at the same time, especially for balances, stock, coupons and limits. That's basically how the ACIDRain researchers found 22 bugs.

Test failures, not just success. Jepsen (jepsen.io) is an independent project that tests databases by breaking networks, skewing clocks and crashing servers, then checking whether the database kept its promises. Its reports are worth reading for any database you rely on. After its 2018 incident, GitHub said it would invest in fault injection and chaos engineering so it could test failure scenarios before users run into them.

Keep logs so you can fix things later. GitHub was able to start reconciling its stranded writes (954 on one busy cluster alone) because it saved the MySQL logs that contained them. Keep write logs and event IDs so you can explain and repair problems after they happen.

Trade-offs at a Glance

Neither column wins everywhere. Strong consistency puts the complexity into your infrastructure and adds delay. Weak consistency puts the complexity into your code and operations. You're really choosing where the complexity lives, feature by feature.

Common Mistakes Developers Make

Consistency Interview Questions (with Simple Answers)

Consistency shows up in almost every system design interview, usually as a follow-up like "What if this replica is behind?" or "How do you stop double booking?" Interviewers want to see that you can match the right guarantee to the problem and explain its cost.

FAQ

Key Takeaways

GitHub's 43 seconds is a good thing to remember. Copies will drift apart someday. The systems that handle it well are the ones that decided, ahead of time, what matters most when they do.

Appendix: Full Runnable Inventory Program

This is the complete program behind the four scenarios in "Consistency Bugs That Cost Real Money." It was run with OpenJDK 21 and the sqlite-jdbc driver version 3.41.2.2 (newer versions also need the slf4j logging library on the classpath):

java -cp sqlite-jdbc-3.41.2.2.jar Inventory.java

import java.sql.*;

// Deterministic interleaving of two "concurrent" requests against one database,
// using two separate connections so each has its own view of the data.
public class Inventory {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");   // needed when run with the single-file source launcher
        String url = "jdbc:sqlite:inventory-demo.db";
        try (Connection setup = DriverManager.getConnection(url);
             Connection a = DriverManager.getConnection(url);
             Connection b = DriverManager.getConnection(url)) {

            Statement s = setup.createStatement();
            s.execute("DROP TABLE IF EXISTS stock");
            s.execute("DROP TABLE IF EXISTS processed_requests");
            s.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL, version INTEGER NOT NULL)");
            s.execute("CREATE TABLE processed_requests (idempotency_key TEXT PRIMARY KEY)");

            // ---- 1. Lost update: read, compute in app code, write back ----
            reset(setup, 1);
            int sold = 0;
            int qa = readQty(a);                      // request A sees 1
            int qb = readQty(b);                      // request B also sees 1
            if (qa > 0) { setQty(a, qa - 1); sold++; } // A sells the last unit
            if (qb > 0) { setQty(b, qb - 1); sold++; } // B sells the same unit again
            System.out.println("1. read-modify-write:    qty=" + readQty(setup) + ", units sold=" + sold + "  <- oversold");

            // ---- 2. Conditional atomic update ----
            reset(setup, 1);
            int ra = sell(a);
            int rb = sell(b);
            System.out.println("2. conditional UPDATE:   A rows=" + ra + ", B rows=" + rb + ", qty=" + readQty(setup));

            // ---- 3. Optimistic locking with a version column ----
            reset(setup, 5);
            int[] va = readQtyAndVersion(a);   // both read qty=5, version=1
            int[] vb = readQtyAndVersion(b);
            int ua = updateIfVersion(a, va[0] - 2, va[1]);
            int ub = updateIfVersion(b, vb[0] - 1, vb[1]);
            System.out.println("3. optimistic locking:   A rows=" + ua + ", B rows=" + ub
                    + " (B must re-read and retry), qty=" + readQty(setup));

            // ---- 4. Idempotency key: the same request delivered twice ----
            reset(setup, 3);
            String key = "order-7f3a-retry-safe";
            boolean first = sellOnce(a, key);
            boolean second = sellOnce(b, key);   // client retried after a timeout
            System.out.println("4. idempotency key:      first applied=" + first + ", retry applied=" + second
                    + ", qty=" + readQty(setup));
        }
    }

    static void reset(Connection c, int qty) throws SQLException {
        try (Statement s = c.createStatement()) {
            s.execute("DELETE FROM stock");
            s.execute("DELETE FROM processed_requests");
            s.execute("INSERT INTO stock VALUES ('SKU-1', " + qty + ", 1)");
        }
    }

    static int readQty(Connection c) throws SQLException {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery("SELECT qty FROM stock WHERE sku = 'SKU-1'")) {
            rs.next();
            return rs.getInt(1);
        }
    }

    static int[] readQtyAndVersion(Connection c) throws SQLException {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery("SELECT qty, version FROM stock WHERE sku = 'SKU-1'")) {
            rs.next();
            return new int[]{rs.getInt(1), rs.getInt(2)};
        }
    }

    static void setQty(Connection c, int qty) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement("UPDATE stock SET qty = ? WHERE sku = 'SKU-1'")) {
            ps.setInt(1, qty);
            ps.executeUpdate();
        }
    }

    // The check and the write happen in ONE statement, so the database decides atomically.
    static int sell(Connection c) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "UPDATE stock SET qty = qty - 1 WHERE sku = 'SKU-1' and qty >= 1")) {
            return ps.executeUpdate();   // 1 = sold, 0 = out of stock
        }
    }

    static int updateIfVersion(Connection c, int newQty, int expectedVersion) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "UPDATE stock SET qty = ?, version = version + 1 WHERE sku = 'SKU-1' and version = ?")) {
            ps.setInt(1, newQty);
            ps.setInt(2, expectedVersion);
            return ps.executeUpdate();   // 0 means someone else changed the row first
        }
    }

    static boolean sellOnce(Connection c, String key) throws SQLException {
        c.setAutoCommit(false);
        try {
            int inserted;
            try (PreparedStatement ps = c.prepareStatement(
                    "INSERT INTO processed_requests (idempotency_key) VALUES (?) ON CONFLICT DO NOTHING")) {
                ps.setString(1, key);
                inserted = ps.executeUpdate();
            }
            if (inserted == 1) sell(c);   // only the first delivery does real work
            c.commit();
            return inserted == 1;
        } catch (SQLException e) {
            c.rollback();
            throw e;
        } finally {
            c.setAutoCommit(true);
        }
    }
}

It creates a small inventory-demo.db file in the current folder and prints the four lines shown earlier.