Хабр Курсы для бэкендеров
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Поток Бэкенд доступен 24/7 благодаря поддержке друзей Хабра
Комментарии 16
Lost update проходит молча, и чинить его приходится самостоятельно
Почему в стандарте SQL, который реализует PostgreSQL, пишут "The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost."?
Путаница с термином «lost update» — в стандарте и в статье это разные вещи.
«No updates will be lost» в стандарте — про dirty write: незакоммиченную запись транзакции другая не перезатрёт. Эту гарантию дают все 4 уровня через write-локи до конца транзакции.
«Lost update» в статье — read-modify-write аномалия: две транзакции читают один баланс, обе пишут, обе коммитятся — формально ничего не «потеряно», но логически одно списание затёрто.
На практике Postgres так и ведёт себя: READ COMMITTED теряет такой апдейт, REPEATABLE READ ловит конфликт ошибкой 40001.
Подробный разбор: списание с овердрафтом
Что за бред у вас там? Читаем из таблицы accounts, а main и reserve - это значения-параметры, передаваемые в запрос. А вот пишем в таблицы main и reserve.
Почему не атомарный: сколько снять с каждого (70 и 30) вычисляется из балансов обоих счетов — это решение в коде, одной командой
balance = balance - Xне выразить.
Да, PostgreSQL не умеет многотабличный UPDATE. Зато умеет UPDATE в CTE. Так что всё прекрасно выражается одним запросом. Включая и обеспечение того, что итоговый balance не вылетел в минус в обеих записях - для этого есть функция GREATEST(). И не нужно ничего считать в коде, не требуются ни транзакции, ни FOR UPDATE.
да, согласен, пример синтетический. главная мысль в том, что не все можно выразить внутри запроса. и еще есть системы, которые построены на базе ORM и не всегда рационально делать raw queries, только чтобы привести запрос к атомарному виду. и да, вычисления в рантайме приложения, существуют, и поэтому области применения FOR UPDATE всегда найдутся.
main/reserve это id-параметры, таблица одна; в шаге 4 опечатка, должно быть UPDATE accounts SET ... WHERE id = main/reserve. Поправил.
по CTE — тут не соглашусь в главном. да, multi-table UPDATE через data-modifying CTE выражается одним запросом, и раздачу 70/30 с защитой от минуса можно собрать на LEAST/GREATEST — считать в коде необязательно.
но «не нужны ни транзакции, ни FOR UPDATE» — неверно. Все под-запросы CTE работают на одном снапшоте, а в READ COMMITTED при конфликте Postgres перечитывает только саму обновляемую строку и её WHERE — не значения, подтянутые из другой строки. цитата из доки: [Because of the above rules, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED) .
сумма с reserve зависит от баланса main — другой строки. конкурентное изменение main между снапшотом и апдейтом приводит к неверному распределению: суммы посчитаны по старому балансу, а он уже изменился — спишется не та сумма. дока про Read Committed называет такой режим «unsuitable for commands that involve complex search conditions» (13.2.1 из доки выше), хендлится блокировкой строк — SELECT … FOR UPDATE — либо переходом на REPEATABLE READ + повтор.
если есть рабочий вариант без локов, который держит конкурентный тест на двух счетах — с интересом изучу, обновлю статью.
если есть рабочий вариант без локов, который держит конкурентный тест на двух счетах — с интересом изучу
Ну вот, быстренько накидал: fiddle. Оптимальностью не пахнет, даже два CTE избыточны, но принцип демонстрируется. Изменяя cte1.amount, можно убедиться, что все три варианта (достаточно main счёта, достаточно суммарно, недостаточно) обрабатываются правильно.
Текст запроса (на всякий случай)
WITH
cte1 (main_balance, reserve_balance, amount) AS
( -- получить балансы счетов в одну запись
SELECT main.balance,
reserve.balance,
100
FROM accounts reserve
CROSS JOIN accounts main
WHERE reserve.id = 'reserve'
AND main.id = 'main'
),
cte2 (main_balance, reserve_balance) AS
( -- посчитать итоговые суммы
SELECT GREATEST(main_balance - amount, 0),
GREATEST(main_balance + reserve_balance - amount - GREATEST(main_balance - amount, 0))
FROM cte1
)
UPDATE accounts -- записать итоговые суммы
SET balance = CASE id WHEN 'main' THEN main_balance
WHEN 'reserve' THEN reserve_balance
END
FROM cte2
WHERE reserve_balance >= 0
AND id IN ('main', 'reserve');Для того, чтобы избежать описанной вами проблемы, достаточно протащить за собой через CTE старые значения и сравнить во внешнем запросе с текущими. Если хотя бы одна запись изменена - спровоцировать не-выполнение запроса или ошибку (последнее - проще, что-нить вроде 1/ifnullif(nullif())).
спасибо за фиддл. согласен что считать в приложении в нашем примере необязательно. многие вещи можно посчитать и обновить на уровне SQL
но какой ценой?
протащить через CTE старые значения балансов
во внешнем запросе сравнить их с текущими
если хоть одна строка изменилась — уронить весь запрос ошибкой (через sql-хак)
приложение потом повторяет
(надеюсь что правильно понял идею)
ваш пример доказывает что расчет и перевод можно выразить в SQL, но по сути это теже оптимистичные блокировки, которые доступны через штатные механизмы БД, с ценой повторов.
у вас сложность не исчезла, а переехала в запрос. Просто сравните:
BEGIN;
-- 1. Залочили обе строки и прочитали свежие балансы
SELECT id, balance FROM accounts
WHERE id IN ('main', 'reserve')
FOR UPDATE;
-- main=70, reserve=500
-- 2. Посчитали в коде: с main снять 70, остаток 30 — с reserve
-- 3. Записали
UPDATE accounts SET balance = 0 WHERE id = 'main';
UPDATE accounts SET balance = 470 WHERE id = 'reserve';
COMMIT;3 банальных стейтмента против двойного CTE с намеренной ошибкой-абортом
но какой ценой?
Что? Посчитать тупо арифметику на сервере - это, по вашему мнению, цена??? А на самом деле это то, что вы при всём своём желании не сможете зарегистрировать, потому что эта ваша цена будет гораздо ниже точности измерения. Тьфу, и растереть.
3 банальных стейтмента против двойного CTE с намеренной ошибкой-абортом
Ну да... а о 6 сессиях обмена трафиком с сервером вместо двух, о трёх против одного сеансов работы планировщика (и вообще всё в тройном размере), и о дополнительном выполняемом в приложении коде (да с учётом того, что клиент по сравнению с сервером если и не улитка, то уж точно не метеор) вы, конечно, стыдливо так забыли... зря, зря.
А что, если резервных счетов - два? или три? под каждый вариант напишете свой код, да? А вот на стороне SQL это всё делается опять же одним запросом, даже если этих счетов десяток.
говоря о цене, мы говорим о цене саппорта такого решения, а не о нагрузке на железо.
А что, если резервных счетов - два? или три? под каждый вариант напишете свой код, да? А вот на стороне SQL это всё делается опять же одним запросом, даже если этих счетов десяток.
я выберу 10 простых примитивных запросов, а не один сложный, и вот почему:
в простом запросе при рефакторинге LLM допустит ошибку с меньшей вероятностью, простор для "творчества" меньше ;)
у простого запроса меньше сайд-эффектов, я точно знаю что он делает (и мой Claude тоже)
делая сложный запрос я вынужден имплементировать механизм абстракции для того, чтобы работали все N (10) кейсов. эту логику надо знать и мейнтейнить. а ее могло бы не быть
говоря о цене, мы говорим о цене саппорта такого решения, а не о нагрузке на железо.
Сколько вам стОит саппорт любой стандартной функции из любой стандартной библиотеки? думаю, бесплатно. А если она из стандартной для проекта, а то и для всей фирмы, пользовательской библиотеки - что, что-то меняется? стоимость создания - это не саппорт, не учитываем, исправление ошибок, неправильно учтённых краевых, тестирование, отладка, и т.д., и т.п. - это составные части стоимости создания, тоже не саппорт, не учитываем. Так откуда ненулевая цена саппорта за функцию, которая вылизана, отлажена, протестирована, включена в пользовательскую библиотеку, и просто используется, как любая другая стандартная или пользовательская функция?
Да, в этом процессе я не указываю, где нужно использовать LLM, согласен. Но я как-то и изначально не собирался. Да и вообще я не о том говорил, я просто оспорил тезис, что описанная операция не реализуется одним запросом, и, по-моему, привёл корректный контрпример.
Тут при параллельных запросах будет race condition. Если добавить pg_sleep() в какой-нибудь SELECT и запустить параллельно 2 запроса, финальный результат будет как после одного запроса.
проверил ровно ваш сценарий на pg 16: pg_sleep(2) внутри транзакции, два перевода по 100 параллельно, старт main=70, reserve=500.
с FOR UPDATE: вторая транзакция встаёт на лок, ждёт первую, потом перечитывает свежие балансы. итог main=0, reserve=370 — оба списания на месте. по времени 4 секунды, то есть сериализовались, а не параллельно.
без FOR UPDATE — да, ровно как вы говорите: 2 секунды, итог 470, одно списание потерялось
pg_sleep ничего не ломает: лок не даёт второй прочитать устаревший баланс, сколько бы первая ни спала. об этом и статья
Так я не про статью, а про комментарий вашего собеседника “не требуются ни транзакции, ни FOR UPDATE”.
а, ясно, неправильно иерархию комментов понял)
возможно будут рейсы, решение @Akina не тестировал. но интересно, не знал, что в CTE возможны рейсы, думал что все блокируется на время такого запроса. надо будет изучить тему, спасибо
Редкость по нынешним временам, разбор внутренностей постгреса. Хотя надо ли это сейчас знать самому, если честно, большой вопрос.
ИИ агент и сам это вывозит. Паттерн заезженный, данных по нему в обучении более чем достаточно, на запрос "перевод денег, конкурентный доступ" агент сам достаёт for update. Он и сам может прогнать все гонки, увидеть просадку баланса и переписать запрос, ему не обязательно понимать, достаточно проверить.
никто не предлагает в 2026 писать код руками. Но я бы никогда не гарантировал что ИИ всегда поставит for update там, где это необходимо. а цена ошибки слишком высокая - через такую дыру можно опустошить легко опустошить hot wallet проекта. и такое происходит на CEXах
ранее случались громкие истории типа Flexcoin и Poloniex
сейчас все такие уязвимости у крупных проектов лечатся через bug bounty programs
cамо знание не такое сложное, а цена ошибки высокая. Ну и просто полезно понимать, что происходит под нагрузкой на сервисе, где балансы лежат в реляционной БД.
и как всегда бывает в CS - атаки усложняются вместе с эволюцией средств защиты:
The single-packet attack: making remote race-conditions 'local'
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
2 + 2 = 6 и как мы это фиксим: lost updates в Postgres