Hot Row Lock Contention on Counter Updates: Sharded Counters
Databases · Intermediate · 7 min read · published
This article was written by Claude (Anthropic) and published automatically. Published by Osama Fayyaz.
What this solves: A single likes or views counter row serializes every write, so latency climbs with traffic while CPU stays idle. Here's how sharded counters spread that lock.
The Forces at Play
Hot row lock contention on counter updates shows up when a single likes_count, view_count or balance row receives hundreds of concurrent increments. Every one of them queues behind the previous writer's row lock. Throughput collapses and p99 climbs with traffic, yet CPU and disk look bored. The database isn't slow; it is serializing you.
The tension is simple. The product wants one exact number, read cheaply. The database guarantees one writer per row at a time, and the lock is held until commit, not until the statement finishes. So the hold time is the increment plus whatever else your transaction does afterwards, such as an HTTP call to send a notification or writing an audit row.
At 5 ms of hold time, one row tops out around 200 increments per second, no matter how big the machine is. A viral post, a flash sale or a global rate counter goes straight past that.
You'll recognise it in pg_stat_activity. Many sessions show wait_event_type = Lock with wait_event = transactionid or tuple, all running the same UPDATE posts SET likes = likes + 1 WHERE id = $1. On MySQL, innodb_row_lock_waits spikes while SHOW ENGINE INNODB STATUS lists one hot primary key.
Free fix first. Do the increment in its own short transaction, or as the last statement before commit. That often cuts hold time by 10x. When it isn't enough, you need to stop having one row.
The Shape
The sharded counter replaces one logical row with N physical rows. Writers pick a shard at random, so they collide only 1/N as often. Readers sum the shards. An optional rollup keeps reads cheap.
flowchart LR
subgraph Writers
W1[Request A]
W2[Request B]
W3[Request C]
end
W1 -->|shard = rand 0..N-1 -> 3| S3
W2 -->|-> 11| S11
W3 -->|-> 3, waits only on A| S3
subgraph counter_shards for post 42
S0[(shard 0: 812)]
S3[(shard 3: 790)]
S11[(shard 11: 805)]
SN[(... shard N-1)]
end
S0 & S3 & S11 & SN -->|SUM on read| R[Read path]
R --> C[(Cache / denormalized total, TTL seconds)]
RU[Rollup worker] -->|periodic SUM| S0 & S3 & S11 & SN
RU -->|write total| T[(posts.likes_count, read-mostly)]
C --> API[API response]
T --> API
The key property is that no write path ever touches posts.likes_count. Only the rollup writes it, and only one rollup runs, so that row has a single writer and zero contention.
How Data Flows Through It
Here is one like on post 42, end to end.
- Choose a shard. The API handler picks
shard = random_int(0, 15). The choice must vary per write. A random number, a connection id or a worker id all work.hash(post_id)does not. - Upsert the shard row. The handler runs an upsert so shards are created lazily:
CREATE TABLE counter_shards (
counter_id bigint NOT NULL,
shard smallint NOT NULL,
count bigint NOT NULL DEFAULT 0,
PRIMARY KEY (counter_id, shard)
);
INSERT INTO counter_shards (counter_id, shard, count)
VALUES ($1, $2, 1)
ON CONFLICT (counter_id, shard)
DO UPDATE SET count = counter_shards.count + 1;
- Commit in a tiny transaction. The lock is held on one of 16 rows for about a millisecond. Two concurrent likes now collide only 1 time in 16.
- Read when someone views the post. The read path first checks the cache. On a miss it runs
SELECT sum(count) FROM counter_shards WHERE counter_id = 42. That is a 16-row index range scan, which is cheap. The result is cached for a few seconds. - Roll up for list pages. Every 30 s the rollup worker folds hot counters into
posts.likes_count. List pages showing 50 posts read that denormalized column instead of fanning out 50 × 16 rows.
What Each Piece Owns
- Shard picker (application code)
- Owns: spreading writes evenly.
- Does not own: correctness of the total. Any distribution gives the right sum; a bad one just gives less concurrency.
- Shard rows
- Owns: the authoritative partial counts. The truth is
SUM(shards), always. - Does not own: invariants across shards. No shard can tell whether the total is under a limit.
- Owns: the authoritative partial counts. The truth is
- Read path and cache
- Owns: turning N rows into one number with bounded staleness.
- Does not own: write-through updates. Incrementing the cache on every write re-creates the hot key, just in Redis.
- Rollup worker
- Owns: the denormalized total used for sorting and listing.
- Does not own: exactness. That column is eventually correct and should never be read back to make decisions.
- Shard count N
- Owns: the trade between write concurrency and read cost.
- Does not own: growing automatically. Pick it per counter class, e.g. 1 for normal posts and 32 for known-hot ones.
Where It Breaks Down
- Hard limits break first. Inventory, seats, quotas and balances need
total >= 0, and no single row can enforce that. "Read the sum, then decrement" races and oversells.- Fix: give each shard its own pre-allocated budget, guard each decrement with
WHERE count > 0, and retry on another shard when one runs dry.
- Fix: give each shard its own pre-allocated budget, guard each decrement with
- Read amplification. Leaderboards and "sort by likes" over thousands of counters can't SUM shards per row. You must have the rollup column, and now you are operating a background job whose lag is user-visible.
- Changing N. Growing N is easy: new shards just start at 0. Shrinking requires folding shards together inside a transaction. If shard selection ever depends on N, deploys mid-change can skew distribution.
- Bloat in MVCC databases. Every increment in Postgres writes a new tuple version. Hot shard rows generate dead tuples quickly, and long-running transactions stop vacuum from reclaiming them.
- Keep the shard table narrow and unindexed beyond its PK so updates stay HOT.
- Consider
fillfactor = 70on the shard table. - Watch
n_dead_tupon the table.
- Partial failure. If the rollup worker dies, list pages freeze at stale totals while detail pages, which read the live SUM, keep moving. Users notice the mismatch.
- Alert on rollup lag, not just on worker liveness.
When This Is Overkill
Most counters never need this. Work through the cheaper options in order:
- A plain
UPDATE ... SET n = n + 1in its own short transaction is correct up to a few hundred increments per second per row. - Write-behind buffering handles bursts when exactness within seconds is fine. Accumulate deltas in memory or Redis
INCRBYand flushn = n + deltaevery second. One row write per second absorbs any traffic. - Append-only events fit analytics-style counters. Insert a row per like (no contention, since inserts don't fight over a row) and aggregate asynchronously.
The signal that you've outgrown the plain row: lock waits on one specific key appear in pg_stat_activity during peaks, increment latency scales with concurrency rather than load, and shortening the transaction didn't fix it. Even then, reach for sharding only for counters that are both hot and must be durable and exact on read. Otherwise buffering is simpler and wins.
Key takeaway: When one row takes every increment, split it into N randomly chosen shard rows and sum on read. Never shard a counter that has to enforce a hard limit like stock or seats unless each shard gets its own budget.
Real-world challenge
A ticketing service sharded its `seats_remaining` counter into 32 rows to survive on-sale spikes. Each purchase picks a random shard and runs `UPDATE ... SET remaining = remaining - 1 WHERE remaining > 0`. If that matches zero rows, it reads the SUM across all shards and fails if the total is at most 0. Contention is gone, but finance reports 40 oversold tickets per big event. Purchases also sometimes fail while the dashboard still shows seats available.
Diagnosis. The global invariant is total ≥ 0, and no single row enforces it any more. The fallback has two problems.
- Phantom sell-outs. A purchase lands on an empty shard while other shards still have stock, so it fails even though seats remain.
- Oversells. Some code paths on that fallback read the SUM and then decrement a different shard without a guard, so two buyers can both see the last seat. The 40 extra tickets are the classic check-then-act race.
Fix. Make every shard a self-contained budget, so the per-row guard is the global invariant.
- Pre-split the allocation. On creation, write 32 rows that each hold their own share of capacity. Each row's
remainingbecomes that shard's private budget, not a slice of a shared number. - Retry on a different shard. When a guarded decrement matches 0 rows, move to another random shard. Give up only after trying all of them, or after a query that finds
remaining > 0. - Never decrement without the guard. Delete the SUM-then-decrement code path entirely.
-- attempt; retry with another shard if 0 rows updated
UPDATE seat_shards SET remaining = remaining - 1
WHERE event_id = $1 AND shard = $2 AND remaining > 0;
-- when retries keep failing, find any non-empty shard
SELECT shard FROM seat_shards
WHERE event_id = $1 AND remaining > 0
LIMIT 1 FOR UPDATE SKIP LOCKED;
Now overselling is impossible, because no row ever goes below 0 and the shard budgets add up to capacity. Phantom sell-outs only happen in the last handful of seats, and the fallback query handles those.
The lesson is that sharded counters are safe for additive stats but dangerous for limits unless each shard owns a budget.