Почему SELECT перед UPDATE не защищает баланс от конкурентного списания

Текст подготовлен ИИ-ассистентом по запросу simonov_tech. Ниже учебный пример, а не история из рабочего проекта. Описанные результаты SQL ожидаются по документации PostgreSQL; команды при подготовке статьи не запускались.

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

Разберём эту ситуацию на PostgreSQL с уровнем изоляции Read Committed. Сразу уточню термин: два разных списания, которым вместе не хватает денег, и повтор одного платежа требуют разных защит. Условный UPDATE помогает с перерасходом. Повтор операции придётся учитывать отдельно.

Два запроса прочитали один баланс

Для эксперимента нужна отдельная тестовая база. Баланс храним в копейках, тип bigint. Одна валюта, овердрафта нет. Сначала намеренно создадим таблицу без проверки неотрицательности:

CREATE TABLE accounts (    id bigint PRIMARY KEY,    balance bigint NOT NULL);INSERT INTO accounts (id, balance) VALUES (1, 10000);

Откройте два подключения к этой базе. В каждом выполните:

BEGIN ISOLATION LEVEL READ COMMITTED;SELECT balance FROM accounts WHERE id = 1;

Оба запроса прочитают 10000. Каждый обработчик хочет списать 7000, и проверка balance >= amount в приложении проходит.

Теперь в первом подключении:

UPDATE accountsSET balance = balance - 7000WHERE id = 1RETURNING balance;

Ожидаемый результат: 3000. Пока не выполняйте COMMIT.

Во втором подключении запустите тот же UPDATE. Он будет ждать. Вернитесь в первое и выполните:

COMMIT;

После этого второй UPDATE продолжится. Его ожидаемый результат: -4000. Завершите вторую транзакцию командой COMMIT.

Обратите внимание: оба обработчика уже работали внутри транзакций. Само наличие BEGIN не связало обычный SELECT с последующим списанием. При Read Committed чтение получает снимок на начало команды. Условие WHERE id = 1 ничего не говорит о допустимом остатке. Документация об изоляции транзакций.

Иногда код устроен иначе: приложение вычисляет 10000 - 7000 и оба раза записывает готовое значение 3000. Тогда отрицательного баланса не будет, но два принятых списания отразятся в остатке как одно. Проверять только отсутствие минуса в таком тесте недостаточно.

Переносим условие в UPDATE

Для одиночного списания проверка и изменение помещаются в одну команду. Ниже $1 означает сумму в копейках, $2 означает идентификатор счёта. Чтобы пример можно было выполнить в SQL-консоли, оформим его как подготовленный запрос:

PREPARE debit (bigint, bigint) ASUPDATE accountsSET balance = balance - $1WHERE id = $2  AND balance >= $1  AND $1 > 0RETURNING balance;

Положительная сумма обязательна: отрицательное значение превратило бы вычитание в пополнение. В приложении сумму также нужно валидировать до обращения к базе, включая диапазон bigint и правила работы с валютой.

Повторим эксперимент. Убедитесь, что обе предыдущие транзакции завершены. В одном подключении восстановите баланс:

UPDATE accounts SET balance = 10000 WHERE id = 1;

Если запрос ещё не подготовлен, выполните PREPARE выше в каждом подключении: он принадлежит сессии. Затем в первом:

BEGIN ISOLATION LEVEL READ COMMITTED;EXECUTE debit(7000, 1);

Ожидаем 3000, транзакцию пока оставляем открытой. Во втором:

BEGIN ISOLATION LEVEL READ COMMITTED;EXECUTE debit(7000, 1);

Второй запрос ждёт. Выполните COMMIT в первом подключении. Теперь второй запрос должен вернуть ноль строк. Завершите его транзакцию командой COMMIT. Итоговый баланс должен остаться 3000.

После ожидания PostgreSQL проверяет условие WHERE на обновлённой версии строки. Остаток уже равен 3000, поэтому условие balance >= 7000 ложно. Это поведение описано для Read Committed; переносить его без проверки на другую СУБД или уровень изоляции не стоит. Поведение конкурентных UPDATE.

RETURNING balance позволяет получить новый остаток без дополнительного чтения. Если запрос выполняется внутри явной транзакции, возвращённая строка ещё не означает, что транзакция успешно зафиксирована. Документация RETURNING.

Что означает отсутствие строки

Для этого запроса ноль строк означает, что списание не произошло. Причины могут быть разными:

• счёта с таким id нет;

• денег недостаточно;

• сумма неположительная или передана как NULL.

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

Ошибка SQL, таймаут и отсутствие строки также требуют разных веток обработки. Их нельзя свести к одному false.

Ограничение на уровне схемы

После восстановления корректных данных добавим защиту:

ALTER TABLE accountsADD CONSTRAINT accounts_balance_nonnegativeCHECK (balance >= 0);

Она остановит запись отрицательного остатка, даже если другой участок кода забудет условие в UPDATE. При нарушении будет ошибка, которую приложение должно обработать.

NOT NULL оставляем: сам по себе CHECK (balance >= 0) допускает NULL, потому что проверка отвергает ложное выражение, но не неизвестное значение. Ограничения CHECK и NOT NULL.

Чего этот запрос не решает

Повтор одного платежа. Допустим, списание зафиксировано, но HTTP-ответ потерялся. Клиент повторяет запрос, и денег хватает ещё раз. Условный UPDATE снова успешно спишет сумму. Здесь нужен ключ идемпотентности: уникальная запись операции, проверка соответствия её параметров и сохранённый результат. Запись операции и изменение баланса должны фиксироваться согласованно, в одной транзакции.

Перевод между счетами. Списание, зачисление и запись операции объединяются в транзакцию. Если получатель не найден или любой обязательный шаг не выполнен, нужен ROLLBACK, даже когда SQL не вернул ошибку, а изменил ноль строк. Проверять нужно оба обновления. Транзакции и откат.

Правила на несколько строк. Проверка общего лимита по нескольким счетам уже выходит за рамки этого примера. Может понадобиться другая схема блокировок или SERIALIZABLE. При ошибке сериализации 40001 повторяют всю транзакцию вместе с логикой принятия решений, а не один упавший запрос. Обработка ошибок сериализации.

Для простого списания с одной строки достаточно начать с конкретного вопроса: где проверяется доступный остаток? Если только в коде между SELECT и UPDATE, конкурентный запрос успевает сделать эту проверку устаревшей. Условие в самом обновлении закрывает именно этот промежуток, а ограничения схемы и учёт повторных операций решают оставшиеся задачи.

Иллюстрация создана с помощью ИИ.