Rows everything updates.
WHAT THE PROBLEM IS
A single row updated by every transaction becomes a bottleneck.
WHAT EXAMPLES LOOK LIKE
A total on a summary table A sequence number maintained manually A stock quantity on a popular item
WHAT HAPPENS UNDER LOAD
Transactions queue on the lock, and throughput collapses.
WHAT THE SYMPTOM IS
Lock waits concentrated on one table.
WHAT THE APPROACHES ARE
Counting rows when needed, rather than maintaining a total Updating the counter periodically rather than per transaction Splitting the counter into several rows and summing them Using an atomic operation in a cache store
WHAT SPLITTING ACHIEVES
Contention spread across rows.
WHAT IT COSTS
Reads must sum them.
WHAT PERIODIC AGGREGATION ACHIEVES
Removing the contention entirely.
WHAT IT COSTS
The total lags reality.
WHAT TO ASK
Whether it must be exact and immediate.
WHAT THE ANSWER FREQUENTLY IS
No.
WHAT MUST BE EXACT
Anything affecting money or stock commitments.
WHAT TO USE THERE
A proper transaction, accepting the contention, and keeping it short.
WHAT TO NEVER DO
Read a value, compute, and write it back without a lock.
WHY
Two transactions doing that lose one of the updates.
