Денежные операции в БД: типы, транзакции и баланс

Прямой ответ

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

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 100.00
WHERE id = 42 AND balance >= 100.00;
INSERT INTO operations (account_id, amount) VALUES (42, -100.00);
COMMIT;

Блокировка строки удерживается до завершения транзакции, поэтому параллельное списание ожидает завершения и не читает прежнее значение баланса. О типах — в статье про деньги в приложении.

Тип столбца и точность хранения

Тип столбца определяет точность хранения суммы, поэтому выбор типа влияет на представление разменных единиц.

Числовой тип с фиксированной точностью. Значение хранится с указанным числом знаков, поэтому разменные единицы представляются точно.

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

Двоичный тип с плавающей точкой. Значение хранится приближённо, поэтому арифметика вносит погрешность в последних разрядах.

Практическое следствие: сумма, хранящаяся в двоичном типе, накапливает погрешность при арифметике, поэтому итог по операциям расходится с ожидаемым.

Отдельный аспект — разрядность столбца. Число знаков после запятой в схеме должно покрывать минимальную единицу валюты, поэтому разрядность недостаточная приводит к округлению при записи.

Транзакция и блокировка строки баланса

Изменение баланса выполняется в транзакции с блокировкой строки, поэтому параллельные операции не приводят к потере списания.

Транзакция. Списание баланса и запись операции выполняются в одной транзакции, поэтому частичное применение исключается.

Блокировка строки. Строка баланса блокируется до завершения транзакции, поэтому параллельная операция ожидает завершения.

Условие достаточности. Проверка баланса в условии обновления исключает списание сверх доступной суммы, поэтому баланс не становится отрицательным.

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

Проверка согласованности баланса

Согласованность баланса проверяется сравнением значения с суммой операций, поэтому расхождение указывает на потерю операции.

Сумма операций. Баланс равен сумме записанных операций, поэтому расхождение указывает на операцию, не отражённую в балансе.

Проверка по счёту. Сравнение выполняется по каждому счёту, поэтому расхождение локализуется до конкретного счёта.

Отрицательный баланс. Значение ниже нуля указывает на списание без проверки достаточности, поэтому условие обновления требует проверки.

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

Об округлении сумм — в статье про округление денежных значений; об уровнях изоляции — в статье про транзакции PostgreSQL.

Практика: реализация денежных операций

1. Храните сумму в числовом типе с фиксированной точностью. Разменные единицы представляются точно, поэтому погрешность не накапливается.

2. Задавайте разрядность столбца под минимальную единицу валюты. Недостаточная разрядность приводит к округлению при записи.

3. Выполняйте списание в транзакции. Списание баланса и запись операции применяются атомарно, поэтому частичное применение исключается.

4. Блокируйте строку баланса при списании. Параллельная операция ожидает завершения, поэтому прежнее значение не читается.

5. Проверяйте достаточность средств в условии обновления. Баланс не становится отрицательным, поэтому списание сверх суммы исключается.

6. Проверяйте баланс против суммы операций. Расхождение указывает на операцию, не отражённую в балансе.

7. Фиксируйте валюту счёта. Сумма однозначно относится к валюте, поэтому смешение сумм исключается.

Рекомендации:

1. Не храните денежные суммы в двоичном типе с плавающей точкой. Погрешность накапливается при арифметике, поэтому итог расходится.
2. Учитывайте уровень изоляции при параллельных списаниях. Блокировка строки исключает чтение прежнего значения баланса.
3. Сопоставляйте разрядность столбца с валютой счёта. Разрядность должна покрывать минимальную единицу валюты.
4. Фиксируйте проверку согласованности в регулярной задаче. Расхождение баланса обнаруживается до формирования отчётности.
5. Пересматриваем схему при добавлении валюты. Новая валюта может требовать иной разрядности столбца.

Практический сценарий: списание терялось при параллельных запросах

Контекст: баланс счёта расходился с суммой операций, при этом каждая операция записывалась в журнал.

1. Диагностика. Проверка показала, что два списания выполнялись одновременно. Каждое читало значение баланса до завершения другого, поэтому второе списание применялось к прежнему значению и одно списание терялось.
2. Причина. Строка баланса не блокировалась при списании, поэтому параллельные операции читали одно и то же значение и перезаписывали результат друг друга.
3. Действия. (а) Добавили блокировку строки баланса при списании, чтобы параллельная операция ожидала завершения транзакции; (б) объединили списание баланса и запись операции в одну транзакцию, чтобы частичное применение исключалось; (в) добавили условие достаточности средств в обновление, чтобы баланс не становился отрицательным; (г) настроили регулярную проверку баланса против суммы операций, чтобы расхождение обнаруживалось; (д) проверили разрядность столбца суммы и убедились, что она покрывает минимальную единицу валюты.
4. Результат. Баланс совпадает с суммой операций, поскольку параллельные списания выполняются последовательно и каждая операция отражается в балансе.

Вывод: списание без блокировки строки баланса приводит к чтению прежнего значения при параллельных операциях, поэтому одна из операций теряется. Блокировка строки и объединение списания с записью операции в транзакцию исключают потерю.

FAQ

Какой тип столбца выбрать для денежной суммы? Числовой тип с фиксированной точностью. Двоичный тип с плавающей точкой вносит погрешность, накапливающуюся при арифметике.

Зачем блокировать строку баланса при списании? Параллельная операция читает прежнее значение баланса без блокировки, поэтому одно из списаний теряется.

Как исключить списание сверх доступной суммы? Условием достаточности средств в обновлении. Баланс не становится отрицательным, поэтому списание сверх суммы отклоняется.

Как проверить согласованность баланса? Сравнением значения баланса с суммой записанных операций. Расхождение указывает на операцию, не отражённую в балансе.

Источники: PostgreSQL numeric types, PostgreSQL money type, PostgreSQL transaction isolation — ссылки в шапке.