Обеспечение согласованности баланса в таблице транзакций — PostgreSQL
В базовом банковском приложении у нас есть таблица под названием «транзакции», которая включает столбцы «сумма» и «баланс». В среде вставки данных с высокой степенью параллелизма я хочу обновить столбец баланса, добавив следующее значение: текущая сумма + баланс предыдущей записи.
Пример:
таблица: транзакции
Мы попытались использовать приведенный ниже триггер для достижения этой цели. Однако при запуске нескольких процессов, создающих новые транзакции, результаты становятся противоречивыми.
CREATE OR REPLACE FUNCTION update_balance() RETURNS TRIGGER AS $$
BEGIN
UPDATE transactions
SET balance = NEW.amount + COALESCE((SELECT balance FROM transactions WHERE id = NEW.id - 1), 0)
WHERE id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_balance_trigger
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_balance();
Мы рассматриваем возможность постановки процесса обновления в очередь, но обеспокоены тем, что это не решит проблемы согласованности и может ухудшить производительность.
Как мы можем поддерживать последовательность и производительность?
Ответы 1
В Oracle есть функция, с помощью которой вы можете заблокировать строки , которые планируете обновить, чтобы сохранить целостность данных. Читая, я увидел, что PostgreSQL имеет аналогичную функцию и аналогичный синтаксис.
Судя по описанию, механизм кажется идентичным, и, судя по моему опыту работы с Oracle, у нас никогда не было несоответствий при его использовании, поэтому я ожидаю, что и в PostgreSQL у вас их не будет. Поскольку вы работаете с балансами, вам придется ждать изменений, иначе использование NOWAIT вернет ошибку, и вы можете «потерять» запрос на обновление баланса.
Что касается вашего другого подхода, у нас есть таблица пользовательских операций (где практически любой щелчок в приложении делает «что-то»), которая использует подход с организацией очередей. С точки зрения производительности я бы не сказал, что это повлечет за собой накладные расходы (кроме обычных в таких ситуациях), хотя профилирование использования всегда является лучшим подходом, а не предположением, однако, поскольку вы ставите в очередь обновление баланса, и если для вашего клиента выполняется много транзакций, тогда он увидит реальную величину баланса своего банковского счета позже, а не в реальном времени. Сколько «позже»? Это зависит от ситуации (опять же, вам нужно будет провести несколько тестов производительности, чтобы получить правильную оценку).
А пока попробуйте заблокировать строки с помощью SELECT 1 FROM transactions WHERE id = X FOR UPDATE. Очевидно, не забудьте отключить триггер.
Вы можете попробовать заблокировать NOWAIT и поставить в очередь только «потерянные» обновления, поскольку обычно вы обновляете каждую учетную запись отдельно, поэтому имеет смысл иметь эксклюзивные блокировки для каждой учетной записи, которая нуждается, и обновлять и ставить в очередь эту строку учетной записи, если ей нужно больше. последующие обновления для нескольких обновлений. Опять же, вам нужно будет проверить, является ли их объединение подходящим подходом для этого варианта использования.
Хотя мой опыт работы связан с Oracle, а не с PostgreSQL, я вижу, что у них есть тот же механизм, который можно было бы использовать и в Oracle: эксклюзивные блокировки строк. Я подробно это описал в своем ответе. Имейте в виду, что любой тип «защиты», обеспечивающий целостность, требует некоторых накладных расходов. От ограничений столбцов до триггеров и многого другого. Таким образом, производительность не будет одинаковой, независимо от того, какой подход вы выберете. Главное — увидеть результаты тестирования производительности и найти лучший подход.