A counter looks like the simplest record in a database: one number you need to bump. Yet counters are exactly where correctness breaks under concurrency, because the naive code reads the value, adds one, and writes it back in three separate steps. The database can do all three atomically in a single statement.
The lost-update bug
Take a table of view counters keyed by user:
CREATE TABLE counters (
id bigint PRIMARY KEY,
n bigint NOT NULL DEFAULT 0
);
Typical application code is a read-modify-write:
SELECT n FROM counters WHERE id = 1;
UPDATE counters SET n = 42 WHERE id = 1;
Suppose two processes both read 41. Both add one and both write 42. Two increments collapse into one — that is the lost update. Under load the counter steadily falls behind, and no try/catch catches it: there is no error, just silently dropped increments.
One statement instead of three
The fix is to push the read, the arithmetic, and the write into the UPDATE itself:
UPDATE counters SET n = n + 1 WHERE id = 1;
Here n + 1 is evaluated against the row's current value at write time, not against whatever the app read a second ago. PostgreSQL takes a row lock: a second concurrent UPDATE waits for the first to commit, then adds to the already-updated value. Two statements give +2, as they should. Atomicity is a property of the single statement, not of your discipline.
The same trick works for any running total, such as a customer's order sum:
UPDATE users
SET orders_total = orders_total + (
SELECT amount FROM orders WHERE id = 5001
)
WHERE id = 42;
Upsert: create or increment
Often the counter row does not exist yet — the first event should create it, later ones should bump it. INSERT ... ON CONFLICT handles both cases in one statement:
INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1;
- If the row is missing, it is inserted with
1.
- If it exists, the
DO UPDATE branch fires, where counters.n refers to the current value in the table.
- Gotcha: do not write
SET n = EXCLUDED.n — EXCLUDED is the rejected insert (1), so the counter would be stuck at one forever. You want counters.n + 1.
This upsert is atomic and race-safe too: concurrent inserts of the same id serialize on the unique index.
Return the new value with RETURNING
After incrementing you often need the result — say, the new position in a queue. A separate SELECT would reopen the race window. RETURNING hands the value back from the same atomic statement:
UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;
You get the value your own transaction assigned, with no re-read. It works with the upsert too:
INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1
RETURNING n;
Hotspots and limits
Atomicity fixes correctness, but it does not magically remove contention. If every request hits the same row, that row becomes a hotspot: transactions queue up behind the row lock, and throughput is capped by a single write.
- Gotcha: a long transaction that bumps the counter and then does more work holds the row lock until
COMMIT. Increment as late as possible and commit fast.
- For very hot counters, shard them: N partial rows (
id, shard, n), write to a random shard, read with SUM(n). This spreads contention across several rows.
- If you only need a monotonically increasing unique number rather than an exact count, a
SEQUENCE (or GENERATED AS IDENTITY) is cheaper: it does not block and does not roll back, but it allows gaps in the numbering.
Engine differences: in MySQL/InnoDB, UPDATE ... SET n = n + 1 is atomic the same way, and the upsert is written as INSERT ... ON DUPLICATE KEY UPDATE n = n + 1; there is no RETURNING, so people use LAST_INSERT_ID() tricks or a re-SELECT in the same transaction. ClickHouse is an analytical engine: point UPDATEs are expensive there, so counters are usually built with SummingMergeTree or AggregatingMergeTree, where values are added up when parts merge instead of per row.
The takeaway is simple: never compute the increment in the application. Let the database do the read, the add, and the write in one n = n + 1 expression — then the counter stays correct under any concurrency.
A counter looks like the simplest record in a database: one number you need to bump. Yet counters are exactly where correctness breaks under concurrency, because the naive code reads the value, adds one, and writes it back in three separate steps. The database can do all three atomically in a single statement.
The lost-update bug
Take a table of view counters keyed by user:
CREATE TABLE counters ( id bigint PRIMARY KEY, n bigint NOT NULL DEFAULT 0 );Typical application code is a read-modify-write:
-- step 1: read SELECT n FROM counters WHERE id = 1; -- got 41 -- step 2: app adds 1 in memory -> 42 -- step 3: write back UPDATE counters SET n = 42 WHERE id = 1;Suppose two processes both read
41. Both add one and both write42. Two increments collapse into one — that is the lost update. Under load the counter steadily falls behind, and notry/catchcatches it: there is no error, just silently dropped increments.One statement instead of three
The fix is to push the read, the arithmetic, and the write into the
UPDATEitself:UPDATE counters SET n = n + 1 WHERE id = 1;Here
n + 1is evaluated against the row's current value at write time, not against whatever the app read a second ago. PostgreSQL takes a row lock: a second concurrentUPDATEwaits for the first to commit, then adds to the already-updated value. Two statements give+2, as they should. Atomicity is a property of the single statement, not of your discipline.The same trick works for any running total, such as a customer's order sum:
UPDATE users SET orders_total = orders_total + ( SELECT amount FROM orders WHERE id = 5001 ) WHERE id = 42;Upsert: create or increment
Often the counter row does not exist yet — the first event should create it, later ones should bump it.
INSERT ... ON CONFLICThandles both cases in one statement:INSERT INTO counters (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = counters.n + 1;1.DO UPDATEbranch fires, wherecounters.nrefers to the current value in the table.SET n = EXCLUDED.n—EXCLUDEDis the rejected insert (1), so the counter would be stuck at one forever. You wantcounters.n + 1.This upsert is atomic and race-safe too: concurrent inserts of the same
idserialize on the unique index.Return the new value with RETURNING
After incrementing you often need the result — say, the new position in a queue. A separate
SELECTwould reopen the race window.RETURNINGhands the value back from the same atomic statement:UPDATE counters SET n = n + 1 WHERE id = 1 RETURNING n;You get the value your own transaction assigned, with no re-read. It works with the upsert too:
INSERT INTO counters (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = counters.n + 1 RETURNING n;Hotspots and limits
Atomicity fixes correctness, but it does not magically remove contention. If every request hits the same row, that row becomes a hotspot: transactions queue up behind the row lock, and throughput is capped by a single write.
COMMIT. Increment as late as possible and commit fast.id, shard, n), write to a random shard, read withSUM(n). This spreads contention across several rows.SEQUENCE(orGENERATED AS IDENTITY) is cheaper: it does not block and does not roll back, but it allows gaps in the numbering.Engine differences: in MySQL/InnoDB,
UPDATE ... SET n = n + 1is atomic the same way, and the upsert is written asINSERT ... ON DUPLICATE KEY UPDATE n = n + 1; there is noRETURNING, so people useLAST_INSERT_ID()tricks or a re-SELECTin the same transaction. ClickHouse is an analytical engine: pointUPDATEs are expensive there, so counters are usually built withSummingMergeTreeorAggregatingMergeTree, where values are added up when parts merge instead of per row.The takeaway is simple: never compute the increment in the application. Let the database do the read, the add, and the write in one
n = n + 1expression — then the counter stays correct under any concurrency.