sql - Postgres 并发性和可串行化。我需要 SERIALIZABLE 隔离级别吗？

Question

我有一个 Items 和 Jobs 表:

项目

• id = PK
• job_id = 工作 FK
• 状态 = 进行中 |完成

工作

• 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，我确实会收到“错误:由于事务之间的读/写依赖性而无法序列化访问”错误，如所述。

所以我的问题是:

• 我需要 SERIALIZABLE 吗？
• 我可以使用 SELECT FOR UPDATE 吗？在哪里？
• 谁能给我解释一下发生了什么，为什么？

编辑:我重新打开了这个问题，因为我对之前的答案解释不满意。有人能为我解释一下吗？具体来说，我想要一些针对该伪代码的示例查询。

Answer 1

您可以在 items 和 jobs 上使用 SELECT FOR UPDATE，并在单个事务中处理两个表中受影响的行。这应该足以强制执行整个操作的完整性，而无需 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 不同，它会一直工作直到失败，然后您必须在第二次尝试中重新执行所有处理。