У меня типичная проблема производителя и потребителя:
Несколько приложений-производителей записывают запросы на задания в таблицу заданий в базе данных PostgreSQL.
Запросы заданий имеют поле состояния, которое начинается с QUEUED при создании.
Есть несколько потребительских приложений, которые уведомляются по правилу, когда производитель вставляет новую запись:
CREATE OR REPLACE RULE "jobrecord.added" AS
ON INSERT TO jobrecord DO
NOTIFY "jobrecordAdded";
Они попытаются зарезервировать новую запись, установив ее состояние в положение RESERVED. Конечно, только на потребителя должно получиться. Все остальные потребители не должны резервировать одну и ту же запись. Вместо этого они должны резервировать другие записи с состоянием = QUEUED.
Пример: некоторые производители добавили следующие записи в таблицу jobrecord:
id state owner payload
------------------------
1 QUEUED null <data>
2 QUEUED null <data>
3 QUEUED null <data>
4 QUEUED null <data>
теперь два потребителя A, B хотят их обработать. Они начинают работать одновременно. Нужно зарезервировать идентификатор 1, другой должен зарезервировать id 2, тогда первый, кто закончит, должен зарезервировать id 3 и т.д.
В чистом многопоточном мире я бы использовал мьютекс для контроля доступа к очереди заданий, но потребители - это разные процессы, которые могут выполняться на разных машинах. Они получают доступ только к одной базе данных, поэтому вся синхронизация должна выполняться через базу данных.
Я прочитал много документации о параллельном доступе и блокировке в PostgreSQL, например. http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html Выберите разблокированную строку в Postgresql PostgreSQL и блокировка
Из этих тем я узнал, что следующий SQL-оператор должен делать то, что мне нужно:
UPDATE jobrecord
SET owner= :owner, state = :reserved
WHERE id = (
SELECT id from jobrecord WHERE state = :queued
ORDER BY id LIMIT 1
)
RETURNING id; // will only return an id when they reserved it successfully
К сожалению, когда я запускаю это в нескольких потребительских процессах, примерно в 50% случаев они по-прежнему сохраняют одну и ту же запись, обрабатывая ее и переписывая изменения другой.
Что мне не хватает? Как мне написать инструкцию SQL, чтобы несколько пользователей не оставляли одну и ту же запись?