Building Software
Sections

Contents Section 5, State

Transactions and Consistency

Making multi-step changes all-or-nothing, and understanding what concurrent readers and writers can see.

Section 5 of 9, subsection 2 of 4. Depth: do. Core 12 of 25. A 10-minute read.

Mistakes to catch in review

  1. A read-modify-write on a balance or counter with no transaction or lock, losing updates under concurrent requests.

  2. A multi-step change where a failure halfway through leaves partial data behind.

  3. A read from a replica right after a write to the primary, so users don't see their own change.

  4. An assumption that the database's default isolation level prevents double-booking when it does not.

Two Requests, One Row

An agent writes you an endpoint that adds loyalty points to an account. It is four lines long and obviously correct:

TypeScript
const { rows } = await db.query("SELECT points FROM accounts WHERE id = $1", [id]);
const next = rows[0].points + earned;
await db.query("UPDATE accounts SET points = $1 WHERE id = $2", [next, id]);
return next;

It passes every test, because every test sends one request at a time. In production the same customer checks out on their phone while a referral bonus is credited in the background, and the two requests arrive a few milliseconds apart.

Both requests report success and nothing is logged, yet the account ends up holding only one of the two changes.

The database did exactly what it was told, one statement at a time. The bug lives in the gap between the SELECT and the UPDATE, and to see it you have to think about two requests at once.

The lost update, step by step

Call the two requests A and B. The account starts with 100 points. A adds 10, B adds 50. Here is one order the database is allowed to run their statements in:

Step Request A Request B Stored value
1 reads 100 100
2 reads 100 100
3 writes 110 110
4 writes 150 150

The right answer is 160. B computed its new value from a number that was already stale by the time it wrote, so A's change was overwritten. This is called a lost update, and it appears anywhere code reads a value, computes a new one in the application, and writes it back: balances, counters, inventory, the number of seats left.

You can reproduce it by hand with two terminal sessions connected to the same database. In session 1, run BEGIN; and the SELECT. In session 2, run the whole read and write and commit. Then go back to session 1, write the value it computed, and commit. At PostgreSQL's default isolation level, Read Committed, both transactions commit without an error and session 1's write overwrites session 2's change.

For an automated test, you need the two reads to both happen before either write. The simplest way is to fire many requests in parallel and check the total:

TypeScript
test("concurrent point grants are not lost", async () => {
  await resetAccount(id, 0);
  await Promise.all(Array.from({ length: 50 }, () => addPoints(id, 1)));
  expect(await getPoints(id)).toBe(50);
});

With the four-line version, this test usually ends with a total below 50, as long as addPoints goes through a connection pool with more than one connection. A test like this can pass by luck, so a stronger version forces the interleaving: pause request A after its read (with a hook, or a barrier both requests wait on) until request B has also read.

Closing the gap

The first fix is to stop doing arithmetic in the application. Let the database compute the new value inside a single statement:

SQL
UPDATE accounts SET points = points + $1 WHERE id = $2 RETURNING points;

An UPDATE takes a lock on the row it changes. At Read Committed, if two of these arrive together, the second waits for the first to commit, then re-reads the committed row and adds to it. Nothing is lost, because there is no longer a moment where one request holds a stale copy. At Repeatable Read or Serializable the second one instead fails with a serialization error and has to be retried.

You can watch both versions run against an in-memory table where every query takes 5 ms. The stand-in for the single UPDATE reads and writes in one step that nothing can come between, which is what the row lock gives the real statement.

TypeScriptRuns in your browser
const accounts = new Map<number, number>();
const roundTrip = () => new Promise((resolve) => setTimeout(resolve, 5));

// The agent's version: read, add in the application, write back.
async function addPoints(id: number, earned: number) {
  await roundTrip();
  const points = accounts.get(id)!;          // SELECT points ...
  await roundTrip();
  accounts.set(id, points + earned);         // UPDATE accounts SET points = $1 ...
}

// The fix: UPDATE accounts SET points = points + $1, one statement.
async function addPointsAtomic(id: number, earned: number) {
  await roundTrip();
  accounts.set(id, accounts.get(id)! + earned);
}

for (const add of [addPoints, addPointsAtomic]) {
  accounts.set(1, 100);
  await Promise.all([add(1, 10), add(1, 50)]);
  const pair = accounts.get(1);

  accounts.set(1, 0);
  await Promise.all(Array.from({ length: 50 }, () => add(1, 1)));
  console.log(`${add.name}: 100 + 10 + 50 = ${pair}; fifty grants of 1 = ${accounts.get(1)}`);
}

The read-then-write version loses A's 10 points, exactly as in the table. With fifty requests, all fifty read 0 before any of them writes, so the total is 1.

That works when the new value is a simple function of the old one. When there is a rule to check first, such as "stock may not go below zero", you can often fold the rule into the same statement:

SQL
UPDATE products SET stock = stock - $1
WHERE sku = $2 AND stock >= $1;

Then check how many rows it changed. Zero rows means either the rule failed or no product has that sku, so a caller that needs to tell "sold out" from "no such product" has to look further. At Read Committed no other request can slip in between the check and the write, because they are the same statement.

The second fix is to keep the read and the write separate but hold a lock between them. SELECT ... FOR UPDATE reads the row and locks it until the transaction ends:

SQL
BEGIN;
SELECT points FROM accounts WHERE id = $1 FOR UPDATE;
-- application computes the new value
UPDATE accounts SET points = $2 WHERE id = $1;
COMMIT;

A second transaction that tries SELECT ... FOR UPDATE on the same row waits at that line until the first commits, then reads the new value (at Repeatable Read or Serializable it fails with a serialization error instead). This is pessimistic locking: you assume a conflict and block it up front.

There is also an optimistic version. Give the row a version column, read it along with the data, and make the write conditional on it:

SQL
UPDATE accounts SET points = $1, version = version + 1
WHERE id = $2 AND version = $3;

If another write got there first, the version no longer matches, zero rows change, and your code reads again and retries. No lock is held while the user thinks, which suits cases where conflicts are rare and the gap between read and write is long, such as a user editing a form for a minute. The conditional UPDATE can still wait briefly if another transaction holds the row at that moment. The check only works if every writer of that row bumps version, including background jobs; one that skips it can overwrite a change without failing anyone's version check.

All or nothing

Transactions do a second job, separate from concurrency. Consider an order endpoint that inserts an order, then for each line item decrements stock and inserts the item. If the process crashes halfway through, after the order row is written, it leaves partial data behind: an order with no items. A transaction makes the whole group atomic: either every statement's effect becomes visible, or none of them does.

Here is the pattern in Node with the pg library:

TypeScript
const client = await pool.connect();
try {
  await client.query("BEGIN");
  const { rows } = await client.query(
    "INSERT INTO orders (customer_id) VALUES ($1) RETURNING id", [customerId]);
  for (const item of items) {
    const reserved = await client.query(
      "UPDATE products SET stock = stock - $2 WHERE sku = $1 AND stock >= $2",
      [item.sku, item.qty]);
    if (reserved.rowCount === 0) throw new Error(`cannot reserve ${item.sku}`);
    await client.query(
      "INSERT INTO line_items (order_id, sku, qty) VALUES ($1, $2, $3)",
      [rows[0].id, item.sku, item.qty]);
  }
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}
Two calls, BEGIN and INSERT, each made through pool.query, go into a pool. The pool sends BEGIN to connection 1, where a transaction is open, and sends INSERT to connection 2, which has no transaction. A note reads: outside the transaction.
Each pool.query call can get its own connection, so BEGIN opens a transaction on one while the INSERT runs on another.

The detail agents get wrong is pool.connect(). A transaction belongs to one connection. If the code calls pool.query("BEGIN") and then pool.query("INSERT ..."), the pool may hand each call a different connection, and the inserts run outside the transaction entirely. The code looks transactional and has no protection. With the real transaction, running out of stock on the third item throws, and the rollback undoes the order row and the first two decrements.

A transaction also covers only what happens inside the database. If the endpoint sends a confirmation email or calls a payment API in the middle, a rollback does not unsend the email. Put those side effects after the commit. A crash between the commit and the send then loses the email, so when delivery must be reliable, record the pending work in a table inside the transaction (an outbox) and have a separate worker send it, retrying until it succeeds.

Isolation: what concurrent transactions can see

Wrapping the four-line endpoint in BEGIN and COMMIT does not by itself fix the lost update. Whether it helps depends on the isolation level, which is the database's rule for how much one transaction can see of others running at the same time.

Isolation levels are defined by which anomalies they allow. An anomaly is a result that could not happen if the transactions had run one after another. PostgreSQL's behavior at each level looks like this:

Anomaly Read Committed Repeatable Read Serializable
Dirty read (seeing uncommitted data) prevented prevented prevented
Non-repeatable read (same row, two reads, two answers) allowed prevented prevented
Lost update (read, compute, write back) allowed prevented prevented
Phantom (same query, new rows appear) allowed prevented prevented
Write skew (two checks pass, combined writes break a rule) allowed allowed prevented

PostgreSQL defaults to Read Committed. MySQL's InnoDB defaults to Repeatable Read, and its Repeatable Read does not behave like PostgreSQL's, so the table above is only for PostgreSQL. Always check the documentation for the database you are actually using.

Under PostgreSQL's Repeatable Read, the lost update is prevented by an error: when B tries to update a row that A changed and committed after B's snapshot was taken, B fails with a serialization error. The snapshot is taken at a transaction's first query, not at BEGIN, so a transaction that has only said BEGIN has not yet fixed what it can see. The error is the database protecting you, and it puts a duty on you. Code running at Repeatable Read or Serializable has to catch that error and retry the whole transaction. An agent that raises the isolation level without adding a retry loop has turned silent data loss into user-facing errors under load.

Double-booking and write skew

Write skew is how a check inside a transaction can still be wrong by the time the transaction commits. A meeting room booking endpoint does this inside a transaction:

SQL
BEGIN;
SELECT count(*) FROM bookings
WHERE room_id = 5 AND starts_at < '11:00' AND ends_at > '10:00';
-- if count is 0:
INSERT INTO bookings (room_id, starts_at, ends_at) VALUES (5, '10:00', '11:00');
COMMIT;

(Times are shortened for reading; real columns would be full timestamps.)

Two people book room 5 for 10:00 at the same moment. At Read Committed or Repeatable Read, both transactions run the count and see zero, because neither has inserted yet. Both insert and both commit, so the room is double-booked.

Two transactions run on parallel timelines, time running left to right. In the first column, Check first, both run count(*) and see 0, no overlap yet. In the second column, Then insert, both INSERT 10:00 and commit. Each insert lands as its own room 5, 10:00 row in bookings, so bookings holds two rows for the same slot: double-booked. Both read before either writes.
Both transactions count zero overlapping bookings before either inserts, so both inserts commit and room 5 is booked twice.

No row was written twice, so there is no lost update for Repeatable Read to catch. Each transaction made a decision based on a check, and the other transaction's write made that check false.

Serializable isolation catches this in PostgreSQL by aborting one of the two with a serialization failure.

Two other fixes work at the default level. One is a constraint, so the rule lives in the database: a unique constraint on (room_id, slot) if bookings use fixed slots, or a PostgreSQL exclusion constraint (with the btree_gist extension) that forbids overlapping time ranges for the same room. The other is to lock something both transactions must touch, such as SELECT ... FROM rooms WHERE id = 5 FOR UPDATE before the check, so the second booking waits for the first.

When an agent says "this is safe because it is in a transaction", find the check and the write, and ask what a second copy of the same transaction would see if it ran its check before the first one committed.

Replicas and reading your own writes

Many production databases have one primary that takes writes and one or more replicas that copy the primary's changes and serve reads. The copy is not instant. The primary streams its log of changes and each replica applies them a little later.

That delay is replica lag. It varies: often milliseconds, but seconds or more when a replica is under heavy load or a large write is being applied.

Now picture a user who edits their display name. The write goes to the primary. The page redirects, and the next request reads the profile from a replica that has not applied the change yet. The user sees their old name and edits it again.

A user on the left. Step 1: the user's save goes to the primary, which now holds the new name. Step 2: the primary streams its log to the replica, labelled replica lag, and the replica still holds the old name. Step 3: the user's next read comes from the replica and returns the old name. A note reads: your own write, missing.
The new name is on the primary, but the next read goes to a replica that has not applied the change, so the user sees the old name.

The guarantee they wanted is called read-your-writes consistency: after you write something, your own later reads reflect it. Other users may see it a moment later without harm. A system that only promises the replicas will catch up eventually, if writes stop, is called eventually consistent, and it does not give you this.

A much stronger model, linearizability, says every operation appears to take effect at one instant between its start and its finish, in real-time order, as though there were one copy of the data. Sending every read to the primary removes replica lag, which is one obstacle, but the guarantee also depends on ordering across the whole system, including what happens at failover. Replicas trade some of that away in exchange for spreading the read load.

After the write commits, record the primary's log position with pg_current_wal_lsn(). Then check whether the replica has replayed that far with pg_last_wal_replay_lsn(). If not yet, read from the primary; once it has, read from the replica, which then includes your own write.
Record the log position after the write commits; read from the replica only once it has replayed that far, and fall back to the primary until then.

There are two common ways to provide read-your-writes on top of replicas. The simple one is to route a user's reads to the primary for a short window after they write, for example by storing the time of their last write in their session and sending their reads to the primary for the next few seconds. That cuts stale reads but guarantees nothing, because lag can outlast any window you pick. The precise one is to record the primary's log position after the write commits (in PostgreSQL, pg_current_wal_lsn()), and before reading from a replica, check that the replica has replayed at least that far (pg_last_wal_replay_lsn()), falling back to the primary if it has not.

Here are the three routes on a fake clock, with a replica that applies each change 50 ms after the primary and a 30 ms window for the session rule. The user saves a new name and reads it back 40 ms later:

TypeScriptRuns in your browser
let now = 0;                       // fake clock, in milliseconds
const LAG = 50;                    // this replica is 50 ms behind
const primary = new Map([["name", "Ana"]]);
const replica = new Map(primary);
const log: { key: string; value: string; at: number }[] = [];
let replayed = 0;                  // how many log entries the replica has applied

function advance(ms: number) {
  now += ms;
  while (replayed < log.length && log[replayed].at + LAG <= now) {
    const change = log[replayed++];
    replica.set(change.key, change.value);
  }
}

function write(key: string, value: string) {
  primary.set(key, value);
  log.push({ key, value, at: now });
  return { position: log.length, at: now };  // what the session remembers
}

const fromReplica = (key: string) => replica.get(key);
const withWindow = (key: string, w: { at: number }) =>
  now - w.at < 30 ? primary.get(key) : replica.get(key);
const withPosition = (key: string, w: { position: number }) =>
  replayed >= w.position ? replica.get(key) : primary.get(key);

const saved = write("name", "Ana Lima");
advance(40);
console.log("replica only:  ", fromReplica("name"));
console.log("30 ms window:  ", withWindow("name", saved));
console.log("log position:  ", withPosition("name", saved));

The window closed at 30 ms and the replica caught up at 50 ms, so for those 20 ms the session rule served the old name. The position check compared the replica's progress with the write itself and read the primary instead.

For any code that reads a value and then writes based on it, find the moment it learned the fact and the moment it acted on it. If another request can change that fact in between, you need an atomic statement, a lock, a version check, a constraint or a serializable transaction that closes the gap.

Check your understanding

7 questions on this lesson. Pick an answer to see whether it holds and why.

  1. 1An agent wrote this stock update, and two orders for the same sku keep arriving together on PostgreSQL at Read Committed. Which change makes concurrent orders subtract correctly?
    TypeScript
    const { rows } = await db.query("SELECT stock FROM products WHERE sku = $1", [sku]);
    await db.query("UPDATE products SET stock = $1 WHERE sku = $2", [rows[0].stock - qty, sku]);
    Show the answer

    Answer B. Inside one Read Committed transaction both requests can still read the same stock and write the same result, because the transaction does not stop the other request reading before this one writes. Doing the subtraction in the UPDATE makes the database apply each change to the current value under the row lock, and the stock >= $1 guard refuses to oversell. A second read narrows the window without closing it, and an index changes speed, not correctness.

  2. 2This statement reports 0 rows changed. What can the code conclude from that alone?
    SQL
    UPDATE products SET stock = stock - $1
    WHERE sku = $2 AND stock >= $1;
    Show the answer

    Answer C. The WHERE clause fails both when stock is too low and when no row matches the sku, and the row count cannot tell them apart. A caller that needs to show sold out versus no such product has to look further. A held lock makes the statement wait, after which it re-checks the condition; it does not return 0 rows because of the lock.

  3. 3An agent wrapped invoice creation in a transaction using a connection pool. A line item insert throws. What state is the database in?
    TypeScript
    try {
      await pool.query("BEGIN");
      await pool.query("INSERT INTO invoices (id, customer_id) VALUES ($1, $2)", [invoiceId, customerId]);
      for (const line of lines) {
        await pool.query("INSERT INTO invoice_lines (invoice_id, amount) VALUES ($1, $2)", [invoiceId, line.amount]);
      }
      await pool.query("COMMIT");
    } catch (err) {
      await pool.query("ROLLBACK");
      throw err;
    }
    Show the answer

    Answer B. A transaction belongs to one connection. pool.query checks out whichever connection is free for each call, so BEGIN can open a transaction on one connection while the inserts autocommit on others, and the ROLLBACK may land somewhere else again. Check out one client with pool.connect() and run every statement on it. PostgreSQL undoes only the failed statement, never the earlier autocommitted ones.

  4. 4A hospital rule says at least one doctor must be on call. Two on-call doctors each run a transaction at Repeatable Read: count the on-call doctors, see 2, and set themselves off call. Both commit, and nobody is on call. What is this, and what prevents it?
    Show the answer

    Answer C. Each transaction wrote a different row, so no update was lost; each based its write on a check that the other one's write made false. That is write skew, which PostgreSQL's Repeatable Read allows. Serializable aborts one of the two. SELECT ... FOR UPDATE on the on-call rows makes the second transaction wait; at Repeatable Read it then fails with a serialization error and must retry, and at Read Committed it re-reads the changed rows.

  5. 5To fix lost updates on the points endpoint, an agent sets the isolation level to Repeatable Read and changes nothing else. What happens under concurrent load?
    Show the answer

    Answer A. At PostgreSQL's Repeatable Read, a transaction that tries to update a row another transaction changed and committed after its snapshot fails with a serialization error. That stops the silent loss, and it hands the failure to the caller. The code has to catch that error and retry the whole transaction, or the fix trades lost points for errors under load.

  6. 6After a user saves a new display name, this handler returns the old name about one time in twenty. Which change guarantees the user sees their own write?
    TypeScript
    await primary.query("UPDATE users SET display_name = $1 WHERE id = $2", [name, userId]);
    const { rows } = await replica.query("SELECT display_name FROM users WHERE id = $1", [userId]);
    return rows[0].display_name;
    Show the answer

    Answer D. Replica lag varies and can outlast any fixed wait or window, so the first two options reduce stale reads without guaranteeing anything. Comparing the replica's replayed position with the position recorded after the write knows whether the change has arrived. One transaction cannot span two connections to two servers, so wrapping the queries changes nothing.

  7. 7Accounts use optimistic locking: editors read version and write with WHERE id = $1 AND version = $2, bumping it. A nightly job updates points with a plain UPDATE accounts SET points = ... WHERE id = $1 and leaves version alone. While an editor has the account form open, which loads and saves points along with the other fields, the job runs. What happens when the editor saves?
    Show the answer

    Answer D. The version check only detects writers that bump version. The job changed the row without touching it, so the editor's WHERE version = $2 still matches, and the save writes back the points value the form loaded earlier. Every writer of the row has to bump the version, background jobs included, for the check to protect anything.

You understand it when you can

Drill

An agent wrote a gift-card redemption endpoint that reads the balance, checks that it covers the purchase, and writes the new balance in three separate statements. Find the concurrent sequence that spends the same balance twice, and write the test that reproduces it.

Topics

Atomicity
All-or-nothing changes, and what a transaction does and does not protect against.
Isolation Levels and Anomalies
Dirty reads, lost updates, write skew and phantoms, and which isolation levels prevent which.
Locking and Optimistic Concurrency
Row locks, version numbers and compare-and-set as ways to make concurrent writers safe.
Consistency Models
Strong, eventual and read-your-writes consistency, and what each one promises a user.
Replication and Replica Lag
How copies of data are kept in sync, and what stale reads look like to users.

Further reading

Start here

Short reads

Watch

  • Jepsen 15: What Even Are Transactions? by Kyle Kingsbury. Kyle Kingsbury, 2024. 40-minute talk.

    Uses Jepsen's test results to define isolation anomalies precisely and shows real databases violating the isolation levels they advertise.

  • Distributed Systems 7.2: Linearizability. Martin Kleppmann, 2020. 19-minute lecture.

    Defines strong consistency as linearizability and shows how a read from a lagging replica after a write breaks it, which is the stale-read bug behind replica reads.

Books

  • Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. Martin Kleppmann and Chris Riccomini, 2026, 2nd edition.

    Its transactions and replication chapters define dirty reads, lost updates, write skew and phantoms, which isolation levels stop each one, and how to provide read-your-writes on top of replica lag.

  • Database Internals: A Deep Dive into How Distributed Data Systems Work. Alex Petrov, 2019.

    Explains transaction processing, recovery logs, locking and optimistic concurrency control, then replication and consistency models, from the storage engine up.

Primary sources

  • PostgreSQL Documentation: Transaction Isolation. Manual.

    The table of which anomalies each isolation level allows in PostgreSQL, and the serialization failures an application must retry under Repeatable Read and Serializable.

  • Jepsen: Consistency Models. Reference.

    A map of consistency models from serializable and linearizable down to read-your-writes, each with a short definition of what it promises.