Advanced SQL Tutorial

Atomic Counters through Unique Key Checks

Imagine the following table:

CREATE TABLE u(id INT PRIMARY KEY, cnt INT);

INSERT INTO u VALUES (1, 2), (2, 4);

SELECT * FROM u;

+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    4 |
+------+------+
3 rows in set (0,001 sec)

If you want to add to cnt whenever an event occurs, add a unique key over id and do the following:

INSERT INTO u VALUES (2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);


+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    5 |
+------+------+
3 rows in set (0,001 sec)

This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via Data Auto Sharding and Auto Indexing