Conditional UPDATE in SQL: Atomic Check-and-Set with WHERE

Условный UPDATE — это приём, при котором предусловие записано прямо в WHERE, так что проверка и изменение происходят одной атомарной командой. Классический пример — списать деньги со счёта только если их хватает: база сама решит, что строка под условие не подходит, и вернёт ноль затронутых строк вместо порчи данных.

Проверка и запись одной командой

Возьмём счёт и попробуем списать 200. Наивный подход — сначала SELECT balance, потом в коде сравнить, потом UPDATE. Между этими шагами другой транзакцией баланс может измениться, и вы спишете деньги, которых уже нет. Правильный путь — слить проверку и запись:

UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200;

Здесь balance >= 200 — не фильтр выборки, а предусловие. Если денег не хватает, строка не пройдёт по WHERE, и UPDATE просто ничего не изменит. Никакого отдельного чтения: новое значение считается от текущего (balance - 200) прямо внутри движка, под блокировкой строки.

• Условие и изменение видят один и тот же снимок строки — между ними нельзя «вклиниться».
• balance = balance - 200 читает актуальное значение, а не то, что вы видели секунду назад.
• Если строки с id = 1 нет вовсе, результат тот же — ноль затронутых строк.

Число затронутых строк как сигнал

Главный трюк — не делать после UPDATE второй SELECT, чтобы понять, прошло ли списание. Об этом говорит само число изменённых строк. Драйверы отдают его: в PostgreSQL — cmd_status / rowcount, через psql виден тег UPDATE 1 или UPDATE 0.

-- 1 row -> debit applied; 0 rows -> insufficient funds
UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200;

Логика приложения становится тривиальной: получили 1 — успех, получили 0 — «недостаточно средств» (или счёта нет). Чтобы различить эти два случая, удобен RETURNING:

UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200
RETURNING id, balance;

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

Compare-and-swap по версии

Тот же шаблон решает проблему потерянного обновления для любых полей, не только для денег. Допустим, два процесса читают заказ и оба хотят его изменить. Добавим в WHERE ожидаемое прежнее значение — это и есть compare-and-swap.

UPDATE orders
SET status = 'shipped'
WHERE id = 42
  AND status = 'paid';

Кто-то один переведёт заказ из paid в shipped и получит 1 строку; второй увидит уже не paid и получит 0 — его обновление безопасно отклонено. Часто для этого держат столбец-версию:

UPDATE orders
SET status = 'shipped',
    version = version + 1
WHERE id = 42
  AND version = 7;

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

Подводный камень и различия СУБД

Главная ошибка — вернуться к двухшаговой схеме «SELECT затем UPDATE» под нагрузкой. Между шагами образуется окно гонки, и под параллельными запросами баланс уходит в минус. Условие обязано жить в WHERE того же UPDATE.

• Транзакции. В READ COMMITTED (умолчание PostgreSQL) UPDATE перечитает строку перед записью, так что приём надёжен и без явных блокировок. Но если в одной транзакции вы сначала сделали SELECT, а потом UPDATE, защищает только условие в UPDATE, а не то, что вы видели в SELECT.
• MySQL/InnoDB. Работает так же; число строк зависит от флага: с CLIENT_FOUND_ROWS сервер вернёт совпавшие, иначе — реально изменённые. Это важно, если новое значение равно старому.
• ClickHouse. ALTER TABLE ... UPDATE асинхронна и не транзакционна, а сравнения вроде «не списать в минус» она не гарантирует. Для строгого check-and-set ClickHouse не предназначен — держите такой счёт в OLTP-базе.

Грабли при проектировании API: не превращайте ноль затронутых строк в общую ошибку «не найдено». Для пользователя это разные ситуации: объекта может не быть, денег может не хватить, версия могла устареть. Если нужен точный ответ, возвращайте данные через RETURNING или проверяйте причину отдельным чтением уже после отказа.

Запомните: один UPDATE, предусловие в WHERE, решение по числу затронутых строк.