Postgres update после выбора

Я хочу сделать следующее за один раз:

SELECT * FROM jobs WHERE status='PENDING';
UPDATE jobs SET status='RUNNING' WHERE status='PENDING';

Итак, получите все ожидающие задания, затем установите их как "RUNNING" сразу после.

Причина, по которой я не хочу делать это один за другим в двух утверждениях, заключается в том, что задания могут быть добавлены в таблицу заданий как "ОЖИДАНИЕ" после SELECT, но до UPDATE, поэтому я бы поставил задания как РАБОТАЙ, даже если я не схватил его, пока он находился в состоянии PENDING.

Есть ли способ сделать это в одном? Поэтому я хочу, чтобы результат SELECT и UPDATE выполнялся "на лету".

Спасибо.

Ответ 1

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

Предполагая, что вы используете настройку по умолчанию для Read Committed, вот что он говорит:

Read Committed - это уровень изоляции по умолчанию в PostgreSQL. Когда транзакция выполняется на этом уровне изоляции, запрос SELECT видит только данные, выполненные до начала запроса;

И что касается UPDATE:

Команды UPDATE, DELETE, SELECT FOR UPDATE и SELECT FOR SHARE вести себя так же, как SELECT с точки зрения поиска целевых строк: они будут найдены только целевые строки, которые были зафиксированы с момента запуска команды время. Однако такая целевая строка может быть уже обновлена (или удалена или заблокирована) другой параллельной транзакцией к моменту ее найденный. В этом случае потенциальный обновитель будет ждать первого обновление транзакции для фиксации или откат (если оно все еще находится в прогресс). Если первый обновитель откатится назад, то его эффекты и второй обновитель может продолжить обновление первоначально найденная строка. Если первый обновитель завершится, второй обновитель будет игнорировать строку, если первый обновитель удалит ее, иначе она будет попытайтесь применить свою операцию к обновленной версии строки. условие поиска команды (предложение WHERE) переоценивается для посмотрите, соответствует ли обновленная версия строки поиску состояние. Если это так, второй обновитель продолжает свою работу, начиная с обновленной версии строки. (В случае SELECT FOR UPDATE и SELECT FOR SHARE, это означает, что это обновленная версия строки, которая заблокирована и возвращается клиенту.)

Итак, в вашем сценарии одно UPDATE должно быть в порядке.

Имейте в виду, что существует так называемый оператор SELECT FOR UPDATE, который блокирует выбранные вами строки. Вы можете прочитать об этом здесь. Сценарий, в котором вам нужно будет использовать эту функцию, будет в системе резервирования. Рассмотрим этот пример:

Выполнить SELECT, чтобы узнать, доступен ли номер XYZ для резервирования даты X.
Номер доступен. Выполните запрос UPDATE, чтобы забронировать номер.

Вы видите потенциальную проблему здесь? Если между шагами 1 и 2 комната забронирована другой транзакцией, тогда, когда мы достигнем шага 2, мы работаем над предположением, которое уже недействительно, а именно, что комната доступна.

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

Но опять же, в вашем сценарии этот SELECT FOR UPDATE не нужен, потому что вы делаете все в одном утверждении и ничего не проверяете заранее.

Ответ 2

Почему бы не использовать предложение RETURNING и обработать обе вещи в одном объявлении:

UPDATE jobs 
    SET status='RUNNING' 
WHERE status='PENDING'
RETURNING *

Таким образом вы получите все строки, которые были изменены UPDATE с помощью одной атомной операции.

Ответ 3

begin;
select * 
from jobs 
where status='pending'
for update
;
update jobs 
set status='running' 
where status='pending';
commit;