Параллелизм и сериализуемость Postgres. Нужен ли SERIALIZABLE уровень изоляции?

У меня есть таблица Items and Jobs:

Предметы

id = PK
job_id = Работы FK
статус = IN_PROGRESS | ПОЛНОЕ

работы

id = PK

Элементы начинаются как IN_PROGRESS, но работа выполняется над ними и передается работнику для обновления. У меня есть процесс обновления, который обновляет элементы по мере их поступления, с новым статусом. Подход, который я делал до сих пор, был (в псевдокоде):

def work(item: Item) = {
  insideTransaction {
    updateItemWithNewStatus(item)
    jobs, items = getParentJobAndAllItems(item)
    newJobStatus = computeParentJobStatus(jobs, items)
    // do some stuff depending on newJobStatus
  }
}

Имеет ли это смысл? Я хочу, чтобы это работало в параллельной среде. Проблема, которую я сейчас имею в виду, заключается в том, что COMPLETE многократно приходит на работу, когда я только хочу сделать логику на COMPLETE, один раз.

Если я изменил уровень транзакции на SERIALIZABLE, я получаю сообщение об ошибке "ERROR: не удалось выполнить сериализацию доступа из-за ошибок чтения/записи между транзакциями", как описано.

Поэтому мои вопросы:

Нужен ли мне SERIALIZABLE?
Могу ли я уйти с SELECT FOR UPDATE и где?
Может кто-нибудь объяснить мне, что происходит, и почему?

Edit: Я снова открыл этот вопрос, потому что меня не устраивало объяснение предыдущих ответов. Кто-нибудь может объяснить это мне? В частности, мне нужны некоторые примерные запросы для этого псевдокода.

Ответ 1

Если вы хотите, чтобы задания могли работать одновременно, ни SERIALIZABLE ни SELECT FOR UPDATE будут работать напрямую.

Если вы заблокируете строку, используя SELECT FOR UPDATE, тогда другой процесс будет просто блокироваться, когда он выполнит SELECT FOR UPDATE пока первый процесс не совершит транзакцию.

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

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

Еще один подход, который я вижу чаще, - это изменить свой столбец "статус", чтобы иметь третье временное состояние, которое используется во время обработки задания. Как правило, такие состояния должны быть такими, как "PENDING", "IN_PROGRESS", "COMPLETE". Когда ваш процесс ищет работу, он находит задания "ОЖИДАНИЕ", немедленно перемещает его в "IN_PROGRESS" и совершает транзакцию, затем продолжает работу и, наконец, переводит ее в "ПОЛНЫЙ". Недостатком является то, что если процесс умирает во время обработки задания, он будет оставаться в "IN_PROGRESS" на неопределенный срок.

Ответ 2

Вы можете использовать SELECT FOR UPDATE для items и jobs и работать с затронутыми строками в обеих таблицах в рамках одной транзакции. Этого должно быть достаточно для обеспечения целостности всей операции без накладных расходов SERIALIZABLE или блокировки таблицы.

Я предлагаю вам создать функцию, которая вызывается после того, как в таблицу items добавлена вставка или обновление, передав PK элемента:

CREATE FUNCTION process_item(item integer) RETURNS void AS $$
DECLARE
    item items%ROWTYPE;
    job  jobs%ROWTYPE;
BEGIN  -- Implicitly starting a transaction
    SELECT * INTO job FROM jobs
    WHERE id = (SELECT job_id FROM items WHERE id = item)
    FOR UPDATE;  -- Lock the row for other users

    FOR item IN SELECT * FROM items FOR UPDATE LOOP      -- Rows locked
        -- Work on items individually 

        UPDATE items
        SET status = 'COMPLETED'
        WHERE id = item.id;
    END LOOP;

    -- Do any work on the job itself
END;  -- Implicitly close the transaction, releasing the locks
$$ LANGUAGE plpgsql;

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