Атомарный счётчик в SQL: почему n = n + 1 безопаснее, чем считать в приложении

Счётчик кажется одной из самых простых вещей в базе данных.

Есть число. Нужно увеличить его на единицу.

Например:

• количество просмотров статьи;
• число лайков;
• количество попыток входа;
• остаток товара;
• номер следующей задачи;
• сумма заказов пользователя.

На первый взгляд можно просто прочитать текущее значение, прибавить 1 в приложении и записать обратно.

Но именно так часто появляется неприятный баг — lost update, или потерянное обновление.

Правильный вариант почти всегда такой:

UPDATE counters
SET n = n + 1
WHERE id = 1;

В этом запросе база сама читает текущее значение, прибавляет единицу и записывает результат. Всё происходит одной атомарной командой.

Разберёмся, почему это важно.

Таблица для примера

Представим таблицу счётчиков:

CREATE TABLE counters (
    id bigint PRIMARY KEY,
    n  bigint NOT NULL DEFAULT 0
);

Добавим один счётчик:

INSERT INTO counters (id, n)
VALUES (1, 41);

Теперь в таблице есть строка:

id	n
1	41

Допустим, это счётчик просмотров статьи.

При каждом новом просмотре нужно увеличить n на 1.

Наивный подход: прочитать, посчитать, записать

Очень частая ошибка — делать инкремент в три шага.

Сначала приложение читает текущее значение:

SELECT n
FROM counters
WHERE id = 1;

Получает:

41

Потом приложение прибавляет единицу у себя в памяти:

41 + 1 = 42

И записывает новое значение обратно:

UPDATE counters
SET n = 42
WHERE id = 1;

Если один пользователь открыл страницу один раз, всё выглядит нормально.

Но проблема начинается, когда несколько процессов делают это одновременно.

Как появляется lost update

Представим, что два пользователя почти одновременно открыли одну и ту же статью.

Счётчик сейчас равен 41.

Первый процесс делает:

SELECT n
FROM counters
WHERE id = 1;

и получает:

41

Второй процесс в этот же момент делает такой же запрос:

SELECT n
FROM counters
WHERE id = 1;

и тоже получает:

41

Теперь оба процесса у себя в приложении считают:

41 + 1 = 42

Первый записывает:

UPDATE counters
SET n = 42
WHERE id = 1;

Второй тоже записывает:

UPDATE counters
SET n = 42
WHERE id = 1;

Что получилось?

Было два просмотра. Счётчик должен был стать 43. Но стал 42.

Один инкремент потерялся.

Это и есть lost update — потерянное обновление.

Самое неприятное, что ошибки при этом может не быть. Запросы выполняются успешно. Приложение довольно. База не ругается. Просто значение становится неправильным.

Под нагрузкой такие потери могут происходить постоянно, и счётчик будет медленно, но уверенно отставать от реальности.

Правильный вариант: один UPDATE вместо трёх шагов

Вместо того чтобы считать новое значение в приложении, нужно поручить это базе:

UPDATE counters
SET n = n + 1
WHERE id = 1;

Вот это и есть атомарный инкремент.

Здесь нет отдельного SELECT, нет расчёта в приложении и нет записи заранее посчитанного числа.

База сама делает всё внутри одного оператора:

1. Находит строку.
2. Берёт нужную блокировку.
3. Читает текущее значение n.
4. Считает n + 1.
5. Записывает новое значение.

Если два процесса одновременно выполнят:

UPDATE counters
SET n = n + 1
WHERE id = 1;

PostgreSQL не даст им испортить значение.

Один UPDATE выполнится первым и увеличит счётчик с 41 до 42.

Второй подождёт, увидит уже обновлённое значение 42 и увеличит его до 43.

Итог будет правильным:

id	n
1	43

Два инкремента дали +2.

Главное отличие: не отправляйте готовое число

Плохой вариант:

UPDATE counters
SET n = 42
WHERE id = 1;

Здесь приложение заранее решило, что новое значение должно быть 42.

Но оно могло принять это решение на основе старых данных.

Хороший вариант:

UPDATE counters
SET n = n + 1
WHERE id = 1;

Здесь новое значение считается от текущего значения в базе.

Это принципиально важно.

Короткое правило:

Не считайте новый счётчик в приложении. Считайте его внутри UPDATE.

Почему это атомарно

Атомарность означает, что операция выполняется как единое целое.

В запросе:

UPDATE counters
SET n = n + 1
WHERE id = 1;

нет места, куда могла бы «вклиниться» другая транзакция между чтением n и записью нового значения.

Для приложения это одна команда.

PostgreSQL сам следит за конкурентным доступом к строке. Если несколько транзакций хотят изменить одну и ту же строку, они не перетирают друг друга молча. Они выстраиваются в очередь за блокировкой строки.

Поэтому n = n + 1 безопаснее, чем схема:

SELECT -> прибавить в коде -> UPDATE готовым числом

Пример: лайки к посту

Допустим, есть таблица постов:

CREATE TABLE posts (
    id          bigint PRIMARY KEY,
    title       text NOT NULL,
    likes_count bigint NOT NULL DEFAULT 0
);

Пользователь нажал «лайк».

Плохая схема:

SELECT likes_count
FROM posts
WHERE id = 10;

Потом приложение прибавляет единицу и пишет готовое значение:

UPDATE posts
SET likes_count = 128
WHERE id = 10;

Под нагрузкой лайки могут теряться.

Правильный вариант:

UPDATE posts
SET likes_count = likes_count + 1
WHERE id = 10;

Если нужно снять лайк:

UPDATE posts
SET likes_count = likes_count - 1
WHERE id = 10
  AND likes_count > 0;

Здесь условие likes_count > 0 защищает от ухода счётчика в минус.

Пример: остаток товара на складе

Счётчик — это не только просмотры и лайки.

Остаток товара тоже можно рассматривать как число, которое уменьшается при покупке.

Есть таблица товаров:

CREATE TABLE products (
    id    bigint PRIMARY KEY,
    name  text NOT NULL,
    stock bigint NOT NULL DEFAULT 0
);

Покупатель хочет купить 2 единицы товара.

Правильный запрос:

UPDATE products
SET stock = stock - 2
WHERE id = 100
  AND stock >= 2;

Что здесь важно:

• stock = stock - 2 уменьшает актуальный остаток;
• stock >= 2 проверяет, что товара хватает;
• всё происходит в одном UPDATE.

Если товара хватает, обновится одна строка.

Если товара не хватает, обновится ноль строк.

Приложение должно посмотреть на количество затронутых строк:

UPDATE 1

значит покупка прошла.

UPDATE 0

значит товара не хватило или такой строки нет.

Как вернуть новое значение через RETURNING

Часто после инкремента нужно сразу узнать новое значение.

Например:

• показать пользователю новое число лайков;
• получить новый номер в очереди;
• вернуть обновлённый остаток;
• записать новое значение в лог.

Не нужно делать отдельный SELECT.

В PostgreSQL есть RETURNING:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

Если до запроса было:

id	n
1	41

то результат будет:

n
42

Главное преимущество: вы получаете значение из того же самого атомарного запроса.

Не так:

UPDATE counters
SET n = n + 1
WHERE id = 1;

SELECT n
FROM counters
WHERE id = 1;

А так:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

Так вы точно получаете результат именно своего обновления.

Что делать, если строки счётчика ещё нет

Иногда счётчик нужно создать при первом событии.

Например, для статьи ещё не было просмотров, поэтому строки в counters нет.

Хочется одной командой сделать так:

• если строки нет — вставить n = 1;
• если строка есть — увеличить n на 1.

В PostgreSQL для этого используют INSERT ... ON CONFLICT.

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1;

Разберём:

INSERT INTO counters (id, n)
VALUES (1, 1);

пытается вставить новую строку.

Если строки с id = 1 ещё нет, она появится со значением 1.

Если строка с id = 1 уже есть, сработает конфликт по первичному ключу ON CONFLICT (id).

И тогда выполнится DO UPDATE SET n = counters.n + 1, то есть существующий счётчик увеличится на единицу.

Важная ошибка в ON CONFLICT

В ON CONFLICT есть специальное имя EXCLUDED.

Оно означает строку, которую PostgreSQL пытался вставить, но не смог из-за конфликта.

В нашем примере VALUES (1, 1) значит, что EXCLUDED.n равно 1.

Поэтому вот так писать нельзя:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = EXCLUDED.n;

Почему?

Потому что при каждом конфликте вы будете ставить n = 1.

Счётчик не будет расти.

Он застрянет на единице.

Правильно:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1;

Здесь counters.n — это текущее значение в таблице.

А значит, счётчик действительно увеличивается.

Upsert с RETURNING

Можно сразу вернуть новое значение:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1
RETURNING n;

Если строки не было, запрос вставит её и вернёт:

n
1

Если строка уже была со значением 41, запрос увеличит её и вернёт:

n
42

Это очень удобный паттерн для счётчиков, которые создаются лениво — только при первом событии.

Пример: счётчик просмотров по статьям

Допустим, есть таблица просмотров:

CREATE TABLE article_views (
    article_id bigint PRIMARY KEY,
    views      bigint NOT NULL DEFAULT 0
);

Каждый просмотр статьи можно записывать так:

INSERT INTO article_views (article_id, views)
VALUES (1001, 1)
ON CONFLICT (article_id)
DO UPDATE SET views = article_views.views + 1
RETURNING views;

Что получится:

• первый просмотр создаст строку со значением 1;
• второй просмотр увеличит до 2;
• третий — до 3;
• параллельные просмотры не перетрут друг друга.

Инкремент на другое число

Счётчик не обязательно увеличивать на 1.

Можно прибавлять любое значение.

Например, пользователь получил 50 бонусных баллов:

UPDATE users
SET bonus_points = bonus_points + 50
WHERE id = 42
RETURNING bonus_points;

Или нужно увеличить сумму заказов пользователя на сумму нового заказа:

UPDATE users
SET orders_total = orders_total + 1500
WHERE id = 42
RETURNING orders_total;

Если сумма лежит в другой таблице, можно использовать подзапрос:

UPDATE users
SET orders_total = orders_total + (
    SELECT amount
    FROM orders
    WHERE id = 5001
)
WHERE id = 42
RETURNING orders_total;

Главная идея та же:

новое значение должно считаться внутри SQL от текущего значения в базе.

Атомарность решает корректность, но не убирает очередь

Важно понимать ограничение.

Запрос:

UPDATE counters
SET n = n + 1
WHERE id = 1;

безопасен с точки зрения корректности.

Но если тысячи запросов одновременно увеличивают один и тот же счётчик, все они конкурируют за одну строку.

Эта строка становится горячей точкой.

PostgreSQL не потеряет инкременты, но транзакции будут ждать друг друга.

То есть атомарность отвечает на вопрос:

«Будет ли значение правильным?»

Да, будет.

Но она не обещает:

«Будет ли это бесконечно быстро при любой нагрузке?»

Нет, не будет.

Одна строка не может обновляться бесконечно параллельно. Изменения по ней всё равно выстраиваются в очередь.

Не держите блокировку дольше нужного

Когда вы выполняете:

UPDATE counters
SET n = n + 1
WHERE id = 1;

PostgreSQL берёт блокировку на строку до конца транзакции.

Если у вас автокоммит, команда быстро завершилась — блокировка быстро снялась.

Но если вы делаете это внутри длинной транзакции, может быть проблема.

Плохо:

BEGIN;

UPDATE counters
SET n = n + 1
WHERE id = 1;

-- app then does a lot of other work
-- calls an external API
-- waits for the response
-- writes to other tables
-- runs heavy computations

COMMIT;

Пока транзакция не завершилась, другие транзакции, которые хотят изменить этот же счётчик, могут ждать.

Лучше делать инкремент ближе к концу транзакции и быстро коммитить:

BEGIN;

-- main work

UPDATE counters
SET n = n + 1
WHERE id = 1;

COMMIT;

Короткое правило:

обновили горячий счётчик — не держите транзакцию открытой без необходимости.

Что делать с очень горячими счётчиками

Если один счётчик обновляется очень часто, одной строки может стать мало.

Например:

• просмотры главной страницы;
• лайки у вирусного поста;
• общий счётчик событий;
• статистика кликов по популярной рекламе.

Тогда используют шардированный счётчик.

Идея простая: вместо одной строки держим несколько строк-частей.

Например:

CREATE TABLE counter_shards (
    counter_id bigint NOT NULL,
    shard_id   integer NOT NULL,
    n          bigint NOT NULL DEFAULT 0,
    PRIMARY KEY (counter_id, shard_id)
);

Для одного логического счётчика можно создать 16 или 32 шарда.

При записи выбираем случайный шард:

UPDATE counter_shards
SET n = n + 1
WHERE counter_id = 1
  AND shard_id = 7;

Или через upsert:

INSERT INTO counter_shards (counter_id, shard_id, n)
VALUES (1, 7, 1)
ON CONFLICT (counter_id, shard_id)
DO UPDATE SET n = counter_shards.n + 1;

А при чтении считаем сумму:

SELECT SUM(n) AS total
FROM counter_shards
WHERE counter_id = 1;

Так конкуренция распределяется не по одной строке, а по нескольким.

Минус: чтение становится чуть сложнее, потому что нужно суммировать части.

Плюс: записи могут лучше параллелиться.

Когда лучше использовать SEQUENCE

Иногда вам нужен не счётчик в смысле «точное количество событий», а просто уникальный растущий номер.

Например:

• номер заявки;
• номер операции;
• технический идентификатор;
• значение для id.

Для этого лучше использовать SEQUENCE или GENERATED AS IDENTITY.

Пример:

CREATE SEQUENCE ticket_number_seq;

Получить следующий номер:

SELECT nextval('ticket_number_seq');

Или колонка в таблице:

CREATE TABLE tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL
);

Последовательности хорошо подходят для генерации уникальных чисел, потому что они быстрые и не требуют обновлять одну и ту же строку счётчика.

Но у них есть особенность: они могут давать пропуски.

Например, транзакция получила номер 101, а потом откатилась. Номер 101 уже не вернётся обратно.

Для id это нормально.

Но если вам нужен именно точный счётчик событий без пропусков, SEQUENCE может не подойти.

Коротко:

нужен уникальный растущий номер — используйте SEQUENCE или IDENTITY; нужно точное накопленное значение — используйте атомарный UPDATE n = n + 1.

Частые ошибки

Ошибка 1. Считать новое значение в приложении

Плохо:

SELECT n
FROM counters
WHERE id = 1;

Потом:

UPDATE counters
SET n = 42
WHERE id = 1;

Правильно:

UPDATE counters
SET n = n + 1
WHERE id = 1;

Ошибка 2. Делать отдельный SELECT после UPDATE

Не всегда ошибка, но часто лишнее:

UPDATE counters
SET n = n + 1
WHERE id = 1;

SELECT n
FROM counters
WHERE id = 1;

Лучше:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

Ошибка 3. В upsert писать n = EXCLUDED.n

Плохо:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = EXCLUDED.n;

Так счётчик будет снова и снова становиться 1.

Правильно:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1;

Ошибка 4. Держать транзакцию открытой после инкремента

Плохо:

BEGIN;

UPDATE counters
SET n = n + 1
WHERE id = 1;

-- long work

COMMIT;

Лучше коммитить как можно быстрее после изменения горячей строки.

Ошибка 5. Использовать одну строку для сверхгорячего счётчика

Для обычных счётчиков одна строка нормальна.

Но если в одну строку летят тысячи обновлений в секунду, она станет узким местом.

Тогда стоит подумать о шардировании счётчика или другой архитектуре сбора событий.

MySQL: как сделать то же самое

В MySQL/InnoDB атомарный инкремент выглядит так же:

UPDATE counters
SET n = n + 1
WHERE id = 1;

Для upsert используется другой синтаксис:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE n = n + 1;

Идея та же:

• если строки нет — вставить;
• если строка есть — увеличить счётчик.

В PostgreSQL удобно использовать RETURNING, чтобы сразу получить новое значение:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

В MySQL с этим исторически сложнее. В зависимости от версии и задачи часто используют отдельный SELECT в той же транзакции или специальные приёмы с LAST_INSERT_ID().

Главная мысль при этом не меняется:

не считайте инкремент в приложении, делайте n = n + 1 внутри SQL.

ClickHouse: другой подход к счётчикам

ClickHouse — аналитическая база.

Она отлично подходит для агрегаций по большим объёмам данных, но не для частых точечных обновлений одной строки.

Поэтому счётчики в ClickHouse обычно проектируют иначе.

Например, вместо постоянного:

UPDATE counters
SET n = n + 1
WHERE id = 1;

чаще записывают события или добавочные значения, а потом агрегируют их.

Для таких задач в ClickHouse используют движки и подходы вроде:

• SummingMergeTree;
• AggregatingMergeTree;
• хранение событий и подсчёт через sum();
• материализованные представления для агрегатов.

То есть в OLTP-базах вроде PostgreSQL и MySQL счётчик часто обновляют на месте.

А в ClickHouse обычно складывают данные аналитически: добавляют новые строки, а сумма получается при чтении или при слиянии частей.

Короткая шпаргалка

Увеличить счётчик на 1:

UPDATE counters
SET n = n + 1
WHERE id = 1;

Увеличить и вернуть новое значение:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

Уменьшить счётчик, не уходя ниже нуля:

UPDATE counters
SET n = n - 1
WHERE id = 1
  AND n > 0
RETURNING n;

Создать счётчик или увеличить существующий:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1
RETURNING n;

Увеличить остаток или накопленную сумму:

UPDATE users
SET orders_total = orders_total + 1500
WHERE id = 42
RETURNING orders_total;

Шардированный счётчик:

INSERT INTO counter_shards (counter_id, shard_id, n)
VALUES (1, 7, 1)
ON CONFLICT (counter_id, shard_id)
DO UPDATE SET n = counter_shards.n + 1;

Прочитать сумму по шардам:

SELECT SUM(n) AS total
FROM counter_shards
WHERE counter_id = 1;

Главное правило

Никогда не делайте инкремент счётчика по схеме:

SELECT n -> прибавить 1 в приложении -> UPDATE n = новое число

Под параллельной нагрузкой такая схема теряет обновления.

Правильный вариант:

UPDATE counters
SET n = n + 1
WHERE id = 1;

База сама прочитает актуальное значение, прибавит единицу и запишет результат атомарно.

Если нужно новое значение — добавьте RETURNING.

Если строки может ещё не быть — используйте INSERT ... ON CONFLICT DO UPDATE.

Если счётчик слишком горячий — подумайте о шардировании или другой архитектуре.

Запомните коротко:

счётчик должен увеличиваться внутри SQL, выражением n = n + 1, а не в памяти приложения.

Так вы защититесь от lost update и получите корректное значение даже при параллельных запросах.