Transactions, Consistency & Race Conditions

Stage 3 · Data & Databases · B.U.I.L.D. letter: L

You shipped a payment feature in a weekend using vibes and it works great in testing. Then two users click "Transfer" at the exact same millisecond and one of them loses $50 that never arrives anywhere. Congratulations — you just discovered why databases have transactions. This lesson turns that panic into muscle memory.

---

⚠️ The vibe trap

You built a transfer feature: read the sender's balance, subtract the amount, add it to the receiver, done. Two lines of SQL, maybe three. Feels clean. But if your server crashes between the subtract and the add, money has left one account and arrived nowhere. Worse, if two requests both read the sender's balance at the same instant, both see "enough funds," both subtract — and you've just created money out of thin air (or destroyed it). Every multi-step write that touches shared data has this problem, and "it works in testing" is not a defense because you never tested two simultaneous requests hitting an unlucky interleave.

---

🔄 Why Writes Need to Be All-or-Nothing

A transaction is a promise: either every statement inside it succeeds and is saved permanently, or none of them are. No partial saves. No half-transferred money.

-- Without a transaction — dangerous!
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
-- Server dies here. Alice lost $100. Bob got nothing.
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';

-- With a transaction — safe
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;
-- Both updates land together, or neither does.

Mental model: Think of BEGIN as picking up a pen, COMMIT as signing the contract, and ROLLBACK as shredding the paper and starting over. While you're writing (between BEGIN and COMMIT), no one else sees your draft.

Why it matters: Without a transaction, the database has no way to know those two UPDATE statements are related. It treats them as independent operations. A crash, a timeout, or a constraint violation after the first one will leave your data permanently inconsistent.

Common mistake: Wrapping only the "risky" second statement in a transaction. Both statements must be inside the same BEGIN/COMMIT block, or the atomicity guarantee is worthless.

---

🧱 ACID in Plain Language

ACID is not a chemistry class — it is the four guarantees every production-grade relational database makes when you use transactions correctly.

-- Consistency example: a CHECK constraint enforces valid state
CREATE TABLE accounts (
  id      TEXT PRIMARY KEY,
  balance NUMERIC NOT NULL CHECK (balance >= 0)
);

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'alice';
  -- If alice only has $200, the CHECK fires here.
  -- The whole transaction rolls back automatically.
  UPDATE accounts SET balance = balance + 500 WHERE id = 'bob';
COMMIT;

Mental model: Atomicity is about time (all-or-nothing). Consistency is about rules (valid state). Isolation is about concurrency (no peeking). Durability is about survival (crash-proof).

Why it matters: Vibe-coded apps often get A and D for free just by using a real database. C requires you to actually write constraints (see D2-01). I is where most production bugs hide — and it is the subject of the rest of this lesson.

Common mistake: Assuming your ORM gives you ACID automatically. ORMs wrap individual queries in their own implicit transactions. Multi-step operations across multiple ORM calls require you to explicitly open a transaction, or each call commits independently.

---

⚡ Race Conditions: The Silent Money Thief

A race condition happens when two concurrent operations read the same data, make a decision based on it, then both write back — and one write silently overwrites the other.

The lost update / double-spend pattern:

Time →    Request A               Request B
T1        SELECT balance → 200    SELECT balance → 200
T2        balance - 100 = 100     balance - 100 = 100
T3        UPDATE balance = 100    (still computing)
T4                                UPDATE balance = 100
T5        COMMIT                  COMMIT

Alice started with $200. Two transfers of $100 ran simultaneously. The database now shows $100. Bob got $100. Carol also got $100. Alice lost $100 that the database never subtracted twice.

-- This code has the race condition
-- (shown as two statements to illustrate the problem)

-- Session A
SELECT balance FROM accounts WHERE id = 'alice'; -- returns 200
-- Session B reads here too, also gets 200
UPDATE accounts SET balance = 100 WHERE id = 'alice'; -- A writes 100
-- Session B writes 100 as well — overwrites A's commit
UPDATE accounts SET balance = 100 WHERE id = 'alice'; -- B writes 100

Mental model: The race condition is not about the database being wrong. The database faithfully executed both UPDATE statements. The bug is in your application logic: reading, computing outside the database, then writing back creates a window where the world can change underneath you.

Why it matters: This exact bug caused real financial losses at real companies. It is impossible to reproduce reliably in development because you need precise timing. It will happen in production under load.

Common mistake: Testing race conditions with a single browser tab. Always test concurrency with two simultaneous requests — Promise.all([transfer(), transfer()]) in a test file will surface this immediately.

---

🔒 Fix #1 — Transactions with Row Locking (SELECT FOR UPDATE)

SELECT ... FOR UPDATE tells the database: "I am about to write to this row. Lock it so no other transaction can touch it until I commit."

-- Safe transfer using row-level locking
BEGIN;
  -- Lock both rows immediately; other transactions wait here
  SELECT id, balance
  FROM accounts
  WHERE id IN ('alice', 'bob')
  ORDER BY id          -- always lock in a consistent order to prevent deadlocks
  FOR UPDATE;

  -- Now do the math inside the transaction, not in your app
  UPDATE accounts
  SET balance = balance - 100
  WHERE id = 'alice' AND balance >= 100;  -- guard: refuse if insufficient

  -- Check rows affected; if 0, alice didn't have enough → rollback
  UPDATE accounts
  SET balance = balance + 100
  WHERE id = 'bob';
COMMIT;

In JavaScript with a real driver (pg / node-postgres pattern):

async function transfer(pool, fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Lock both rows; ORDER BY prevents deadlocks between concurrent transfers
    const { rows } = await client.query(
      `SELECT id, balance
       FROM accounts
       WHERE id = ANY($1::text[])
       ORDER BY id
       FOR UPDATE`,
      [[fromId, toId].sort()]
    );

    const sender = rows.find(r => r.id === fromId);
    if (!sender || Number(sender.balance) < amount) {
      await client.query('ROLLBACK');
      throw new Error('Insufficient funds');
    }

    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
  } catch (err) {
    // If COMMIT hasn't been called, roll back to be safe
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release(); // always return the connection to the pool
  }
}

Mental model: FOR UPDATE is a turnstile. The first transaction walks through and locks it. Every other transaction that wants those same rows queues up and waits. When the first commits, the next one gets to enter — but now reads the freshly committed data, not the stale snapshot.

Why it matters: Without the lock, two transactions can both read the balance, both decide "sufficient funds," and both subtract. With the lock, the second transaction waits, then re-reads the already-reduced balance and correctly sees insufficient funds.

Common mistake: Forgetting FOR UPDATE on the SELECT and only relying on the subsequent UPDATE to be safe. The race window is the time between your SELECT and your UPDATE. Lock it at the SELECT.

---

⚛️ Fix #2 — Atomic Updates (No Read Required)

For simpler cases — decrementing a counter, spending from a balance, claiming a seat — you can skip the SELECT entirely and do the math inside the UPDATE. The database evaluates the expression and the guard in a single atomic operation.

-- Atomic decrement with a guard: no separate SELECT needed
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 'widget-42'
  AND quantity >= 1;
-- Check rows_affected in your driver: 0 means out of stock, 1 means success.

// Atomic update in JS — no transaction boilerplate needed for single-row cases
async function reserveSeat(pool, eventId, userId) {
  const result = await pool.query(
    `UPDATE events
     SET seats_remaining = seats_remaining - 1
     WHERE id = $1
       AND seats_remaining >= 1
     RETURNING id, seats_remaining`,
    [eventId]
  );

  if (result.rowCount === 0) {
    throw new Error('No seats available');
  }

  // Optionally: insert into bookings table here (wrap in a transaction if so)
  return result.rows[0];
}

Mental model: The expression quantity - 1 and the guard AND quantity >= 1 are evaluated by the database engine atomically. There is no application-level read that can go stale. Two concurrent requests will serialize at the row level — one will decrement, the other will find quantity = 0 and be blocked or find zero rows affected.

Why it matters: This pattern is simpler and faster than SELECT FOR UPDATE for single-row operations. No transaction overhead, no explicit locking ceremony. The database's own row-level locking handles concurrency.

Common mistake: Using this pattern when you need to write to two rows (like a transfer). Atomic updates are perfect for "change one thing safely." The moment you need "change A, then change B, and both must succeed together," you need a transaction.

---

🛠️ Your Mission

1. Find one multi-step write in your app — a form submission that touches two tables, an order that decrements stock and creates a record, a message send that marks a notification read. Wrap it in a BEGIN / COMMIT transaction using your database driver's explicit transaction API. Add a ROLLBACK in the error handler. Deploy and verify both paths (success and failure) work correctly.

2. Find one read-then-write pattern — a place where your code does SELECT x, computes something in JavaScript, then UPDATE x = <computed value>. Replace it with an atomic UPDATE ... SET x = x - $1 WHERE x >= $1 pattern. Verify with a quick test that two simultaneous requests cannot both succeed when only one should.

---

✅ You're done when…

• Every multi-step write in your app that touches more than one row or table is wrapped in an explicit transaction with a ROLLBACK on error (Production-Readiness Checklist: data integrity under failure)
• At least one read-then-write pattern has been replaced with an atomic UPDATE ... WHERE condition and the app correctly returns a "failed / unavailable" response (not a silent bad write) when the condition is not met
• You can explain to a teammate, without looking at notes, what SELECT ... FOR UPDATE does and why ORDER BY id prevents deadlocks when locking multiple rows

---

➡️ Next: Migrations. Build It Right, Or Don't Build It At All. 🏛️