Large-Scale Concurrency Control: Solving the DB Bottleneck from a Flood of “Likes”
CK 3788 Created at Updated at
26900
1. The Problem: Hotspots Created by Viral Posts
A service that ran perfectly fine on most days would slow to a crawl — and sometimes the database would freeze entirely — the moment traffic converged on a single influencer’s post or an event-driven piece of content. The root cause was write contention on a single row.
When the likes count is updated directly on a column of the post’s row, every single “like” request for that post lines up for the same row lock. As concurrent requests grow, lock wait times climb, the connection pool drains, and eventually even unrelated requests get dragged down.
Imagine it’s a quiet Tuesday afternoon. Our system is humming along perfectly, and the servers are running cool.
Suddenly, an influencer with five million followers hits "Publish" on our platform.
Within seconds, thousands of people open the app, read the post, and start smashing that "Like" button. It looks like a massive win for user engagement, right? But down in the engine room, a disaster is brewing.
The Problematic Architecture
Bottleneck Summary
Symptom
Root Cause
Spiking response latency
Serialized write locks on the same row
Connection pool exhaustion
Connections held hostage by lock waits
Cascading failure
One hot post’s load spreads across the whole DB
Write ceiling
Single-row update throughput becomes the system limit
2. The Strategy: Eliminate Direct Writes — Distribute and Decouple Them
The solution rests on three core ideas.
Don’t write to the DB immediately — accept requests first in a fast in-memory store (Redis).
Batch the writes — instead of applying each request one by one, aggregate them and apply them periodically in bulk.
Persist asynchronously — decouple DB writes from the user’s response path via a message queue.
The improved architecture is described below in three modes.
The whole flow at a glance. For non-technical stakeholders or a high-level overview.
One-line summary: A fast store accepts user requests and responds instantly, while the actual DB updates are batched and applied behind the scenes.
4. Improved Architecture — Mode 2: Reference View
Structurally represents each component’s role and the data flow. For design reviews and documentation.
PRO
Responsibilities by Layer
Layer
Role
Application
Stateless API servers; absorb traffic via horizontal scaling
Fast Write
Redis absorbs writes instantly; atomic counting via INCR
Async
Queue + worker decouple DB writes into async, batched operations
Persistence
The DB receives only aggregated results, not individual requests
5. Improved Architecture — Mode 3: Technical View
Detailed from an implementation standpoint: key design, atomic operations, idempotency, and batch flush. For engineers building it.
PRO
Implementation Notes
IdempotencySADD’s return value prevents duplicate “likes.” If a user has already liked the post, it returns 0 and the count does not increment — so retries from network failures or double-clicks are safe.
Atomicity Redis operations like INCR/SADD are atomic by virtue of its single-threaded execution model, so no locks are needed. The RDBMS row-lock contention itself disappears.
Eliminating Write Amplification (Batching) Instead of hitting the DB on every like, only changed posts are tracked in the dirty:posts set and flushed in bulk every N seconds. 100,000 likes collapse into a single +100000 update.
Load Isolation The DB write path is fully separated from the user’s response path, so a hot post’s load no longer propagates to other requests.
6. Before vs. After
Aspect
Before (Direct Write)
After (Redis + Batching)
Write contention
Serialized single-row locks
Lock-free atomic INCR
DB write count
1 per like
1 per N seconds (aggregated)
User response
Bound to lock waits
Instant response (lower p99)
Duplicate handling
Requires separate lookup
Auto-idempotent via SADD
Failure isolation
Load propagates
Write path is isolated
Scalability
DB limit = system limit
Horizontally scalable
7. Trade-offs — Nothing Is Free
This design embraces eventual consistency. Right after a user hits “like,” it may not be instantly reflected on another user’s screen (typically within a few seconds). There’s also a risk of data loss before a flush if Redis fails, so safeguards like Redis persistence (AOF), early publishing to the queue, or a reconciliation batch are necessary.
In short, the core is a trade-off where we give up a little “real-time accuracy” to gain a great deal of “write throughput and stability.” It fits especially well for counters that tolerate slight delays, such as likes, view counts, and reaction totals.
This post outlines general large-scale concurrency-control patterns. Real-world application requires tuning to your specific traffic characteristics, consistency requirements, and infrastructure environment.
