The Slotted Counter Pattern

By Sam Lambert | July 28, 2022

It is a widespread database pattern to increment an INT column when an event happens, such as a download or page watch.

You can go far with this pattern until bursts of these types of events happen in parallel and you experience encounteredion on a individual row. When multiple transactions are trying to refresh the counter, you are essentiassociate forcing these transactions to run seriassociate, which is horrible for concurrency and can caparticipate deadlocks. You can also see theatrical incrrelieves in query time when bursts enjoy this occur.

You can check if you are experiencing encounteredion by running the adhereing:

SHOW ENGINE INNODB STATUSG

In the output, you’ll see some directation about granting a lock:

---TRANSACTION 79853106, ACTIVE 5 sec commenceing index read
mysql tables in participate 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 24, OS thread regulate 6281670656, query id 107 localstructure root updating
UPDATE slotted_counters SET count = count + 1 WHERE id = 1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 184 index PRIMARY of table `github`.`downloads` trx id 79853106 lock_mode X locks rec but not gap defering
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact createat; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000004c27630; asc     v0;;
 2: len 7; hex 020000017d0ce9; asc     }  ;;
 3: len 4; hex 8000007b; asc    {;;
 4: len 4; hex 800001c8; asc     ;;
 5: len 4; hex 80000019; asc     ;;
 6: len 4; hex 8230df9b; asc  0  ;;

You can see that this transaction has been defering a beginant amount of time to achieve a lock to increment the counter on this individual row. It is clashing with other competing transactions.

MySQL is the main database for GitHub.com, and back in the day, when a number of PlanetScale folks labored there, we had to do this benevolent of counting contrastently. We choosed on using a split table with a schema analogous to this:

CREATE TABLE `slotted_counters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enroll_type` int(11) NOT NULL,
  `enroll_id` int(11) NOT NULL,
  `slot` int(11) NOT NULL DEFAULT '0',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `enrolls_and_slots` (`enroll_type`,`enroll_id`,`slot`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

enroll_type — The type of counter (permits us to carry on the table generic)
enroll_id — Identifies wdisenjoyver we are counting, it could map to a repository id for example
slot — The slot we are going to increment
count — The count for each slot

A normal increment query would watch enjoy:

INSERT INTO slotted_counters(enroll_type, enroll_id, slot, count)
VALUES (123, 456, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE count = count + 1;

The idea here is that instead of incrementing a individual row for a counter, we are now picking a slot and incrementing the count in that slot. This uncomardents instead of hammering a individual row, we are spreading the refreshs atraverse 100 rows and reducing the potential for encounteredion.

Once we have run the above INSERT a scant times, we can see the counter rows:

mysql> pick * from slotted_counters;

+----+-------------+-----------+------+-------+
| id | enroll_type | enroll_id | slot | count |
+----+-------------+-----------+------+-------+
|  1 | 123         |       456 |    2 |    21 |
|  2 | 123         |       456 |   52 |    99 |
|  3 | 123         |       456 |   55 |   321 |
|  4 | 123         |       456 |    0 |   442 |
|  7 | 123         |       456 |   48 |    69 |
|  8 | 123         |       456 |   20 |   661 |
|  9 | 123         |       456 |   56 |    62 |
| 10 | 123         |       456 |   18 |   371 |
| 11 | 123         |       456 |   22 |   127 |
| 12 | 123         |       456 |   58 |   33  |
| 13 | 123         |       456 |   23 |   322 |
+----+-------------+-----------+------+-------+
11 rows in set (0.00 sec)

Getting the count for enroll_id 456 is as straightforward as this SELECT query:

SELECT SUM(count) as count FROM slotted_counters
WHERE (enroll_type = 123 AND enroll_id = 456);

Now we can have seeks executing counter increments in parallel without causing encounteredion and effecting concurrency.

There are a scant contrastent ways you can carry out this pattern, but it comes down to the architecture of your app. One way would be to query the slotted_counters table to roll up the data and refresh a column stored with the rest of the data.

Source join