Запретить одновременное обновление строки базы данных Postgresql

Question

Запретить одновременное обновление строки базы данных Postgresql

У меня есть запрос, как показано ниже, чтобы увеличить количество транзакций после каждой транзакции, проходящей через определенный шлюз. Приведенный ниже запрос используется в приложении PHP, которое выполняет задания по обслуживанию транзакций. Как убедиться, что два одновременных обновления не создают проблемы при подсчете?

Подумал об использовании консультативных блокировок PostgreSQL, но не уверен, как его использовать в следующем контексте. Есть несколько ссылок на Java, но относительно новый в PHP.

Запрос 1 (чтобы найти запись для обновления):

SELECT id
FROM Client.gatewaystat_tbl
WHERE clientid = 1 AND gatewayid = 2 ;

Если запись найдена:

UPDATE gatewaystat_tbl gt
SET statvalue = statvalue + 1
WHERE id = '<from above query>'

Если запись не найдена:

INSERT INTO client.gatewaystat_tbl
(gatewayid, clientid, statetypeid, statvalue)
VALUES (2, 1, 1, 1);

Приведенный выше код может вызываться несколько раз в течение секунды, так как транзакции будут происходить с очень большим объемом в часы пик и после каждой транзакции счетчик (statvalue) нужно увеличить. Как сделать так, чтобы несколько потоков не пытались одновременно обновлять счет и приводить к ошибочным данным?

0

concurrency php postgresql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Нет обновления для statvalue может потеряться, потому что UPDATE атомно. Строка будет заблокирована перед оператором, и блокировка будет снята только после фиксации транзакции.

Но пока вы не можете потерять обновление statvalueмогут возникнуть другие проблемы:

Параллельная транзакция может обновить или удалить строку между SELECT и UPDATE,
Параллельная транзакция может вставить конфликтующую строку между SELECT и INSERT,

Вы можете защитить себя от первой проблемы, используя SELECT ... FOR UPDATE который заблокирует строку, но вы должны быть готовы к обработке ошибок во время INSERT,

Если у вас есть первичный ключ или уникальное ограничение на (gatewayid, clientid)Вы можете сделать все это в одном выражении и избежать всех этих проблем:

INSERT INTO client.gatewaystat_tbl
(gatewayid, clientid, statetypeid, statvalue)
VALUES (2, 1, 1, 1)
ON CONFLICT (gatewayid, clientid) DO UPDATE
SET statvalue = statvalue + 1;

0