ConcurrencyGENERALDATABASE-SPECIFICSCALE-SPECIFIC

Atomic Operations

Do it in one statement the database evaluates indivisibly, instead of reading, deciding and writing from application code.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Can the database make this decision for me, so there is no window for anyone to write into?

The requirement

Decrement remaining inventory when an item is bought, and never let it go below zero, however many people buy at once.

The obvious build

Read stock, check that it is greater than zero in application code, then write stock - 1. The check and the decrement are right next to each other.

Why it breaks

Two requests read stock = 1, both pass the check, both write 0. Two items sold, one in stock. The check happened against a value that changed before the write (Backend Races).

How it breaks in production

Two requests read stock = 1, both pass the check, both write 0. Two items sold, one in stock. The check happened against a value that changed before the write (Backend Races).
Wrapping it in a transaction does not help at read-committed — neither transaction saw the other's write, and both commit.
Fixing it with a lock works and serialises the row for the duration of the transaction, which is far longer than the operation needs (Pessimistic Locking).
Fixing it with a version column works and costs a retry on every conflict, which on a popular item is most requests (Optimistic Concurrency).
The value read into application memory is stale the instant it arrives. Any arithmetic performed on it is arithmetic on a snapshot.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

A single SQL statement is evaluated atomically with respect to other statements: the row is locked for the duration of the update and released when the statement completes within its transaction, so no other writer can observe or modify the intermediate state.
Moving the condition into the WHERE clause moves the decision into that atomic evaluation. UPDATE stock SET n = n - 1 WHERE id = $1 AND n > 0 tests and acts indivisibly.
The affected-row count is the outcome: one means the decrement happened, zero means the condition was false. That count is how the application learns what the database decided (Optimistic Concurrency uses the same signal).
Arithmetic expressed relative to the current value — n = n - 1, balance = balance + $2 — is computed by the database from the value at write time, so it never operates on a stale read.
This is the database equivalent of compare-and-swap, and it has the same character: no lock is held across a round trip, no retry is needed on success, and the loser learns from a return value (Compare-and-Swap: The Primitive Everything Is Built On).
The same primitive appears in every store that offers conditional or arithmetic writes: Redis INCR and SET NX, DynamoDB conditional expressions, MongoDB $inc with a filter. The shape transfers; the syntax does not.

Move the decision into the WHERE clause

DATABASE-SPECIFICRETURNING on UPDATE is Postgres, SQLite and MariaDB; MySQL requires a second SELECT, which is not atomic with the update and can therefore read a value another writer has since changed — usually acceptable for display, never for a subsequent decision.

The transformation is mechanical once you see it. Any check performed in application code between a read and a write can often be moved into the predicate of the write, at which point the window disappears — not because it is protected, but because it no longer exists.

What you give up is the intermediate value. The application no longer knows the stock level before the decrement, only whether the decrement was permitted. In practice that is almost always what the business rule needed anyway, and where it is not, RETURNING gives the post-state in the same round trip.

The pattern extends well past counters. State transitions — WHERE status = 'pending' — are the same shape, and they solve the "two workers picked up the same job" problem with no lock at all.

Four decisions moved into the statement

1-- 1. Conditional decrement: the check IS the predicate.
2UPDATE inventory SET stock = stock - $2
3 WHERE sku = $1 AND stock >= $2
4RETURNING stock;
5-- 0 rows -> insufficient stock. Never a negative value, ever.
6 
7-- 2. State transition: only one worker can win.
8UPDATE jobs SET state = 'running', worker = $2, started_at = now()
9 WHERE id = $1 AND state = 'pending'
10RETURNING id;
11-- 0 rows -> someone else claimed it. No lock, no wait.
12 
13-- 3. Create-or-update, atomically.
14INSERT INTO daily_counters (user_id, day, n)
15VALUES ($1, current_date, 1)
16ON CONFLICT (user_id, day) DO UPDATE SET n = daily_counters.n + 1
17RETURNING n;
18-- One round trip; no check-then-insert window.
19 
20-- 4. Belt and braces: the invariant holds for every code path,
21--    including the one someone writes next year without reading this.
22ALTER TABLE inventory ADD CONSTRAINT stock_non_negative CHECK (stock >= 0);

Each statement returns something the application must branch on. An atomic operation whose result is discarded provides no more safety than the read-check-write it replaced.

What atomicity covers, and what it does not

A statement is atomic. An operation made of two statements is not. This trips up implementations that correctly make the stock decrement atomic and then create the order row separately: a crash between the two leaves stock consumed with no order, and no error anywhere.

The composition rule is that statement atomicity gives you a race-free decision, and transaction atomicity gives you all-or-nothing across statements. You usually need both — the conditional update inside a transaction that also writes the order.

It is worth being precise about what each buys, because "atomic" is used loosely enough that people assume one implies the other. It does not: a transaction with two unconditional updates is all-or-nothing and still races; a conditional update outside a transaction is race-free and not durable alongside anything else.

Construct	Race-free decision?	All-or-nothing across writes?	What it is for
Read, check in app code, write	No	No	Nothing — this is the bug
Conditional `UPDATE ... WHERE cond`	Yes	Single statement only	Making one decision safely
Transaction of unconditional updates	No	Yes	Keeping several writes consistent
Transaction containing a conditional update	Yes	Yes	The usual correct combination
`SELECT ... FOR UPDATE` then write	Yes	Yes, within the transaction	When the decision needs app logic (Pessimistic Locking)
Version column + conditional update	Yes	Single statement only	Detecting concurrent edits (Optimistic Concurrency)
`CHECK` / `UNIQUE` constraint	Yes, absolutely	n/a	Invariants that must hold for every code path

When one row is the bottleneck

SCALE-SPECIFICThese options are ordered by when you should reach for them, not by quality. Sharding a counter that is not contended adds read cost and complexity for nothing; the trigger is measured lock wait on that specific row, not an expectation of growth.

An atomic update serialises access to the row it touches. That is the mechanism working correctly, and it means the row has a throughput ceiling: writers queue, and adding application instances does not help because the queue is in the database (Little's Law as Working Intuition).

For most rows this never matters, because writes are spread across users, orders and documents. It matters for genuinely shared aggregates: a global counter, a leaderboard total, the stock of one viral product, a per-tenant balance for a very large tenant.

The fixes are data model changes rather than statement changes, and they share a shape: stop having one row that everyone writes. Shard the counter across N rows and sum on read; or stop maintaining a total at all and append immutable entries, aggregating when someone asks. Both convert write contention into read cost, which is usually the better direction because reads can be cached, replicated and materialised.

The counter row is contended. What now?

How much write throughput do you need on this single value, and how fresh must reads be?

Leave it — one atomic update

when Contention is low; the serialisation is invisible.

cost None until it is the ceiling. Most rows are here.

Shard the counter

when High write rate, and reads can sum N rows.

cost Reads aggregate; the shard count is a tuning parameter you must revisit.

Append-only ledger, aggregate on read

when You need the history anyway: balances, points, audit.

cost Reads get more expensive; usually needs a materialised total refreshed on a schedule.

Batch in the application

when Approximate, high-volume counters: views, impressions.

cost In-flight counts are lost on a crash; the value is behind by the flush interval.

Move to a store built for it

when A pure counter with no transactional relationship to your rows.

cost A second store, and no shared transaction with the database (The Dual Write Problem).

How to build it

Most important first.

Ask first whether the decision can be expressed as a predicate. If it can, put it in the WHERE clause and delete the application-level check (Finding the Critical Section).
Express arithmetic relative to the column, never from a value read earlier. SET n = n - 1 is atomic; SET n = $2 with $2 computed in application code is not.
Always branch on rows affected. An atomic conditional update whose result is ignored is a silent failure.
Use RETURNING where the database supports it, to get the new value in the same round trip and avoid a follow-up read that would itself be racy.
Prefer a constraint as a second line of defence: CHECK (stock >= 0) makes the invariant true regardless of which code path writes (Database Constraints).
For counters under extreme contention, stop updating one row: append to a ledger and aggregate, or shard the counter across N rows and sum them (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
Use upserts — INSERT ... ON CONFLICT DO UPDATE — to make create-or-update atomic instead of check-then-insert.

What can go wrong

Failure modes

Ignoring the affected-row count, so a rejected decrement looks like a successful one and the order proceeds with no stock.
Computing the new value in application code and writing it as a literal, which reintroduces the read-modify-write the statement was meant to remove.
An upsert without a WHERE guard on its update branch, so a stale writer overwrites newer data (Webhook Retries and Ordering).
Atomic on one row and not across the operation: the stock decrements atomically, the order row is created separately, and a crash between them leaves stock reserved for an order that does not exist (Where the Transaction Boundary Goes).
A single hot row becoming the throughput ceiling, because atomic updates to one row still serialise on that row (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
Assuming multiple atomic statements compose into an atomic operation. They do not; only a transaction gives that, and only with appropriate isolation.
Redis INCR used for a limit check where the increment succeeds and the subsequent check fails, leaving the counter incremented for a rejected request (Rate Limit Algorithms).

What can race

The read-modify-write this mechanism eliminates — the reason it exists.
Two atomic statements in sequence, with a window between them that the atomicity of each does not cover (Where the Transaction Boundary Goes).
An upsert without a version guard racing another upsert, so the later-arriving-but-older write wins.
A hot row serialising writers, which is not a correctness race but is the throughput consequence of removing one.
Constraint violation as a race outcome: two inserts, one succeeds, one raises — which is the correct behaviour and must be handled as a branch rather than as an error (Duplicate Detection).

Security

Business invariants enforced in application code are bypassable by concurrency. CHECK constraints and unique indexes hold against every code path and every race (Database Constraints).
Limit checks — credits, quotas, redemptions — are prime targets for parallel submission. Expressing them as conditional updates removes the window an attacker is aiming at.
Never let user input reach the arithmetic side unvalidated: SET balance = balance + $2 with an unvalidated negative $2 is a withdrawal disguised as a deposit (Business Validation).

Misreads

"A transaction makes my sequence atomic." A transaction makes it all-or-nothing. It does not make it isolated from concurrent readers and writers unless the isolation level or an explicit lock provides that (Isolation Levels).
"Atomic means fast." It means indivisible. An atomic update to a contended row is a serialisation point and can be the slowest thing in the system.
"Two atomic statements are atomic together." They are two operations with a window between them.
"INCR in Redis solves rate limiting." It solves the increment. The limit check, the window reset and the failure path each need their own thought (Rate Limit Algorithms).
"The database will stop the value going negative." Only if a constraint says so. An UPDATE will happily write -1 unless the predicate or a CHECK prevents it.

Operating it

How you see it in production

Count conditional updates that affect zero rows, by statement. For a stock decrement that is your out-of-stock rate; for a state transition it is your conflict rate.
Watch for invariant violations directly — SELECT count(*) FROM stock WHERE n < 0 — because that query is the ground truth and a constraint would make it impossible.
Row-level lock waits on the hot row, which is what tells you an atomic update has become a throughput ceiling (Low CPU, High Latency: Lock Contention).
Latency of the single statement under load: it should be flat until contention on the row starts queueing, and the inflection point is your capacity for that row.

What changes at 10x and 100x

Atomic updates on distinct rows scale essentially linearly; the contention is per row, so a well-distributed key space has no ceiling in this mechanism.
A single hot row does not scale, and no amount of instances changes it: writers to one row are serialised by the database (Little's Law as Working Intuition).
At 100x on a hot counter, the answer is a data model change — sharded counters summed on read, or an append-only ledger aggregated periodically. Both trade read cost for write throughput.
Append-only designs scale writes best and move the cost to reads, which can then be materialised on a schedule (Read Replicas From the Application).

What this costs

The logic must be expressible in SQL. Anything requiring branching, external data or complex validation cannot move into the WHERE clause, and then you need a version or a lock.
The application never sees the intermediate state, which is exactly the point and makes some error messages less specific: you know the update did not apply, not which condition failed, unless you go and look.
Sharded counters give write throughput and make reads more expensive and eventually-consistent-looking.
Pushing invariants into constraints makes them robust and makes schema changes heavier, since the constraint must hold for existing data.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALEvery store that offers a conditional or arithmetic write offers this primitive in some form.
DATABASE-SPECIFICPostgres supports RETURNING on UPDATE and INSERT, and ON CONFLICT DO UPDATE ... WHERE for a guarded upsert. MySQL has neither: no RETURNING on UPDATE, and ON DUPLICATE KEY UPDATE takes no WHERE, so a guard must be written as a conditional expression per column. MySQL also reports changed rather than matched rows by default, so branching on affected rows behaves differently.
SCALE-SPECIFICSingle-row atomic updates are the right answer until one row becomes hot enough that serialised access is the bottleneck. Past that point the answer is not a better statement but a different data model — sharding or appending — and where that point lies depends on transaction duration and write rate.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databaseconcurrency-anomalies isolation-levels update-delete-under-mvcc transactions-and-acid

Performancehot-keys lock-contention littles-law

Concurrencyatomics compare-and-swap atomicity-illusion atomics-are-not-magic finding-the-critical-section contention-costs

Domains that do not exist yet

— Programming Languages & Runtime Internals — hardware compare-and-swap as the same idea one layer down, and why "atomic" means indivisible rather than fast.

OS & Networkingatomic-operations