python - postgresql 中具有锁定和选择更新的一致性

Question

我有一个可以支持一定数量的并发操作的应用程序。这由 postgres 中的“插槽”表表示。当节点上线时，它们会在表中插入多行，每个槽一行。当作业认领插槽时，它们会更新表中的一行以认领其中一个插槽，并在完成时再次释放它。

插槽表如下所示:

CREATE TABLE slots (
    id INT8 PRIMARY KEY DEFAULT nextval('slots_seq'),
    node_name TEXT NOT NULL,
    job_name TEXT
);

在任何时候，它都有一些半固定数量的行，每行可能会或可能不会填写 job_name。

当一个新作业想要启动时，它会运行这些查询来获取它应该运行的节点的名称:

BEGIN;
LOCK TABLE slots IN ACCESS EXCLUSIVE MODE;
SELECT id, node_name
    FROM slots
    WHERE job_name IS NULL
    LIMIT 1
    FOR UPDATE;

(从游标中读出node_name和id)

UPDATE slots
    SET job_name = %(job_name)s
    WHERE id = %(slot_id)s;
COMMIT;

这通常能够在不丢失任何更新的情况下声明行，但具有更高级别的并发性，只有少数行将被声明，而许多 SELECT ... FOR UPDATE 和 UPDATE 查询已被执行。最终结果是，我们最终运行的作业远远多于它们的插槽。

我是否犯了锁定错误？有没有更好的方法来解决这个问题？不使用表锁的东西？

事务级别 SERIALIZABLE 不会削减它，只会填充少数行。

我使用的是 postgresql 8.4 版。

Answer 1

BEGIN; 
LOCK TABLE slots IN ACCESS EXCLUSIVE MODE; 
UPDATE slots SET job_name = '111' WHERE id IN (SELECT id FROM slots WHERE job_name IS NULL LIMIT 1) RETURNING *;
COMMIT;

这似乎适用于 Read Committed。它只是 sql(与您的代码相同)并且可以在一次调用中执行(更快)。

@Seth Robertson:没有 LOCK TABLE 和 while 循环是不安全的。

如果同时存在事务A和事务B:A会选择第一行，B会选择第一行。 A 将锁定并更新行，B 必须等到 A 提交。然后 B 将重新检查条件 job_name IS NULL。它为 false，B 不会更新 - B 不会选择下一行，只会重新检查并返回空结果。

@joegester:SELECT FOR UPDATE 不是问题，因为所有表都已锁定。

也许还有另一种方法可以完成这项工作 - 如果您删除并插入行(在其他表中？)而不是设置 NULL。但我不确定如何。