SQL Ready | Базы Данных
Как атомарно резервировать лимит без SELECT FOR UPDATE!
При работе с квотами, остатками и лимитами важно не допустить, чтобы конкурентные запросы одновременно прошли проверку одного и того же доступного значения. Если проверка выполняется отдельно от изменения данных, между этими операциями появляется окно гонки:
SELECT used, quota
FROM accounts
WHERE id = 42;
Если два процесса одновременно получили used = 80 при quota = 100 и каждый собирается зарезервировать ещё 15, обе проверки в приложении могут успешно пройти.
Проверка ограничения должна выполняться непосредственно в операции изменения данных:
UPDATE accounts
SET used = used + 15
WHERE id = 42
AND used + 15 <= quota
RETURNING used;
Такой запрос одновременно проверяет условие и изменяет строку. В PostgreSQL конкурентный UPDATE одной строки сериализуется через row-level locking. Если другая транзакция успела изменить строку, условие WHERE для ожидавшего UPDATE будет повторно проверено относительно актуальной версии строки в READ COMMITTED:
UPDATE accounts
SET used = used + :amount
WHERE id = :account_id
AND used <= quota - :amount
RETURNING id, used, quota;
Если строка вернулась через RETURNING, резервирование выполнено. Если результат пустой, строка отсутствует либо доступного лимита недостаточно. Для прикладной логики эти случаи при необходимости можно различить отдельной проверкой после неуспешной попытки.
Предполагается, что amount >= 0: это стоит валидировать в приложении или закрепить ограничением на уровне БД:
UPDATE inventory
SET reserved = reserved + :qty
WHERE product_id = :product_id
AND reserved <= stock - :qty
RETURNING product_id, reserved;
Тот же паттерн применяется к резервированию товара, квотам API, счётчикам использования и другим инвариантам, которые выражаются условием над одной изменяемой строкой. Отдельный SELECT FOR UPDATE здесь нужен не всегда: если проверку можно выразить непосредственно в WHERE, сам UPDATE становится точкой синхронизации:
UPDATE limits
SET consumed = consumed + :delta
WHERE id = :id
AND consumed <= maximum - :delta
RETURNING consumed;
🔥 Делаем вывод: инвариант вида consumed + delta <= maximum над одной строкой лучше проверять внутри атомарного UPDATE, а не между SELECT и UPDATE в приложении. Это сокращает критическую секцию и корректно работает при конкурентном изменении строки.
➡️ SQL Ready | #практика
22 · 1.7K ·