The Slotted Counter Pattern

This post introduces the 'Slotted Counter Pattern' to address contention issues when incrementing single-row counters in high-throughput scenarios. It explains how to detect contention using `SHOW ENGINE INNODB STATUS` and provides a schema for a `slotted_counters` table with `record_type`, `record_id`, `slot`, and `count` columns. The post demonstrates an `INSERT ... ON DUPLICATE KEY UPDATE` query to increment counts within randomly chosen slots, spreading updates across multiple rows to reduce contention. It also shows how to retrieve the total count by summing the counts across all slots for a given record type and ID. This pattern improves concurrency and reduces query times by avoiding serial transaction execution on a single row.