Definition
- Hot row – A single database row that many concurrent requests try to update, creating lock contention that serializes all writers.
- Counter sharding – Split one logical counter into N physical sub-counter rows; writers spread across them, readers SUM all N.
- Buffering – Accumulate increments in a fast store (Redis INCR) and periodically flush the aggregate to the database.
- Append-only ledger – For real money, model each change as an appended entry and compute the balance, avoiding the hot row entirely.
- Trade-offs – Sharding keeps increments durable; buffering trades staleness for throughput.
Imagine a single donation box at National Bank of India where thousands of customers must drop coins one at a time, so everyone queues. Counter sharding puts out twenty donation boxes so twenty people donate at once, and at the end you add up all twenty. Buffering lets a fast assistant collect everyone's coins in a bucket and tip the whole bucket into the main box once a minute.
Understanding the Concepts
Why does a hot row hurt performance?
Every UPDATE takes a row-level lock. Thousands of concurrent increments queue on that one lock, running one at a time. The database is not CPU-bound; it is lock-bound.
package com.bank.hotrow;
public class HotRowProblem {
public static void main(String[] args) {
System.out.println("Hot row problem:");
System.out.println(" 1000 concurrent UPDATEs on one counter row");
System.out.println(" Each waits for the row lock -> serialized");
System.out.println(" Throughput: ~100 updates/sec (lock contention)");
System.out.println(" Database CPU: 10% (not the bottleneck!)");
System.out.println(" The LOCK is the bottleneck");
}
}
Expected output:
Hot row problem:
1000 concurrent UPDATEs on one counter row
Each waits for the row lock -> serialized
Throughput: ~100 updates/sec (lock contention)
Database CPU: 10% (not the bottleneck!)
The LOCK is the bottleneck
How does counter sharding work?
Split one counter into N sub-rows. Each writer picks a shard at random. Read is SUM of all shards.
package com.bank.hotrow;
import java.util.concurrent.ThreadLocalRandom;
public class CounterSharding {
private static final int NUM_SHARDS = 16;
private final int[] shards = new int[NUM_SHARDS];
public void increment() {
int shard = ThreadLocalRandom.current().nextInt(NUM_SHARDS);
shards[shard]++;
}
public int getTotal() {
int total = 0;
for (int s : shards) total += s;
return total;
}
public static void main(String[] args) {
CounterSharding counter = new CounterSharding();
for (int i = 0; i < 1000; i++) counter.increment();
System.out.println("Total across 16 shards: " + counter.getTotal());
}
}
-- DB counter sharding
CREATE TABLE counter_shards (
counter_id VARCHAR(50),
shard_index INT,
value BIGINT DEFAULT 0,
PRIMARY KEY (counter_id, shard_index)
);
-- Write: pick random shard
UPDATE counter_shards SET value = value + 1
WHERE counter_id = 'fd-opened-today' AND shard_index = 7;
-- Read: sum all shards
SELECT SUM(value) FROM counter_shards WHERE counter_id = 'fd-opened-today';
Expected output:
Total across 16 shards: 1000
With 16 shards: 16 writers proceed in parallel
Contention reduced ~16x
Read: SUM query over 16 rows
How does Redis buffering work?
Accumulate increments in Redis with atomic INCR. A background job flushes the aggregate to the DB.
package com.bank.hotrow;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
@Component
public class RedisBufferedCounter {
private final StringRedisTemplate redis;
private final CounterRepository dbRepo;
private static final String KEY = "counter:fd-opened-today";
public RedisBufferedCounter(StringRedisTemplate redis, CounterRepository dbRepo) {
this.redis = redis;
this.dbRepo = dbRepo;
}
public void increment() {
redis.opsForValue().increment(KEY);
// Atomic INCR: handles thousands/sec on single key
}
@Scheduled(fixedRate = 5000) // every 5 seconds
public void flushToDb() {
String value = redis.opsForValue().getAndSet(KEY, "0");
if (value != null && !"0".equals(value)) {
long delta = Long.parseLong(value);
dbRepo.incrementBy("fd-opened-today", delta);
System.out.println("Flushed " + delta + " to database");
}
}
}
Expected output:
Flushed 5432 to database
10,000 increments/sec -> 1 DB write every 5 seconds
DB contention: nearly eliminated
Trade-off: DB stale between flushes (serve reads from Redis)
How do sharding and buffering compare?
| Aspect | Counter Sharding | Redis Buffering |
|---|---|---|
| Durability | Every increment durable in DB | Un-flushed delta at risk |
| Read freshness | Eventually consistent | Fresh from Redis, stale in DB |
| Max throughput | Limited by N shards | Nearly unlimited (Redis single-thread) |
| Best for | Durable counters | Approximate counters (likes, views) |
package com.bank.hotrow;
public class ShardingVsBuffering {
public static void main(String[] args) {
System.out.println("Counter sharding: every increment in DB, less contention");
System.out.println("Redis buffering: extreme throughput, DB stale between flushes");
System.out.println();
System.out.println("Composable: sharded Redis counters flushed to DB");
}
}
Expected output:
Counter sharding: every increment in DB, less contention
Redis buffering: extreme throughput, DB stale between flushes
Composable: sharded Redis counters flushed to DB
What about real money? Avoid the hot row entirely.
For account balances, model each change as an appended ledger entry. The balance is the SUM. No hot row.
package com.bank.hotrow;
import java.math.BigDecimal;
public class AppendOnlyLedger {
// WRONG: single balance row updated on every transaction
// UPDATE accounts SET balance = balance - 50000 WHERE id = 'SAV2001'
// -> HOT ROW under load
// CORRECT: append-only ledger entries
// INSERT INTO ledger (account_id, amount, type, description)
// VALUES ('SAV2001', -50000, 'DEBIT', 'Transfer to Priya')
// Balance = SUM(amount) WHERE account_id = 'SAV2001'
public static void main(String[] args) {
System.out.println("For real money: NO hot shared counter");
System.out.println(" Model: append-only ledger entries");
System.out.println(" Balance = SUM of all entries for the account");
System.out.println(" No single row updated by every transaction");
System.out.println(" Maintains full ACID and audit trail");
}
}
Expected output:
For real money: NO hot shared counter
Model: append-only ledger entries
Balance = SUM of all entries for the account
No single row updated by every transaction
Maintains full ACID and audit trail
What are quick wins without sharding?
Keep transactions tiny so the lock is held briefly. Use atomic increment.
-- Keep the transaction minimal: just the increment
BEGIN;
UPDATE counter SET value = value + 1 WHERE id = 'fd-opened-today';
COMMIT;
-- Lock held for ~1ms instead of 100ms with business logic in the transaction
package com.bank.hotrow;
public class QuickWins {
public static void main(String[] args) {
System.out.println("Quick wins before sharding:");
System.out.println(" 1. Atomic increment (no SELECT + UPDATE, just UPDATE +1)");
System.out.println(" 2. Minimal transaction (only the counter in the TX)");
System.out.println(" 3. Batch increments at app layer (batch 100 into 1 update)");
System.out.println(" 4. If these are not enough: shard or buffer");
}
}
Expected output:
Quick wins before sharding:
1. Atomic increment (no SELECT + UPDATE, just UPDATE +1)
2. Minimal transaction (only the counter in the TX)
3. Batch increments at app layer (batch 100 into 1 update)
4. If these are not enough: shard or buffer
What should a senior engineer know?
For real money, avoid the hot row entirely with an append-only ledger. For approximate counters, shard or buffer. They compose.
Senior guidelines:
[x] Real money: append-only ledger, NOT a shared counter
[x] Durable counter: shard into N sub-rows
[x] Approximate counter: buffer in Redis, flush periodically
[x] Composable: sharded Redis counters flushed to DB
[x] Quick win: atomic increment in minimal transaction
[x] Monitor: row lock wait time per hot table
What are common mistakes?
The most common mistake is using SELECT then UPDATE instead of atomic increment, which doubles the lock time and introduces a race condition. Another is sharding a counter for real money instead of using an append-only ledger.
package com.bank.hotrow;
public class HotRowMistakes {
public static void main(String[] args) {
// WRONG: SELECT + UPDATE (two operations, race condition)
System.out.println("WRONG: SELECT balance, then UPDATE balance = balance - amount");
System.out.println(" Two transactions read 100000, both set to 50000");
System.out.println(" Lost update: 50000 lost!");
// CORRECT: atomic UPDATE
System.out.println("CORRECT: UPDATE accounts SET balance = balance - 50000");
System.out.println(" Single atomic operation, no lost update");
// WRONG: sharded counter for real money
System.out.println("WRONG: sharded counter for account balance");
System.out.println(" Partial reads, no ACID guarantee across shards");
// CORRECT: append-only ledger for money
System.out.println("CORRECT: append-only ledger entries for money");
System.out.println(" Full audit trail, SUM for balance, no hot row");
}
}
Expected output:
WRONG: SELECT balance, then UPDATE balance = balance - amount
Two transactions read 100000, both set to 50000
Lost update: 50000 lost!
CORRECT: UPDATE accounts SET balance = balance - 50000
Single atomic operation, no lost update
WRONG: sharded counter for account balance
Partial reads, no ACID guarantee across shards
CORRECT: append-only ledger entries for money
Full audit trail, SUM for balance, no hot row
Visual Overview
Hot Row Solutions: Problem: ┌──────────────────────────────────┐ │ 1000 concurrent UPDATEs │ │ │ │ │ │ │ │ │ │ │ ▼ ▼ ▼ ▼ ▼ ▼ ▼ │ │ ┌──────────────────────────┐ │ │ │ ONE ROW (row lock) │ │ │ │ Serialized: 1 at a time │ │ │ └──────────────────────────┘ │ └──────────────────────────────────┘ Solution 1: Counter Sharding ┌──────────────────────────────────┐ │ Writers spread across N shards │ │ ▼ ▼ ▼ ▼ │ │ [S0] [S1] [S2] [S3] │ │ Read: SUM(S0 + S1 + S2 + S3) │ └──────────────────────────────────┘ Solution 2: Redis Buffering ┌──────────────────────────────────┐ │ 1000/sec -> Redis INCR │ │ Every 5s -> flush aggregate to DB│ │ DB: 1 write every 5s (not 1000) │ └──────────────────────────────────┘
Key Takeaway
A hot row serializes all writers on one row-level lock, collapsing throughput even though the database has plenty of CPU. The two main remedies are counter sharding, which splits one counter into N sub-rows so writers spread across N locks and the true value is their SUM, and Redis buffering, which accumulates increments with atomic INCR and flushes the aggregate periodically.
Sharding keeps every increment durable in the database and is the right choice when the source of truth must stay there. Buffering trades staleness for extreme throughput and suits approximate counters like likes or views. The two compose: sharded Redis counters flushed to the database.
The most important lesson for banking is that for real money, you should not use a hot shared counter at all. At National Bank of India, Pankaj models each balance change as an append-only ledger entry and computes the balance as a SUM, which removes the hot row by design while maintaining full ACID guarantees and an audit trail.
Remember one sentence: a hot row serializes on one lock, so either shard the counter into N sub-rows, buffer in Redis and flush periodically, or for real money avoid the hot row entirely with an append-only ledger aggregate.
---