Атомарное ОБНОВЛЕНИЕ .. ВЫБРАТЬ в Postgres

Хотя предложение Эрвина, возможно, является простейшим способом добиться правильного поведения (при условии, что вы повторно попытаетесь выполнить транзакцию, если вы получите исключение с SQLSTATE из 40001), приложения с очередями по своей природе, как правило, лучше работают с блокировкой запросов. для возможности занять очередь в очереди, чем с реализацией SERIALIZABLE транзакций PostgreSQL, которая допускает более высокий уровень параллелизма и несколько более «оптимистична» в отношении шансов столкновения.

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

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

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

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

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

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

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

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

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

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

И он это сделал. :)

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

Выполните запрос в режиме транзакции по умолчанию READ COMMITTED.

Для Postgres 9.5 или новее используйте FOR UPDATE SKIP LOCKED. Видеть:

• ОБНОВЛЕНИЕ Postgres… ОГРАНИЧЕНИЕ 1

Для более старых версий перепроверьте условие 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 FOR UPDATE SKIP LOCKED);  -- pg 9.5+
   -- WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
   -- AND    computed IS NULL; -- pg 9.4-

   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);

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

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