Atomic counters in MySQL...

How to modify a value and fetch it atomically.

One way would be a table with auto_increment column, but that way one table equals one counter. If you want to have more counters you can use the INSERT … ON DUPLICATE KEY construct and subquery with variables.

Table schema:

CREATE TABLE counter (
   id int(10) unsigned NOT NULL,
   count int(10) unsigned NOT NULL,
   PRIMARY KEY  (id);
) ENGINE=InnoDB;

Query which increments the counter and fetches it’s value:

INSERT INTO counter (id, count)
(SELECT 1, @count := 1)
ON DUPLICATE KEY UPDATE count = (SELECT @count := count + 1);
SELECT @count AS count;