The question this answers
My database handles concurrency. Which of my concurrency problems does that actually solve?
A seat-booking endpoint: read available seats, decide, insert a booking, and update an in-process cache of remaining capacity — under concurrent requests for the same event.
Two distinct things: the seats and bookings rows in the database, and the process-local capacityCache map. The database protects the first. Nothing protects the second.
No seat is booked twice, and the cached remaining-capacity figure never claims capacity that the database does not have.
Synchronization exists to preserve this sentence. If a schedule can make it false, the code is wrong no matter which primitive it uses.
Where the guarantee starts and stops
A database gives you a serious set of tools: transactions with ACID properties, row and predicate locks, and multi-version concurrency control so readers do not block writers. Under a sufficiently strong isolation level, concurrent transactions behave as if they ran one after another. That is a real, hard guarantee about the data *in the database*, and it is covered properly in Transactions and ACID, Isolation Levels, MVCC: Multi-Version Concurrency Control and Locks and Deadlocks.
The boundary is precise: the guarantee covers operations the database performed, within a transaction, on data it stores. It does not extend one instruction past that. A value you read into a variable is a snapshot with no continuing relationship to the row. A decision your application makes between two statements is not part of any transaction. A cache you populate is a copy with no invalidation contract. The database does not know these exist.
This matters because the strength of the database guarantee makes people stop reasoning. "It is in a transaction" gets treated as "it is safe", and the check-then-act that happens in application code between the SELECT and the INSERT is invisible to the transaction unless the isolation level or an explicit lock covers it. The schedule below is that gap.
| # | Request 1 (txn A) | Request 2 (txn B) | Database | State |
|---|---|---|---|---|
| 1 | BEGIN; SELECT count(*) FROM bookings WHERE event=7 -> 99 | · | · | capacity=100 bookings=99 |
| 2 | application code: 99 < 100, so proceed | · | · | capacity=100 bookings=99 |
| 3 | · | BEGIN; SELECT count(*) -> 99 | · | capacity=100 bookings=99 |
| 4 | · | application code: 99 < 100, so proceed | · | capacity=100 bookings=99 |
| 5 | INSERT booking; COMMIT | · | · | capacity=100 bookings=100 |
| 6 | · | INSERT booking; COMMIT | · | capacity=100 bookings=101 ✕ Bookings never exceed capacity. 101 bookings for 100 seats, and both transactions committed without error. |
| 7 | · | · | both transactions reported success | bookings=101 |
| 8 | capacityCache[7] = 1 (computed from its stale read of 99) | · | · | bookings=101 cache[7]=1 ✕ The cache never claims capacity the database does not have. It now advertises a free seat that does not exist. |
Four fixes, four costs
The first fix is to *express the invariant where the database can enforce it*. A unique constraint on (event, seat) makes double-booking a specific seat impossible, and the database will reject the second insert. This is the strongest and cheapest option and is chronically underused: constraints are concurrency control that never forgets. Its limit is that not every invariant is expressible as one — "at most 100 bookings" is not a uniqueness property.
The second is pessimistic locking: SELECT ... FOR UPDATE on the event row makes the second transaction wait until the first commits, at which point it reads 100 and correctly refuses. It works, and it serializes all bookings for a popular event through one row — which is What Contention Actually Costs with a database row as the lock, complete with lock waits, deadlock risk between transactions that lock rows in different orders, and held connections. See Locks and Deadlocks and Deadlock Detection: The Waits-For Graph.
The third is serializable isolation, which makes the database detect the conflict and abort one transaction. Correct, and it requires your application to actually handle the serialization failure with a retry — code that is frequently missing, so the fix silently becomes a 500 under load. The fourth is optimistic: a conditional update guarded by a version or a count, retried on failure. That is the same retry burden with more explicit control. Optimistic vs Pessimistic is the choice, and contention rate is what decides it.
| Mechanism | What it guarantees | What it costs | Fails when |
|---|---|---|---|
| Unique constraint / check constraint | The database rejects any write that violates it, forever, regardless of application code | Almost nothing; an index and a rejected insert | The invariant is not expressible as a constraint on one row |
| SELECT ... FOR UPDATE on the event row | Serializes all bookings for that event; the second reads the first's committed result | All bookings for a hot event queue on one row; connections held for the lock duration | Two transactions lock rows in different orders — deadlock; the database aborts one |
| Serializable isolation | The database detects the conflicting schedule and aborts one transaction | Higher abort rate under contention; every caller must implement retry | The application does not handle serialization failure, turning correctness into 500s |
| Conditional UPDATE with a version or count | The write applies only if the world still matches what you read | A retry loop you write and must bound; wasted work on each retry | Contention is high enough that retries dominate — then take a lock instead |
| Application-level mutex | Nothing, across more than one process | False confidence | Always, the moment there are two instances — A Mutex on Server A Does Nothing About Server B |
The cache is where the guarantee ends, visibly
The second half of the incident is the cache, and it is the part transaction reasoning never covers. capacityCache is process-local memory populated from a transactional read. From the moment it is written it is a copy of a past state, with no relationship to the row it came from. Two processes have two caches that disagree with each other and with the database, and the database has no idea either exists.
Worse, the cache write in the schedule happens *after* the commit and uses a value computed from a stale read — so it is wrong in a way that outlives the transaction and keeps being wrong. Under threads there is also a genuine data race on the map itself unless it is synchronized; under an event loop there is no data race but there is still an ordering race across the await. Both produce a cache that advertises capacity that does not exist.
The honest framing is that a cache is a second copy of state with its own concurrency problem, and the database's guarantees stop at its own storage. Cache Invalidation, Stampedes and Hot Keys and Caching Patterns cover the invalidation strategies; what matters here is recognizing that the moment you copy a row into process memory, you have left the region where transactions apply and re-entered the region this whole domain is about.
1const capacityCache = new Map<number, number>() // process-local. Not shared. Not protected.2 3async function book(eventId: number, userId: number) {4 return db.transaction(async (tx) => {5 // Inside the transaction: the database's guarantees apply here.6 const { count } = await tx.one(7 'SELECT count(*) AS count FROM bookings WHERE event = $1', [eventId])8 const { capacity } = await tx.one(9 'SELECT capacity FROM events WHERE id = $1', [eventId])10 11 // <-- NOT inside any guarantee. This comparison happens in your12 // process, on two snapshots, with no lock and no constraint.13 if (count >= capacity) return 'SOLD_OUT'14 15 await tx.none(16 'INSERT INTO bookings (event, user) VALUES ($1, $2)', [eventId, userId])17 18 // <-- also outside: a write to process memory from inside a19 // transaction that might still roll back.20 capacityCache.set(eventId, capacity - count - 1)21 return 'OK'22 })23}24 25// THREE separate problems, only one of which the database can see:26//27// 1. check-then-act across two statements -> fix in the DATABASE:28// INSERT ... WHERE (SELECT count(*) ...) < capacity29// or a constraint, or SELECT ... FOR UPDATE, or serializable + retry.30//31// 2. cache written before commit is known -> fix in APPLICATION:32// write the cache after commit succeeds, never inside the txn.33//34// 3. cache is per-process and unsynchronized -> fix in ARCHITECTURE:35// one shared cache, or accept staleness explicitly with a TTL,36// or do not cache a value that must be exact.Key points
- Transactions, locks and MVCC give hard guarantees about data in the database, within a transaction. They extend zero instructions past that boundary.
- A check performed in application code between two statements is not covered by the transaction unless isolation level or an explicit lock covers it.
- The canonical failure is two transactions both reading 99 of 100, both deciding to proceed, and both committing — with no error anywhere.
- Four fixes with four costs: a constraint (cheapest, most durable), SELECT FOR UPDATE (serializes a hot row), serializable isolation (needs retry code), conditional update (needs a bounded retry loop).
- A constraint is concurrency control that never forgets, and it is chronically underused compared to application-level checks.
- A cache populated from a row is a copy with its own concurrency problem, in a region where the database's guarantees do not reach.
The loop, answered
Every field is required, which is why no lesson here can recommend concurrency without naming the interleaving that breaks it, the complexity it adds, and the simpler thing to consider first.
- • A transaction groups statements so they commit or roll back together, and isolation determines what concurrent transactions may observe of each other.
- • MVCC gives each transaction a consistent snapshot so readers do not block writers — which is exactly why two transactions can both read 99 without conflicting.
- • Row locks serialize writers to the same row; explicit
FOR UPDATEextends that to readers who intend to write. - • Serializable isolation detects schedules that could not have arisen from any serial order and aborts one participant, converting a correctness problem into a retry problem.
- • Constraints are evaluated by the database on every write, independently of application code, transaction boundaries and process count.
- • T1 reads 99, T2 reads 99 under snapshot isolation, both decide to proceed, both insert, both commit. 101 bookings, zero errors — the check was outside the transaction's reach.
- • With
SELECT ... FOR UPDATEon the event row: T2 blocks until T1 commits, then reads 100 and refuses. Correct, and every booking for that event is now serialized through one row. - • With serializable isolation: the database aborts T2 with a serialization failure. If the application does not retry, the user sees an error for a seat that was available — correctness traded for an unhandled failure path.
- • Two application processes, two
capacityCachemaps: process A shows 1 seat left, process B shows 3, and the database has 0. Nothing is synchronized because nothing was ever shared. - • A transaction rolls back after the cache line executed: the database is correct and the cache holds a value derived from a transaction that never happened.
- • The database guarantees atomicity, isolation at the level you configured, and durability — for statements inside the transaction.
- • It does not guarantee anything about values you copied out, decisions you made between statements, or memory in your process.
- • Snapshot isolation specifically guarantees a consistent read view. It does *not* guarantee serializability, which is why two transactions can both act on the same snapshot and both commit.
- • A unique constraint guarantees enforcement regardless of which process, which code path, or which future maintainer writes the insert. Nothing else on this list has that property.
- • Serializable isolation guarantees conflict detection, not conflict avoidance: your application must handle aborts, or the guarantee becomes an outage.
- •
SELECT ... FOR UPDATEon a hot row serializes every writer through it; a popular event turns into a single-threaded booking queue with the connection held for the duration. - • Higher isolation levels increase abort rates under contention, and each abort is work performed and discarded plus a retry that competes with fresh arrivals.
- • Held transactions hold connections, so lock waits inside the database consume the connection pool outside it — Connection Pool Saturation: Waiting in Front of an Idle Database.
- • Deadlocks between transactions locking rows in different orders are resolved by aborting one, which is correct and shows up as intermittent errors under load — Lock Ordering.
- • Lost update: two transactions read, both compute from the same value, and the second write overwrites the first — the classic, and The Lost Update, Step by Step on the API side.
- • Write skew: two transactions each read a set, each make a decision valid in isolation, and together they violate a constraint neither could see. Snapshot isolation permits this by design — see Concurrency Anomalies.
- • Unhandled serialization failure, converting a correctness improvement into user-visible 500s under contention.
- • Cache divergence across processes, advertising capacity that does not exist.
- • Cache written inside a transaction that later rolls back, leaving memory ahead of committed reality.
- • Deadlock between transactions with inconsistent lock ordering, appearing as intermittent aborts that no test reproduces.
- • Any invariant expressible as a constraint — uniqueness, foreign keys, check conditions — where the database enforces it permanently and no future code path can bypass it.
- • Moving a decision into a single statement, so the check and the act happen atomically inside the database instead of straddling application code.
- • Using the database as the coordination point across processes, which is the correct answer when a local lock cannot work — A Mutex on Server A Does Nothing About Server B.
- • When a hot row becomes the serialization point for the whole system, converting a scaling problem into a queue on one lock.
- • When the cost of a retry loop under high contention exceeds the cost of simply taking a lock — the optimistic-versus-pessimistic threshold.
- • When application caches are added for speed without an invalidation story, making the system fast and wrong.
- • When "it is in a transaction" is used as a reason to stop reasoning about interleavings, which is exactly when this failure appears.
- • Serialization-failure and deadlock rates from the database, which are the direct signal that concurrent transactions are conflicting.
- • Lock wait time inside the database, distinct from application-level lock wait — Low CPU, High Latency: Lock Contention.
- • Retry counts and retry-loop exhaustion, which is where optimistic control fails silently.
- • Cache-versus-database divergence, sampled: compare cached values against a fresh read and count disagreements.
- • Transaction duration, since a long transaction holds locks and a connection for its entire length.
- • Isolation levels are a real thing to understand — the anomalies each one permits are not intuitive and are the source of most surprises here.
- • Retry loops must be bounded, idempotent and instrumented, or they convert contention into a retry storm — Retry Storms: The Load You Generated Yourself.
- • Constraints move enforcement away from the code, which is correct but means error handling for constraint violations must exist in every path that writes.
- • Every cache adds an invalidation contract that must be maintained separately from the transactional logic.
- • Express the invariant as a constraint and let the database enforce it — first choice whenever it is expressible.
- • Collapse check-and-act into one statement:
INSERT ... SELECT ... WHERE, or a conditionalUPDATE, so the decision happens inside the database. - • Serialize the work through a queue keyed by event, so only one booking per event is ever in flight — a concurrency design rather than a locking one. See Message Passing.
- • Drop the cache for values that must be exact, and cache only what tolerates staleness with an explicit TTL — Caching Patterns.
- • Optimistic concurrency with a version column when contention is low, pessimistic locking when it is high — Optimistic vs Pessimistic.
What people believe, and what is true
It is inside a transaction, so it is safe from races.
The transaction covers the statements it contains. A comparison your code performs between two of them is not part of it, and snapshot isolation will happily let two transactions make the same decision.
MVCC means readers and writers never conflict.
They do not block each other. That is precisely why two transactions can read the same snapshot, both decide to act, and both commit — write skew is a feature of the isolation level, not a bug.
The database handles concurrency, so my application does not need to.
It handles concurrency for its data. Your caches, counters, in-memory state and cross-statement decisions are entirely yours, and they are where this domain applies.