Building Software
Sections

Contents

Section 5 of 9

State

Data, Concurrency and Distribution

4 subsections, the core path through it Core 11–13.

Why it matters when agents do the typing

Lost updates, duplicate charges, stale reads and unrecoverable data rarely show up in a demo or a single-user test. They show up under real concurrency, real network failures and real data volumes, where the people who own the system get paged and the users get hurt. Understanding transactions, races and partial failure is how you find these bugs before your users do.

The course: 4 lessons, about 38 minutes of reading, in the order below.

  1. 5.1

    Data Modeling and Databases

    Core 11 · Depth: do

    Designing schemas, keys and constraints, choosing the right kind of store, and understanding how a database finds data quickly.

    A mistake it teaches you to catch: Lists stored as comma-separated strings or opaque JSON blobs, making queries slow and integrity impossible to enforce.

    Lesson: What the Schema Promises 10 min

  2. 5.2

    Transactions and Consistency

    Core 12 · Depth: do

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

    A mistake it teaches you to catch: A read-modify-write on a balance or counter with no transaction or lock, losing updates under concurrent requests.

    Lesson: Two Requests, One Row 10 min

  3. 5.3

    Concurrency and Race Conditions

    Depth: explain

    What goes wrong when many things happen at once, within one process or across many workers: races, deadlocks and overload.

    A mistake it teaches you to catch: Check-then-act races, such as checking stock and then decrementing it in two separate steps.

    Lesson: When Things Happen at Once 8 min

  4. 5.4

    Distributed Systems and Partial Failure

    Core 13 · Depth: do

    When work spans machines, any step can fail, stall or happen twice, and the design has to assume it will.

    A mistake it teaches you to catch: Retries without idempotency that charge a customer twice when the first attempt had actually succeeded.

    Lesson: The Call That Might Have Worked 10 min

Section test: State

10 questions across the 4 lessons above. Take it after the course; each answer explains itself.

  1. 1processed has an index on message_id but no unique constraint. A slow database makes one delivery take 40 seconds, so the queue hands the same message to a second worker while the first is still running. On PostgreSQL at Read Committed, what happens?
    TypeScript
    // Consumer for "shipment-received". The queue redelivers any message
    // that is not acknowledged within 30 seconds.
    async function onShipmentReceived(msg: { id: string; sku: string; quantity: number }) {
      await db.transaction(async (tx) => {
        const seen = await tx.query("SELECT 1 FROM processed WHERE message_id = $1", [msg.id]);
        if (seen.rowCount > 0) return;
        await tx.query("INSERT INTO processed (message_id) VALUES ($1)", [msg.id]);
        await tx.query(
          "UPDATE stock SET on_hand = on_hand + $1 WHERE sku = $2",
          [msg.quantity, msg.sku],
        );
      });
    }
    Show the answer

    Answer C. Neither transaction has committed when the other runs its SELECT, so both see no row, both insert, and both add the quantity to stock. Being in a transaction does not stop two copies of the same check from passing. FOR UPDATE locks rows that exist, and there is no row yet to lock. With a unique key, the second insert waits for the first transaction, then fails with a unique violation, and the update after it never runs. The atomic + prevents lost updates and does nothing about applying one message twice.

  2. 2Class 17 has 19 of its 20 seats taken, and the classes row keeps its capacity and an enrolled_count that matches its enrolled registrations. Two students register at the same moment on PostgreSQL's default isolation level, and both get in. Which change stops the class from going over capacity?
    SQL
    BEGIN;
    SELECT count(*) FROM registrations
    WHERE class_id = 17 AND status = 'enrolled';
    -- application: continue only if the count is below capacity (20)
    INSERT INTO registrations (student_id, class_id, status, fee_charged)
    VALUES (42, 17, 'enrolled', 120.00);
    COMMIT;
    Show the answer

    Answer A. Each transaction counts 19 before either inserts, and each inserts a different row, so nothing conflicts. That is write skew, which PostgreSQL's Repeatable Read still allows. A faster count narrows the window without closing it. The conditional UPDATE makes the check and the increment one statement on one row: the second registration waits for the first to commit, re-checks against 20, changes no row, and the student goes to the waitlist.

  3. 3During an incident the partner API answers every request with a 503 in 10 ms. About how many requests per second does this pool now send?
    TypeScript
    // 20 workers share `next`. The partner API allows 100 requests per second
    // and normally answers in 200 ms, so 20 in flight gives about 100 per second.
    async function worker() {
      while (next < items.length) {
        const i = next++;
        results[i] = await api.sync(items[i]); // a failed call is recorded and skipped
      }
    }
    Show the answer

    Answer D. Requests in flight equal throughput times response time, so throughput is 20 ÷ 0.01 s = 2,000 requests a second. A concurrency cap limits how many calls wait at once, and fast failures free each slot almost at once, so the pool sends 20 times the allowed rate to a service that is already failing. Enforce the rate separately, wait with backoff and jitter after a failure, and treat a 503 or 429 as the signal to slow down.

  4. 4The customer was deleted a moment before this runs. What happens?
    TypeScript
    // orders.customer_id REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      await client.query(
        "INSERT INTO orders (id, customer_id) VALUES ($1, $2)",
        [orderId, customerId],
      );
      await queue.publish("order-created", { orderId }); // the insert worked, so the order exists
      await client.query("COMMIT");
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }
    Show the answer

    Answer B. A deferred foreign key is checked at commit, so the insert succeeds and the publish runs before anything fails. The rollback removes the order, and consumers receive a message about an order that never existed. Publishing after the commit avoids that, but a crash between the two then loses the message, so it is at-most-once. An outbox row commits or rolls back with the order, and a separate process publishes it at least once.

  5. 5Which event can this job skip forever?
    TypeScript
    // Every 5 seconds, copy new events to the warehouse. Six app servers insert
    // events, and each sets created_at from its own Date.now().
    const { rows } = await db.query(
      "SELECT id, created_at, payload FROM events WHERE created_at > $1 ORDER BY created_at LIMIT 500",
      [lastSeen],
    );
    await warehouse.insert(rows);
    if (rows.length > 0) lastSeen = rows[rows.length - 1].created_at;
    Show the answer

    Answer D. The cursor assumes every new row gets a timestamp above lastSeen. A server whose clock is behind can stamp a row below it, and created_at > $1 never matches that row again. A row whose transaction commits after a run has already passed its timestamp is lost the same way. Rows stamped above lastSeen, as in the first two options, are picked up on the next run.

  6. 6On PostgreSQL, transfer(1, 2, 500) and transfer(2, 1, 300) start together, and each takes its first lock. What happens next, and what is the fix?
    TypeScript
    async function transfer(from: number, to: number, cents: number) {
      await db.transaction(async (tx) => {
        await tx.query("SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE", [from]);
        await tx.query("SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE", [to]);
        await tx.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [cents, from]);
        await tx.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [cents, to]);
      });
    }
    Show the answer

    Answer B. Each transaction holds one row lock and waits for the other's. PostgreSQL looks for a cycle once a lock wait passes deadlock_timeout, one second by default, and aborts one transaction, so one transfer fails and nothing here retries it. An in-process mutex has no such detector and would hang. Taking locks in one global order, such as lower ID first, makes the second transfer wait for the first instead of forming a cycle. FOR UPDATE locks last until the transaction ends.

  7. 7An agent says imports can now run in parallel without creating duplicate contacts, because the database enforces the unique pair. The same CSV, which has no external_id column, is imported twice at once. What ends up in the table?
    SQL
    CREATE TABLE contacts (
      id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      tenant_id    integer NOT NULL,
      external_id  text,          -- the CRM's ID; CSV imports have none
      email        text NOT NULL,
      UNIQUE (tenant_id, external_id)
    );
    Show the answer

    Answer C. A unique constraint compares values, and NULL is never equal to NULL, so any number of rows can share (tenant_id, NULL). A constraint is the right guard against concurrent writers, but only covers rows where every column in it has a value. Waiting for another transaction happens only when two inserts collide on the same key, which NULLs never do. Give imported rows a key they actually have, such as a unique index on (tenant_id, lower(email)).

  8. 8A user action calls service A, A calls B, and B calls C. A and B each make up to 3 attempts when the call below them times out. C adds points with UPDATE accounts SET points = points + 10 WHERE id = $1, which cannot lose an update. One day C is slow: every attempt commits, and every reply arrives after its caller has given up. At most how many points can one action add?
    Show the answer

    Answer C. Each of A's 3 attempts makes B try C 3 times, so C can run 9 times, and every run commits 10 points. An atomic statement stops two concurrent updates from overwriting each other; it cannot tell that two of them are the same request. The fix is an idempotency key created once for the action and passed down so C applies it once, with retries at one layer instead of two.

  9. 9On a slow day some users get two copies of their digest. Which change stops a second machine from sending a digest the first one is already sending?
    TypeScript
    // Runs every minute on 3 machines; only one should send the daily digests.
    if (await cache.setIfAbsent("digest-lock", hostname, { ttlSeconds: 30 })) {
      const { rows } = await db.query("SELECT id, user_id FROM digests WHERE sent = false");
      await sendAll(rows); // usually 10 s; 90 s when the mail provider is slow
      await db.query("UPDATE digests SET sent = true WHERE id = ANY($1)", [rows.map((r) => r.id)]);
    }
    Show the answer

    Answer A. The lock expires at 30 seconds while the first machine is still sending, and the next minute's run on another machine takes it and reads the same unsent rows. A longer TTL only needs a longer stall to fail, and checking the lock before sending is check-then-act: it can expire right after the check. The conditional UPDATE makes the check and the claim one step, so one machine claims each digest. A crash after the claim now loses that digest, so a real version marks it as sending and has a cleanup job resolve the stuck ones.

  10. 10To spread load, an agent wants to send some reads to a read replica that can run a few seconds behind the primary. Every write still goes to the primary. Which of these reads can move to the replica safely?
    Show the answer

    Answer B. A replica read is safe only where a stale answer cannot cause a wrong write or hide the user's own change. The username hint is advice: if it is stale, the primary's unique index still refuses the duplicate, and the user sees the taken message one step later. A stale balance lets through a withdrawal the real balance would refuse. A stale stock count written back as stock = $1 undoes sales the replica has not seen yet, even when orders arrive one at a time. The settings page right after a save can show the old values, since the replica may not have the write yet.