Как предотвратить аномалии обновления с несколькими клиентами, выполняющими неатомарные вычисления одновременно в PostgreSQL?

Question

Я использую три экземпляра PostgreSQL с использованием репликации (1 ведущий, 2 ведомых), к которым обращаются два отдельных сервера:

• Первый (неэкспонированный) сервер в основном выполняет итерации по каждой строке в конкретной таблице и непрерывно обновляет определенные столбцы (ресурсы) каждый тик (в зависимости от производительности этих ресурсов) для каждого пользователя.
• Второй сервер является общедоступным API, который предоставляет различные функции, такие как расходование определенного количества этих ресурсов.

Для доступа к данным и манипулирования ими я использую библиотеку ORM, которая позволяет мне писать код следующим образом:

const resources = await repository.findById(1337);
// some complex computation
resources.iron = computeNewIron(resources.iron);
await repository.save(resources);

Конечно, может случиться так, что API захочет вычесть определенное количество ресурсов прямо, когда сервер, обрабатывающий тики, пытается обновить количество ресурсов, что может заставить любой из серверов принять определенное количество ресурсов, которое является неправильным, в основном Ваша типичная аномалия ОБНОВЛЕНИЯ.

Моя проблема в том, что я не просто пишу "простой" атомарный запрос, такой UPDATE table SET iron = iron + 42 WHERE id = :id, Библиотека ORM внутренне использует прямое присваивание, которое не ссылается на соответствующие столбцы, что приводит к чему-то похожему на UPDATE table SET iron = 123 WHERE id = :id где сумма была вычислена ранее.

Я могу только предположить, что возможно предотвратить упомянутую аномалию, если я использую написанные вручную запросы, которые увеличивают / уменьшают значения атомарно с помощью собственных ссылок. Я хотел бы знать, какие другие варианты могут облегчить проблему. Должен ли я обернуть мой SELECT/ Computation/UPDATE в транзакции? Достаточно ли этого?

Answer 1

Ваш вопрос немного неясен, но если ваша транзакция охватывает несколько операторов, но при этом должна иметь согласованное состояние базы данных, есть два основных варианта:

1. Используйте пессимистическую блокировку: когда вы читаете значения из базы данных, делайте это с SELECT ... FOR UPDATE, Затем строки блокируются на время вашей транзакции, и никакая параллельная транзакция не может их изменить.

2. Используйте оптимистическую блокировку: начните транзакцию в REPEATABLE READ уровень изоляции. Затем вы видите непротиворечивый снимок базы данных на весь период вашей транзакции. Если кто-то еще изменил ваши данные после того, как вы их прочитали, ваш UPDATE вызовет ошибку сериализации, и вам придется повторить транзакцию.

Оптимистическая блокировка лучше, если конфликты редки, а пессимистическая блокировка предпочтительнее, если конфликты вероятны.