multithreading - 对于从作业队列中拉出运行的工作人员，我应该使用什么隔离级别？

Question

我有一个 PostgreSQL 数据库 (v9.5.3)，它托管“作业”供工作人员拉取、运行和提交。

当一个 worker 想要一份工作时，它会运行一些具有以下效果的东西:

SELECT MIN(sim_id) FROM job WHERE job_status = 0;
-- status 0 indicates it's ready to be run

job 是具有此架构的表:

CREATE TABLE commit_schema.job (
  sim_id serial NOT NULL,
  controller_id smallint NOT NULL,
  controller_parameters smallint NOT NULL,
  model_id smallint NOT NULL,
  model_parameters smallint NOT NULL,
  client_id smallint,
  job_status smallint DEFAULT 0,
  initial_glucose_id smallint NOT NULL
);

然后，它使用这个 sim_id 将一堆参数拼凑成一个 JOIN:

SELECT a.par1, b.par2 FROM
a INNER JOIN b ON a.sim_id = b.sim_id;

然后将这些参数与 sim_id 一起返回给工作人员，然后运行作业。 sim_id 通过使用 UPDATE 将 job.job_status 设置为 1 来锁定:

UPDATE job SET job_status = 1 WHERE sim_id = $1;

然后使用相同的 sim_id 提交结果。

理想情况下，

1. worker 在任何情况下都无法获得相同的 sim_id。

2. 两个 worker 请求工作不会出错，一个只需要等待接收工作。

我认为使用可序列化隔离级别将确保 MIN() 始终返回唯一的 sim_id，但我相信这也可以通过读取来实现提交的隔离级别。然后，MIN() 可能无法同时并确定性地为两个并发工作人员提供唯一的 sim_id？

Answer 1

使用默认隔离级别Read Committed，这对于并发访问应该工作得很好和 FOR UPDATE SKIP LOCKED (PG 9.5 中的新功能):

UPDATE commit_schema.job j
SET    job_status = 1
FROM  (
   SELECT sim_id
   FROM   commit_schema.job
   WHERE  job_status = 0
   ORDER  BY sim_id
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
   ) sub
WHERE  j.sim_id = sub.sim_id
RETURNING sim_id;

job_status 应该定义为 NOT NULL。

警惕某些极端情况 - 在 dba.SE 上的相关答案中有详细解释:

• Postgres UPDATE … LIMIT 1

地址your comment

有多种方法可以从函数返回:

• 使它成为一个简单的 SQL 函数而不是 PL/pgSQL。
• 或使用RETURN QUERY使用 PL/pgSQL。
• 或者用RETURNING ... INTO将结果赋给一个变量- 可以是 OUT 参数，所以它会在函数结束时自动返回。或任何其他变量并显式返回它。

相关(带代码示例):

• Can I make a plpgsql function return an integer without using a variable?
• Returning from a function with OUT parameter