Atomic UPDATE.. SELECT в Postgres

Ответ 1

В то время как предложение Эрвина, возможно, является самым простым способом получить правильное поведение (при повторном выполнении вашей транзакции, если вы получаете исключение с SQLSTATE из 40001), приложения для очередей по своему характеру, как правило, работают лучше с запросами, блокирующими шанс сделать свою очередь в очереди, чем с реализацией PostgreSQL транзакций SERIALIZABLE, что позволяет использовать более высокий concurrency и несколько более "оптимистично" о шансах столкновения.

Пример запроса в вопросе, как он есть, на уровне изоляции транзакции по умолчанию READ COMMITTED позволил бы двум (или более) параллельным соединениям "требовать" одну и ту же строку из очереди. Что произойдет:

• T1 запускается и доходит до блокировки строки в фазе UPDATE.
• T2 перекрывает T1 во время выполнения и пытается обновить эту строку. Он блокирует ожидающие COMMIT или ROLLBACK T1.
• T1 совершает, успешно выполнив "строку".
• T2 пытается обновить строку, обнаружив, что T1 уже имеет, ищет новую версию строки, находит, что она по-прежнему удовлетворяет критериям выбора (именно это соответствует id), а также "утверждает" строки.

Он может быть изменен для правильной работы (если вы используете версию PostgreSQL, которая разрешает предложение FOR UPDATE в подзапросе). Просто добавьте FOR UPDATE в конец подзапроса, который выбирает идентификатор, и это произойдет:

• T1 запускается и теперь блокирует строку перед выбором идентификатора.
• T2 перекрывает T1 во время выполнения и блокирует при попытке выбрать id, ожидая COMMIT или ROLLBACK T1.
• T1 совершает, успешно выполнив "строку".
• К моменту, когда T2 может прочитать строку, чтобы увидеть идентификатор, он видит, что он был заявлен, поэтому он находит следующий доступный идентификатор.

На уровне изоляции транзакции REPEATABLE READ или SERIALIZABLE конфликт записи вызовет ошибку, которую вы могли бы поймать и определить, был сбой сериализации на основе SQLSTATE и повторить попытку.

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

Ответ 2

Если вы являетесь только пользователем, запрос должен быть точным. В частности, в самом запросе нет состояния гонки или тупика (между внешним запросом и подзапросом). Я цитирую руководство здесь:

Однако транзакция никогда не конфликтует с самим собой.

Для одновременного использования вопрос может быть более сложным. Вы были бы в безопасности с SERIALIZABLE режимом транзакции:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
RETURNING * 
COMMIT;

Вам нужно подготовить для сбоев сериализации и повторить запрос в таком случае.

Но я не совсем уверен, что это не слишком много. Я попрошу @kgrittn остановиться... он эксперт с concurrency и сериализуемыми транзакциями.. и он это сделал.:)

Лучшее из обоих миров

Запустите запрос в режиме транзакции по умолчанию READ COMMITTED и явно проверьте условие computed IS NULL во внешнем UPDATE:

UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
AND   computed IS NULL;

Как пояснил @kgrittn в комментарии к его ответу, этот запрос может возникнуть пустым, не сделав ничего, в (маловероятном) случае он переплетается с параллельной транзакцией.

Следовательно, он будет работать так же, как первый вариант в режиме транзакции SERIALIZABLE, вам придется повторить попытку - просто без штрафа за производительность.

Единственная проблема: в то время как конфликт очень маловероятен, потому что окно возможностей настолько крошечное, что это может произойти при большой нагрузке. Вы не могли точно сказать, осталось ли больше строк.

Если это не имеет значения (как в вашем случае), вы здесь. Если это произойдет, если быть абсолютно уверенным, запустите еще один запрос с явным блокированием после получения пустого результата. Если это становится пустым, все готово. Если нет, продолжайте.
В plpgsql он может выглядеть так:

LOOP
   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
   AND    computed IS NULL;

   CONTINUE WHEN FOUND;  -- continue outside loop, may be a nested loop

   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1 FOR UPDATE);
-- AND    computed IS NULL; -- redundant here

   EXIT WHEN NOT FOUND;  -- exit function (end)
END LOOP;

Это должно дать вам лучшее из обоих миров: производительность и надежность.