Database field guide · 10
Schema Design for Concurrency
Make invariants executable
Move correctness from timing assumptions into keys, constraints, indexes, queues, and retryable transactions.
Franck Pachot7 chaptersschema design · invariants · contention
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
Start with the invariant
Concurrency bugs survive when a rule exists only in application control flow. State the invariant independently: one active reservation per seat, no negative balance, every child has a parent. Then choose the database mechanism that can evaluate it atomically.
- Unique and exclusion constraints arbitrate competing writes.
- Foreign keys protect relationships across transactions.
- Checks protect row-local facts, not arbitrary cross-row totals.
02
Read then write is not atomic
Two transactions can both observe absence or an old value before either writes. Repeating the check in application code does not close the race. Conditional DML, constraints, locks, or serializable conflict detection must connect the decision to the write.
- Prefer INSERT with conflict handling over check-then-insert.
- Use UPDATE ... WHERE state = expected for compare-and-set.
- Check affected row counts before reporting success.
03
Keys can create hotspots
A globally increasing key concentrates inserts on the newest index pages; a single counter row serializes every increment. These may be acceptable until concurrency crosses a threshold, but schema choices should make the contention domain intentional.
- Do not sacrifice stable identity merely to randomize writes.
- Partition counters and queues by a meaningful business domain.
- Measure latch, lock, and tablet contention before redesigning keys.
04
Queues need claim semantics
Multiple workers must claim distinct work without blocking behind the oldest busy item. SELECT FOR UPDATE SKIP LOCKED and atomic state transitions can implement scalable claiming, provided leases, failures, and duplicate processing are part of the model.
- Assume a worker can die after claiming work.
- Make processing idempotent or record effects transactionally.
- Order only where the business requires it.
05
Indexes define conflict precision
An index is not only a read accelerator. It lets the engine find conflicting keys and lock a narrow part of a relation. Missing or poorly ordered indexes can turn a local invariant check into broad scans, longer locks, and larger deadlock surfaces.
- Index the keys used to find and arbitrate work.
- Keep transactions short after a lock is acquired.
- Acquire multiple logical resources in a consistent order.
06
Serializable still requires retries
Serializable isolation detects executions that cannot safely coexist and aborts one participant. The schema should minimize false contention through precise predicates and indexes, while the application treats a serialization failure as a request to replay the transaction.
- Retry from a clean transaction boundary.
- Use bounded backoff and preserve request identity.
- Test the invariant under concurrent load, not only the happy path.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Encode one active row as a unique fact
If only one active reservation may exist per seat, a partial unique index makes competing inserts arbitrate on one key. The application no longer relies on a prior absence check. One transaction succeeds; the other waits and then receives a unique violation or follows ON CONFLICT behavior. This works at Read Committed because the index is the coordination point.
CREATE UNIQUE INDEX one_active_reservation_per_seat
ON reservation (show_id, seat_id)
WHERE cancelled_at IS NULL;
Use compare-and-set for state transitions
An UPDATE predicate can combine validation and mutation atomically. A zero row count means another transaction changed the expected state or the business condition is false. This avoids a lost update without holding an application-side value between SELECT and UPDATE. RETURNING gives the committed candidate state to the caller in the same round trip.
UPDATE account
SET balance = balance - $1, version = version + 1
WHERE account_id = $2
AND version = $3
AND balance >= $1
RETURNING balance, version;
Claim queue rows without convoying
FOR UPDATE SKIP LOCKED lets workers ignore rows already claimed by concurrent transactions. Keep selection and state transition in one statement so the lock cannot escape between calls. SKIP LOCKED does not provide fairness and a failed worker still requires lease expiry or transaction rollback. Processing outside the transaction requires an idempotency key.
WITH claim AS (
SELECT job_id FROM job
WHERE state = 'ready' AND run_after <= now()
ORDER BY priority DESC, job_id
FOR UPDATE SKIP LOCKED LIMIT 10
)
UPDATE job j SET state = 'running', worker_id = $1, started_at = now()
FROM claim WHERE j.job_id = claim.job_id
RETURNING j.*;
Retry a transaction, not a statement fragment
Deadlock victims and serialization failures roll back the transaction's logical decision. Retrying only the final UPDATE reuses observations from a failed snapshot and is incorrect. Begin again, repeat every read and check, preserve a request id for deduplication, and bound retries with jittered backoff. PostgreSQL reports serialization failure as 40001 and deadlock detected as 40P01.
retry transaction on SQLSTATE in ('40001', '40P01'):
BEGIN
read invariant state
perform conditional writes
COMMIT
-- External messages use an outbox row committed with the data.
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
