如何在SQL中的两个步骤中迈出一步 - 用户同时工作

Question

目前我在一个系统中为我的问题实现了解决方案。但我认为存在更好的解决方案，但我还没有找到它。我希望有人能在我身上找到或暗示一些二重奏。

我有拆分不同类型工作的系统。每个工作都在PostgreSQL数据库中的表"jobs"中，其中一行是一个作业(可以多次完成(。每个作业都有一个属性，可以完成多少次(有多少用户可以处理它(。所以我的表作业是这样的：

ID_JOB NAME DONE HAS_TO_BE_DONE
1 Puzzle Solving 2  3
2 Washing Dishes 1 3

当用户来找工作时：

1. 当作业满足条件时，用户将获得作业的 IDDONE < HAS_TO_BE_DONE
2. 对于给定的作业值，完成递增 (+1 (

它可以工作，但是当更多用户同时工作时，我需要每次锁定(类型：可序列化(数据库(甚至用于读取(。

如果我不使用数据库锁定，可能会发生： 用户 #1 来了 - 他在步骤 1 中(他知道他得到的工作的 ID(，在同一时间(几毫秒后(来了用户 #2 并将请求工作并将在步骤 1 中，并将获得与用户 #1 相同的工作，因为 #User 1 尚未管理步骤 2(并且没有递增完成(。这就是我必须完全锁定数据库的原因。

有谁知道如何改进这种方法？ 目前它可以工作，但是当 100 个用户同时工作时，数据库锁定会导致应用程序变慢。问题是这两个步骤，第一步我会为用户找到工作，然后在第二步中我增加值。

如果有人能提供更好的解决方案，我会很高兴。谢谢

Answer 1

最简单的方法是在单个语句中进行检查和更新。

update jobs
set done = done + 1
where done < has_to_be_done
and id = ?

如果它更新了一行，则有工作要做。如果没有，就没有工作要做。

---

但是有一个问题。你只有两种状态：工作需要完成，工作完成。如果 5 个用户都在从事同一工作，但只需要完成两次怎么办？这是浪费。您需要三种状态：

• 排队
• 进行中
• 做

您可以将这些列作为列添加到表中并递增/递减它们。

create table jobs (
id bigserial primary key,
name text not null,
queued integer not null default 0 check (queued >= 0),
in_progress integer not null default 0 check (in_progress >= 0),
done integer not null default 0 check (done >= 0)
);

对作业进行排队。

update jobs
set queued = queued + 1
where id = ?

开始工作。如果它更新了列，则表示您获得了作业。如果没有，则队列中没有作业。

update jobs
set queued = queued - 1,
in_progress = in_progress + 1
where queued > 0
and id = ?

完成作业。

update jobs
set in_progress = in_progress - 1,
done = done + 1
where in_progress > 0
and id = ?

---

或者，为了进行更精细的控制，请将其拆分为两个表：作业和作业队列。

create table jobs (
id bigserial primary key
name text not null
);
create table job_queue (
id bigserial primary key
job_id bigint not null references jobs(id) on delete cascade
);
jobs
id    name
1     Puzzle Solving
2     Washing Dishes
job_queue
id job_id
3  1
5  2
6  2

通过插入作业来排队作业。

insert into job_queue (job_id) values (?)

通过删除一个条目来完成作业。没有必要先检查。如果删除了行，则表示您完成了作业。

delete from jobs_queue
where id = (
select id from jobs_queue
where job_id = ?
limit 1
)

---

我们可以将两者结合起来。标志和job_queue表。我们可以存储更多信息，例如谁在做工作，何时排队等等。

create table job_queue (
id bigserial primary key
job_id bigint not null references jobs(id) on delete cascade,
status text not null default 'queued',
user_id bigint references users(id) on delete set null,
created_at timestamp not null default current_timestamp,
updated_at timestamp not null default current_timestamp
);

对作业进行排队。

insert into job_queue (job_id) values (?)

开始工作。同样，无需检查。相反，请检查行是否已更新。

update job_queue
set status = 'in progress', user_id = ?, updated_at = current_timestamp
where id = (
select id
from job_queue
where status = 'queued'
limit 1
)
returning id

完成作业。使用从更新返回的 ID(如果有(。

update job_queue
set status = 'done', updated_at = current_timestamp
where id = ?

或者找到您正在进行的排队作业并对其进行更新。

update job_queue
set status = 'done', updated_at = current_timestamp
where id = (
select id
from job_queue
where user_id = ?
and job_id = ?
and status = 'in progress'
limit 1
)