Comments 41
В мало-мальски сложной учетной системе отделаться констрэйнтом не получится. Балансы могут быть отрицательными, т.к. в этом случае есть например крредитная овердрафт и т.д и т.п. Так что лучше не играться в эти игры, а проектировать уровни изоляции.
Подскажите, если сталкивались, как убрать ошибку:
postgres=# CREATE OR REPLACE procedure act()
LANGUAGE plpgsql
SET transaction_isolation = 'serializable'
AS $$
BEGIN
RAISE NOTICE 'isolation %', current_setting('transaction_isolation');
END;
$$;
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any queryне работал с постгрессом, но обычно устновка уровня изоляции производится перед запросом, как и написано в ошибке. Предположу, что создании процедуры конструкция SET transaction_isolation = 'serializable' должна идти внутри тела определения процедуры, вероятно где-то после BEGIN.
Спасибо! Так тоже не прошло:
postgres=# CREATE OR REPLACE procedure act()
LANGUAGE plpgsql
AS $$
BEGIN
SET transaction_isolation = 'serializable';
RAISE NOTICE 'isolation %', current_setting('transaction_isolation');
END;
$$;
call act();
CREATE PROCEDURE
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT: SQL statement "SET transaction_isolation = 'serializable'"
PL/pgSQL function act() line 3 at SQL statementа так прошло:
postgres=# CREATE OR REPLACE procedure act()
LANGUAGE plpgsql
AS $$
BEGIN
rollback;
SET transaction_isolation = 'serializable';
RAISE NOTICE 'isolation %', current_setting('transaction_isolation');
END;
$$;
call act();
CREATE PROCEDURE
NOTICE: isolation serializable
CALL
А чкюем плох select for update?
со skip locked хорош. Пример:
begin;
select * from account for update;
update account set balance=0;
commit;
с виду всё просто, но порождает взаимоблокировки при параллельном выполнении. Пример в статье про взаимоблокировки
Взаимоблокировки могут быть только если делается несколько блокирований в рамках одной транзакции, причем в разных процессах в разном порядке. Поэтому лучше делать одну на главный объект. Если вы сделали в одном процессе select for update, другой процесс при select этой же строки будет просто ждать, пока она освободится, он не будет блокировать первый процесс.
Взаимоблокировки могут быть только если делается несколько блокирований в рамках одной транзакции
А есть поток UPDATE в разных транзакциях и разных процессах на одной таблице ?
Если вы сделали в одном процессе select for update, другой процесс при select этой же строки будет просто ждать, пока она освободится
Идеальное решение , что бы повесить систему при повышенной нагрузке.
Проверено на практике - регулярно.
Повторюсь - объяснить разрабам , что так нельзя делать невозможно. "Это не мы , это фреймворк такой"(с)
При изменениях данных из приложения только так и нужно делать. Либо SELECT FOR UPDATE, либо блокировки вручную через pg_try_advisory_lock. При правильном подходе ничего не виснет, поэтому эта конструкция и добавлена в базу данных.
При изменениях данных из приложения только так и нужно делать. Либо SELECT FOR UPDATE, либо блокировки вручную через pg_try_advisory_lock.
Они именно так и делают.
И прибегают "у нас все висит, работать не возможно, мы уперлись в СУБД".
Сразу вспоминается , старый анекдот
А где у меня ошибка?
В ДНК.
Значит так и есть, они уперлись в СУБД. Еще раз, без этого из приложения работать нельзя, будут возможны race condition и двойные списания как в примере из статьи.
Может быть в приложении неправильно организован порядок этих SELECT FOR UPDATE, но без анализа кода приложения так сказать нельзя. Если сам фреймворк так делает, то мне это кажется маловероятным.
поток UPDATE в разных транзакциях и разных процессах на одной таблице
Мы говорим про SELECT FOR UPDATE, сначала происходит SELECT строки, потом UPDATE. В этом случае SELECT во всех процессах будет ждать своей очереди. Если у вас что-то падает, значит или процессов слишком много, или обработка в приложении делается слишком долго, и таймаут ожидания блокировки в базе для одного конкретного процесса истекает. Надо увеличить таймаут, или попробовать оптимизировать приложение. Также возможно базе не хватает ресурсов, и приложение долго работает из-за нее, обычно в рамках одного бизнес-процесса делается несколько запросов в базу, и все запросы после SELECT FOR UPDATE увеличивают время ожидания для остальных процессов.
Если у вас есть процессы где сразу идет UPDATE по id без предварительного SELECT, они тоже будут ждать. Если у вас есть массовые UPDATE не по id, а по условию, то так делать не надо, тут как раз возможны взаимоблокировки, потому что строки в разных процессах могут блокироваться в разном порядке.
Вы зачем это мне все рассказываете ?
Это нужно современному поколению архитекторов и разрабов рассказывать.
По личному опыту, я твердо уверен - как работает СУБД они понятия не имеют, не знают и знать не хотят . "Для нас СУБД это хранилище наших данных" - это реальная фраза, реального разраба, на конфколле по поводу аварийной ситуации.
Они твердо уверены в наличии волшебной комбинации конфигурационных параметров СУБД после задания которых их приложения начнут летать.
НИКОГДА, за все время проведения импортозамещения никто , ни разу, не обращался к DBA на этапе проектирования и разработки.
ВСЕГДА после того, как в продуктиве реальные настоящие пользователи начинают работать , ВСЕГДА начинается "у нас ничего не работает , все висит, мы уперлись в СУБД."
Исключения , только если совсем маленькая система на 5 пользователей, максимально в день 1-2 иногда зайдут.
"А у нас на тестовом сервере все работало" - этой фразе лет 30 . История сделала круг по спирали и все повторяется с новым поколением разрабов.
Вы зачем это мне все рассказываете ?
Затем, что вы не понимаете, почему в приложении используется SELECT FOR UPDATE, и говорите, что его надо убрать. Убирать его нельзя. Можно использовать альтернативу, но если есть проблемы с SELECT, то они будут и с альтернативным решением.
По личному опыту, я твердо уверен - как работает СУБД они понятия не имеют, не знают и знать не хотят .
Именно поэтому компания наняла вас и платит вам зарплату. Если бы они сами знали все подробности, вы были бы не нужны. У разработчиков кроме базы своя работа есть, а количество рабочих часов у всех одинаковое.
никто , ни разу, не обращался к DBA на этапе проектирования и разработки.
А что бы вы сделали на этапе проектирования и разработки, чего нельзя сделать после запуска?
у нас ничего не работает , все висит, мы уперлись в СУБД
Значит вы уперлись в СУБД. Иное можно сказать только после анализа последовательности запросов из приложения, чего как я понимаю вы не делаете. Это ваше дело сказать "Эти запросы надо переписать вот так, тут поменять условие, чтобы индекс работал, тут идут запросы в цикле, надо их убрать, тут не надо делать INSERT INTO ... SELECT". DBA в компаниях, где я работал, занимались именно этим.
del
плох select for update
Тем , что система ляжет , при нагрузке выше среднего .
Я в свое время пытался понять логику разрабов - зачем они используют select for update .
Объяснений так и не получилось получить ибо ORM. А как работает субд они не в курсе .
ORM знает сам как лочить записи. П окрайней мере JPA
SELECT FOR UPDATE из приложения нужен, чтобы другой веб-запрос не читал ту же строку, пока обработка в первом не завершена. Допустим, у вас есть кнопка для новых пользователей "Получить бонус", и код в приложении:
isBonusReceived = query('SELECT is_bonus_received FROM users WHERE id = :userId', ['userId' => userId]);
if (isBonusReceived === false) {
addBonusForUser(userId);
query('UPDATE users SET is_bonus_received = true WHERE id = :userId', ['userId' => userId]);
}
Если пользователь отправит из консоли браузера 2 запроса в одну миллисекунду, то оба потока прочитают значение false, и он получит бонус 2 раза. Похожая ситуация описана в примере из статьи.
А с FOR UPDATE запрос SELECT в одном из 2 потоков будет ждать, пока строка освободится, и проверка сработает правильно.
Приведённый код плохой и так писать нельзя. Как минимум, две команды SELECT и UPDATE можно было бы заменить на UPDATE users SET is_bonus_received = true WHERE id = ':userId' AND is_bonus_received = false;
Двойного списания уже не было бы. Осталось бы только незачисление. FOR UPDATE и ORM тут излишни. Как решение можно использовать функцию или процедуру, которую повесить на кнопку, чтобы фронтэндер не занимался тем, чем ему не нужно заниматься.
Почему тут стоит избегать FOR UPDATE: в одном (или больше) потоков будет заблокирован целый серверный процесс (или процессы). Создаётся узкое место. Из-за таких узких мест от некоторых ORM отказывались их производители. Про двойные нажатия - проблему встречал и отдельно описывалось, как фреймворк решает эту проблему. В то время я удивился - вот оно как бывает и правда полезная защита. Наверное, не во всех фреймворках их разработчики сразу узнают про многократные клики. )) Это примерно как две компании из стати узнали про аномалии конкурентного доступа. Когда знаешь - простая вещь.
Как минимум, две команды SELECT и UPDATE можно было бы заменить на UPDATE
Нет, нельзя. Кроме обновления строки нам в этом if нужно сделать еще кучу действий. Все они зависят от значения флага is_bonus_received. Некоторые из них могут быть нетранзакционными.
А перед выполнением этого действия нужно сначала прочитать пользователя из базы, вернуть 404 если его нет, проверить права доступа пользователя на это действие, вернуть 403 если их недостаточно, проверить значение is_bonus_received, если true показать понятную ошибку "Вы уже использовали бонус", и только потом делать начисление бонуса с обновлением строки.
FOR UPDATE и ORM тут излишни
Поэтому нет, не излишни. FOR UPDATE придуман именно для таких случаев.
Двойного списания уже не было бы.
Тут нет двойного списания, тут надо предотвратить двойное зачисление. Оно производится в addBonusForUser и может включать обращение к платежному провайдеру по сети.
в одном (или больше) потоков будет заблокирован целый серверный процесс (или процессы)
Дак в том и смысл, чтобы другие процессы остановились и не работали с этой записью и даже не читали ее, пока первый процесс не произведет все вычисления. Зачем им читать заведомо неактуальное значение и потом выполнять действия, которые от него зависят?
чтобы фронтэндер не занимался тем, чем ему не нужно заниматься
Какой еще фронтендер?) Это серверный код, фронтенд не отправляет запросы в базу.
Про двойные нажатия - проблему встречал и отдельно описывалось, как фреймворк решает эту проблему.
Фреймворк тут совершенно ни при чем, это не его ответственность. Только программист знает, нужно ли ему предотвращать двойные нажатия. И двойные нажатия тут ни при чем, хакеры отправляют параллельные запросы намеренно скриптом.
Дак в том и смысл, чтобы другие процессы остановились и не работали с этой записью и даже не читали ее, пока первый процесс не произведет все вычисления.
потому, что используют пулы соединений и блокировать процессы нельзя.
Зачем им читать заведомо неактуальное значение и потом выполнять действия, которые от него зависят?
потому что так быстрее. Логика «зачем процессу напрягаться» плоха тем, что создает огромные проблемы. Благие намереня и в оптимизации ведут туда же. Например, как 1С с временными таблицами.
Какой еще фронтендер?) Это серверный код, фронтенд не отправляет запросы в базу.
код на javascript, даже похоже на Node.js
серверный код это pl/sql, plpgsql. Промежуточный уровень для финансов java. Базы данных для финансов - oracle, postgresql. Причина даже не в том, что другое хуже, а в накопленном коде (пишут по примерам), шаблонах, качестве трудовых ресурсов. Ошибки в финансах приводят (как в статье) к большим потерям.
В этой статье столько уже накомментировали, а рейтинг не меняется 🤔😀
потому, что используют пулы соединений и блокировать процессы нельзя
Неважно какие у вас пулы, если другой процесс получит неактуальное значение, он рассчитает неправильные новые значения. Писать в базу заведомо неверные значения потому что у вас не хватает ресурсов не имеет смысла.
потому что так быстрее
Можно тогда просто рандомные значения писать, еще быстрее будет.
Это не вопрос какой-то оптимизации, это вопрос правильной обработки данных. Поймите это.
Вы сами в статье писали "Для увеличения гранулярности блокировок можно было пойти дальше и дать возможность транзакциям, меняющим разные столбцы, обновлять совместно строку, но это не реализовано и вряд ли будет реализовано." Тут то же самое, только на уровне бизнес-процессов.
Нельзя делать бизнес-проверки и принимать решения об изменениях по неактуальным данным, которые могли измениться микросекунду назад в другом процессе. Сначала надо гарантировать, что данные будут актуальны до завершения всех проверок и изменений.
Логика «зачем процессу напрягаться»
У меня нет логики "зачем процессу напрягаться". Вы даже не понимаете о чем идет речь.
код на javascript, даже похоже на Node.js
Node.js это серверный процесс, но у меня просто псевдокод для пояснения.
Это ужасный способ, ни в коем случае нельзя так делать. Как заметили выше, могут быть ситуации, когда такую проверку сделать нельзя. Но тут дело даже не в этом, а в том, что проверка "add check (balance>=0)" не защитит от точно такого же двойного списания, когда баланс равен 2.
Единственный правильный способ при работе с ресурсом - блокировать ресурс от любых изменений в параллельном процессе, пока с ним выполняются операции в текущем процессе. В данном случае ресурс это строка таблицы, которая представляет какой-то бизнес-объект. Не должно быть никаких потерянных апдейтов типа "ну тут результат тот же самый, так что все нормально", потому что приложение в этом сценарии может потом делать инсерты в другую таблицы, и там будут дубликаты.
Можно использовать SELECT FOR UPDATE или блокировки вручную через get_lock/pg_try_advisory_lock (с таймаутом). Блокировки вручную удобнее, потому что для SELECT FOR UPDATE нужна транзакция на всё время обработки в приложении, и там могут происходить сетевые запросы. Лучше делать только одну блокировку на главный ресурс (агрегат в терминах DDD), то есть например на order, но не на отдельные order item.
покажите, что не защитит. Я говорю, что защитит. Процитируйте про «могут быть ситуации, когда такую проверку слелать нельзя», я не нашел
В самом первом комментарии.
покажите, что не защитит
В смысле? Вы не верите, что результат выражения balance = balance - 1 будет больше нуля, если balance равен 2?)
я не верю, что баланс станет отрицательным. Может ли транзакция в каком-то случае зафиксироваться так, чтобы баланс стал отрицательным?
В самом первом комментарии уже разобрались - автор не работал с PostgreSQL (от себя: и с Oracle тоже).
Причем тут PostgreSQL? Он говорит, что отрицательный баланс может быть разрешен по бизнес-требованиям.
чтобы баланс стал отрицательным
Я говорю про неправильную работу при положительном балансе. У меня на балансе миллион, я уменьшаю его на 1 в двух параллельных процессах. Ни при какой комбинации результат не будет меньше нуля, будет 999999 или 999998. При этом любой результат из этих двух может быть правильным в зависимости от сценария. Для оплаты за один заказ мы не хотим 2 списания, для снятия денег два раза хотим. Ваш constraint "balance >= 0" в этом случае не предотвращает неправильную обработку. Правильность зависит не от того, какой стал баланс, а от того, сколько раз нам надо выполнить действие.
В статье нет про двойное списание. Если менять условия задачи (а что, если нужен овердрафт, а что если два раза кнопку на веб-форме нажали и не хочу двойное списание), то из этого не следует: не используйте констрейнты, не используйте PostgreSQL. В статье нет призывов не использовать FOR UPDATE, не использовать повторяемость по чтению (она часто используется).
В статье говорится о том, что компании потерпели ущерб (Flexcoin на 600000$) и у них были деньги на создание качественного софта. Но компании так были поглощены модной в то время темой (за рамками статьи), что даже транзакции не использовали. И приведен пример, что даже констрейнт или правильный предикат устранил бы уязвимость. В статье можно найти разве что про то, что распределенные базы используют уровень serializable, а реляционные отстают. Но про это никто не написал (с транзакциями не сильно работали).
Ну как это нет, если вы его и пытаетесь предотвратить. Зачем иначе вам нужны эти проверки?
то из этого не следует: не используйте констрейнты
Следует. Предотвращение одновременных изменений из разных процессов решается мьютексами, а не констрейнтами. А если они у вас будут, то констрейнты не нужны.
Но компании так были поглощены модной в то время темой (за рамками статьи), что даже транзакции не использовали.
В описанном примере это связано не с транзакциями, а именно с отсутствием блокировок.
И приведен пример, что даже констрейнт или правильный предикат устранил бы уязвимость.
Ну я вам и объясняю, что нет. Уязвимость там была в том, что два процесса читали одинаковое изначальное значение баланса, и рассчитывали финальное значение без учета эффектов, которые другой процесс произведет после завершения. Если с баланса 100 списывать 2 раза по 10, то в этом случае финальное значение будет 90. Оно больше нуля, но оно неправильное, и ваша проверка "balance>=0" это не предотвратит.
Изменения ресурса надо делать только последовательно, это совершенно несложно, и не надо возиться с констрейнтами.
Зачем решать блокировками то, что решается констрейнтами? Констрейнты работают быстро и безошибочно ))
Владельцы компании предотвращали так:
What Will Be Done to Prevent Further Exploits?
Withdrawals and order creation have been switched to a queued method, where the first step is to add the task to a global execution queue that is processed sequentially. Each step of critical database operations is verified before proceeding, and such operations are in the process of being converted to transactions. I have hired additional developers to help with tightening up security at Poloniex, as well as created a bug bounty.
То есть создали очередь так, чтобы обработка шла одним процессом (sequentially) - то, что и делает блокировка, создавая узкое место. Но планируют использовать транзакции.
Если с баланса 100 списывать 2 раза по 10, то в этом случае финальное значение будет 90. Оно больше нуля, но оно неправильное, и ваша проверка "balance>=0" это не предотвратит.
Будет 90, если писать: select balance from accounts into ПЕРЕМЕННАЯ; update account set balance = ПЕРЕМЕННАЯ -10.
В статье написано: update account set balance = balance -10. Будет 80.
update всегда перечитывает строку после снятия блокировки. И никаких двойных или недостаточных списаний нет.
Зачем решать блокировками то, что решается констрейнтами?
Потому что блокировки это и есть инструмент для решения вопросов параллельного доступа, их для этого и придумали.
Я же вам объяснил, что это не решается констрейнтами. Ваш констрейнт "больше нуля" не предотвратит одновременное списание со 100 2 раза по 10, без блокировок будет 90, а не 80. А с блокировками констрейнт не нужен.
что и делает блокировка, создавая узкое место
Еще раз объясняю, это не узкое место, оно так и должно работать. А вы хотите сделать как в анекдоте "Я печатаю 1000 знаков в минуту, но такая фигня получается".
Это блокировка не на всю таблицу, а на одну конкретную строку. Процесс, который хочет прочитать другую строку, не будут приостановлен. Никакого узкого места тут нет, даже при большой нагрузке с одной конкретной строкой обычно работает не так много процессов, в большинстве случаев один. Два их становится обычно при намеренном действии.
В статье написано: update account set balance = balance -10. Будет 80.
Ну дак я о том и говорю, что защищает не констрейнт.
Конкретно с этим запросом да, будет 80, только вы еще рекомендуете добавлять условие WHERE balance >= 100. Поэтому первый процесс спишет 10, а во втором "balance >= 100" будет false, и списание не произойдет. Поэтому будет 90, хоть и не по той причине, которую я имел в виду.
Я имел в виду, что если делать так, как сделали FlexCoin, но добавить констрейнт. Если бы он защищал, то этого было бы достаточно.
Кроме того, с вашим запросом во втором процессе обновится 0 строк без всякой ошибки. То есть надо еще и в приложении проверять сколько строк обновилось и бросать исключение если 0.
Только смысла в этом нет, потому что если между ними будет еще третий процесс, который добавит 10 на баланс, то второй процесс списания прочитает 100, WHERE будет true, и второе списание произойдет. То есть вы создали плавающий баг на ровном месте.
Это решается 2 простыми строками, и стабильно работает с любой последовательностью действий, мне непонятно почему вы не хотите использовать предназначенный для этого инструмент, и хотите вместо этого усложнять запросы и объявления таблиц.
this.lockService.lock('Account', id);
...
this.lockService.unlock('Account', id);
Никакого узкого места тут нет, даже при большой нагрузке с одной конкретной строкой обычно работает не так много процессов, в большинстве случаев один. Два их становится обычно при намеренном действии.
да, при намеренном. И вы в приложении хотите создать уязвимость. Намереными действиями блокируются все процессы в пуле и приложение перестаёт обслуживать нормальных пользователей.
Конкретно с этим запросом да, будет 80, только вы еще рекомендуете добавлять условие WHERE balance >= 100. Поэтому первый процесс спишет 10, а во втором "balance >= 100" будет false, и списание не произойдет. Поэтому будет 90, хоть и не по той причине, которую я имел в виду.Я имел в виду, что если делать так, как сделали FlexCoin, но добавить констрейнт. Если бы он защищал, то этого было бы достаточно.
В статье я показал, что и констрейнт и предикат защищают от уязвимости. Напишите статью, где покажите, что не защищает - с примерами.
Кроме того, с вашим запросом во втором процессе обновится 0 строк без всякой ошибки. То есть надо еще и в приложении проверять сколько строк обновилось и бросать исключение если 0.
Только смысла в этом нет, потому что если между ними будет еще третий процесс, который добавит 10 на баланс, то второй процесс списания прочитает 100, WHERE будет true, и второе списание произойдет. То есть вы создали плавающий баг на ровном месте.
Приведите тесткейс бага. Обвинять без доказательств нехорошо. В WHERE указвается столько, сколько нужно списать с баланса. Если третий процесс добавит 100, то второй процесс правильно спишет с баланса.
О том, что нужно проверять, что выдала команда написано в статье. Вы предлагаете не проверять, а это неправильно.
Намереными действиями блокируются все процессы в пуле
Эти решения работают в разных компаниях в продакшене на больших нагрузках. Если у вас что-то блокируется, значит вы делаете что-то неправильно. Можно использовать блокировки в Redis, можно ставить веб-запросы в очередь, есть разные варианты.
Обычно ограничение идет еще на уровне веб-обработчиков в nginx или другом веб-сервере, они делаются по количеству ядер процессора, и остальные веб-запросы ждут, до использования подключений к базе даже не доходит.
Если же у вас 100 веб-подключений и 30 коннектов к базе, то загрузить их все можно независимо от того, есть ли у вас блокировки. Вообще, обработка в приложении обычно занимает значительно меньше, чем запросы в базу по сети, поэтому с блокировкой время использования одного коннекта к базе за один веб-запрос будет больше процентов на 10, а то и вообще в пределах 1%.
Отключать блокировки из-за большой нагрузки тем более нельзя, это как раз и создает случаи типа Flexcoin, DOS-атаки проводят в том числе и для этого.
В статье я показал, что и констрейнт и предикат защищают от уязвимости.
Вместе они защищают от одного конкретного сценария использования уязвимости, а не от уязвимости в целом. Я привел пример некорректной работы в предыдущем комментарии.
Напишите статью, где покажите, что не защищает - с примерами.
Зачем я буду писать статью из-за вашего комментария. Я вам уже привел примеры, они будут верными независимо от того, где они написаны, в статье или в комментарии.
Обвинять без доказательств нехорошо.
Мой пример это и есть доказательство. Если вы не можете по этому описанию написать конкретные SQL-команды для воспроизведения, возможно у вас недостаточно опыта, чтобы спорить на эти темы.
WHERE указывается столько, сколько нужно списать с баланса.
Да, и приложение это определяет запросом SELECT в начале выполнения. В обоих процессах списания оно будет одинаковое и равно 100, потому что по условию примера они запускаются в одну миллисекунду.
Если третий процесс добавит 100, то второй процесс правильно спишет с баланса.
Да. А если не добавит, то не спишет. Хотя баланса достаточно для обоих списаний. Плавающее нестабильное поведение.
Вы предлагаете не проверять, а это неправильно.
Потому что в моем подходе это не надо проверять, мы гарантируем, что с этим аккаунтом в данный момент работаем только мы. Значения в приложении и в базе синхронизированы. Код проще, SQL-запросы проще, и нет ошибок как у вас.
Приведите тесткейс бага.
Был Postgres под рукой, решил проверить.
SQL
create or replace function decreaseBalance(accountId int, diff decimal, sleep int) returns decimal language plpgsql as
$$
declare initialBalance decimal; finalBalance decimal;
begin
select balance from account where id = accountId into initialBalance;
perform pg_sleep(sleep);
update account set balance = balance - diff where balance >= initialBalance and id = accountId
returning balance into finalBalance;
return finalBalance;
end;
$$;
create or replace function increaseBalance(accountId int, diff decimal, sleep int) returns decimal language plpgsql as
$$
declare initialBalance decimal; finalBalance decimal;
begin
select balance from account where id = accountId into initialBalance;
perform pg_sleep(sleep);
update account set balance = balance + diff where balance <= initialBalance and id = accountId
returning balance into finalBalance;
return finalBalance;
end;
$$;
Если запускать все запросы одновременно с отдельных подключений, то получится так.
select increaseBalance(1, 10, 8) as res1;
select decreaseBalance(1, 10, 4) as res2;
select decreaseBalance(1, 10, 12) as res3;
-- 100 90 90
Так все запросы выполнятся, финальный баланс 100 - 10 + 10 - 10 = 90.
select increaseBalance(1, 10, 16) as res1;
select decreaseBalance(1, 10, 4) as res2;
select decreaseBalance(1, 10, 12) as res3;
-- 100 90 null
А так второе списание не выполнится, хотя отличается только порядок коммита транзакций, финальный баланс 100 - 10 - 0 + 10 = 100.
То есть если пользователь покупает товар в интернет-магазине и одновременно приходит списание за подписку на Netflix, то одна транзакция будет сбойная, и ее придется повторять.
select increaseBalance(1, 10, 16) as res1;
select decreaseBalance(1, 10, 2) as res2;
select decreaseBalance(1, 10, 2) as res3;
select increaseBalance(1, 20, 4) as res4;
-- null 90 null 110
А так не выполнится одно пополнение и одно списание, финальный баланс 100 - 10 - 0 + 20 + 0 = 110. То есть половина операций будут сбойные, при том что баланса достаточно для всех операций. Для высоконагруженного ресурса, который часто меняется, такие сбои транзакций будут происходить постоянно.
С блокировками все операции выполнятся без сбоев в порядке поступления.
На 3 картинке статьи, где единичка зелёная - она зелёная только в двух местах и результат первого select на картинке не используется. Вы же даёте select into и потом используете результат в update. На уровне изоляции по умолчанию это создаёт баг. И так писать нельзя. Писать надо так (в соответствии с третьей картинкой статьи):
update account set balance = balance - diff
where balance >= diff and id = accountId returning *Ведь так проще? И бага нет. Зачем до этого ставить select, какой в нём смысл? Также зачем две функции, когда можно обойтись одной.
К сожалению многие именно так и пишут. Плохо то, что много разработчиков, обучившихся на диких примерах, считают их правильными, что усугубляется эффектом Данцига-Крюгера и прочими когнитивными искажениями. :)
А, так у вас "1" в "balance >= 1" это diff, а не ожидаемое значение баланса! Из вашего описания в статье это непонятно.
Тогда непонятно, зачем вообще нужно это условие в WHERE, оно делает то же самое, что и констрейнт "balance >= 0", который проверяется после UPDATE.
Зачем до этого ставить select, какой в нём смысл?
Потому что он имитирует работу приложения. Это значение нужно в приложении для расчетов, логики, и вывода пользователю. Нам надо не только поменять значение в базе, но и сделать другие действия в зависимости от текущего значения баланса. Я же приводил вам примеры.
Зачем до этого ставить select, какой в нём смысл?
Вот еще один пример.
class AccountController {
public function withdraw(int $accountId, Money $amount) {
$account = $this->accountRepository->findOne($accountId, needLock: true);
if ($account === null) {
return $this->notFoundResponse();
}
$result = $this->accountService->withdraw($account, $amount);
$this->accountRepository->unlock($account);
if ($result instanceof ErrorList) {
return $this->validationErrorResponse($result);
}
$newAccountState = $result;
return $this->successResponse([
'id' => $newAccountState->id,
'balance' => $newAccountState->balance,
'currency' => $newAccountState->currency,
]);
}
}
class AccountService {
public function withdraw(Account $account, Money $amount): Account|ErrorList {
$validationErrors = $this->accountService->isWithdrawAllowed($account, $amount);
if ($validationErrors->isEmpty()) {
return $validationErrors;
}
$account->balance -= $amount->value;
$this->entityManager->save($account);
$this->sendMessageToKafka(['type' => MessageType::Withdraw, 'account' => $account]);
return $account;
}
private function isWithdrawAllowed(Account $account, Money $amount): ErrorList {
$errors = [];
if ($account->state === AccountState::FROZEN) {
$errors[] = 'Account is frozen';
}
if ($account->currency !== $amount->currency) {
$errors[] = 'Currency does not match';
}
if ($account->balance <= $amount->value) {
$errors[] = 'Insufficient balance';
}
return new ErrorList($errors);
}
}
class Account {
public int $id;
public Decimal $balance;
public Currency $currency;
public AccountState $state;
}
Покажите, как вы напишете эту логику без SELECT.
Как? Не писать на PHP. Обработка финансовой информации: SQL, PL/*, Java. Всё остальное - проблемы.
Если приходится писать и сложно объединить в одну команду, то можно использовать REPEATABLE READ. Но, обычно, в таких приложениях и базы данных без уровней, констрейнтов и отказоустойчивости. В таких случах можно писать как угодно, лишь бы заказчик принял работу. Я к тому, что ditry, ugly наработки не стоит описывать, как единственно лучшее и верное решение, даже если оно кажтся красивым. Многие поведутся на это и это распространится дальше. Без техподдержки товар с доставкой не закажешь ))
Как? Не писать на PHP.
При чем тут именно PHP? Вы не верите, что я могу на Java написать то же самое? Там отличие только в синтаксисе.
Я к тому, что ditry, ugly наработки не стоит описывать
Какие еще наработки?) Я вам задал конкретный вопрос, как в вашем подходе без SELECT написать код, поведение которого соответствует указанным бизнес-требованиям. Я не предлагаю вам использовать мои наработки, пишите тот код, который считаете нужным. Вы можете это сделать?
А, так у вас "1" в "balance >= 1" это diff, а не ожидаемое значение баланса! Из вашего описания в статье это непонятно.Тогда непонятно, зачем вообще нужно это условие в WHERE, оно делает то же самое, что и констрейнт "balance >= 0", который проверяется после UPDATE.
да, надо будет убрать. Условие делает другое… констрейнт не дает выходить балансу в минус. Условие where о другом: на балансе есть сумма снятия. Ставить лучше оба потому, что тут «избыточности» нет, тут вы не понимаете это другое «defence in depth”.
Аномалии конкурентного доступа