LLearnCode
Academy/sql/State under change

advanced · 26 minute study

Reason about two writers, not one happy path

A single-user test can pass while two users sell the same last item. Concurrency changes what it means for a read to justify a later write.

Loading progressEvidence grows with practice
Useful preparation · 1 prerequisite

Use this as a map, not a lock. If these ideas are familiar, continue.

  • Make a change an all-or-nothing unit — An order without its lines is not half a successful feature. It is a broken state that later screens, reports and retries must somehow interpret.

First, build a mental model

A read is an observation at a moment. A safe write rechecks the invariant where competing changes are serialized.

  1. A browser reading stock = 4 does not reserve those units. Another request can consume them before this browser submits.
  2. A conditional UPDATE moves the availability predicate to the write boundary. Inspect affected rows; zero affected rows is a rejected reservation, not success.
  3. PostgreSQL uses row locks and isolation rules to coordinate concurrent writers. Higher isolation can reject a transaction with a serialization failure; retry the whole transaction with a bounded policy.
  4. Preserve the same operation identity across uncertain retries. Consistent lock ordering reduces deadlock risk; neither isolation nor retries replace tenant authorization.
Read the codesql · educational example
UPDATE products
SET stock = stock - 3
WHERE id = 1 AND stock >= 3
RETURNING id, stock;
-- Run again from the resulting state.
-- Accept a reservation only if exactly one row returned.
  1. First writer

    Starting from stock 4, the conditional write returns stock 1.

  2. Second writer

    The same predicate no longer matches. No row is returned and stock remains 1.

  3. Workflow boundary

    Only an accepted reservation may proceed to dependent writes inside the same authoritative transaction.

Production thinking

Take this beyond the example.

Use a disposable two-session test: synchronize competing starts, preserve both results, inspect final stock and related rows, and prove no duplicate effect. Report the exact isolation level and engine.

Sources and further reading

Your evidence

unseen is a local learning signal, not a credential or proof of production competence.

Explain the mental model, then explicitly acknowledge it. Opening a page is not evidence.

Supported retries can complete this lesson. Independent evidence and delayed review determine the skill stage.

Next lesson · Change a schema while old data still exists →