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.

  1. 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.
  2. 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;
  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.
  2. 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.
  3. 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

Where It Breaks Down

When This Is Overkill

Most counters never need this. Work through the cheaper options in order:

  1. A plain UPDATE ... SET n = n + 1 in its own short transaction is correct up to a few hundred increments per second per row.
  2. Write-behind buffering handles bursts when exactness within seconds is fine. Accumulate deltas in memory or Redis INCRBY and flush n = n + delta every second. One row write per second absorbs any traffic.
  3. 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.

  1. Phantom sell-outs. A purchase lands on an empty shard while other shards still have stock, so it fails even though seats remain.
  2. 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.

  1. Pre-split the allocation. On creation, write 32 rows that each hold their own share of capacity. Each row's remaining becomes that shard's private budget, not a slice of a shared number.
  2. 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.
  3. 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.