时间戳

首页 > 解决方案 > Postgresql 可序列化事务未按预期工作

sql - Postgresql 可序列化事务未按预期工作

问题描述

我正在尝试实施任务分配系统。用户可以从池中请求任务。即使设置为 SERIALIZABLE，事务有时也会将相同的任务提供给多个用户，即使它不应该这样做。

简化架构：

CREATE TABLE tasks(
  _id CHAR(24) PRIMARY KEY,
  totalInstances BIGINT NOT NULL
);

CREATE TABLE assigned(
  _id CHAR(24) PRIMARY KEY,
  _task CHAR(24) NOT NULL
);

任务表充满了很多行，假设每个都有totalInstances = 1，这意味着每个任务最多应该分配一次。

查询添加一行assigned：

WITH task_instances AS (
  SELECT t._id, t.totalInstances - COUNT(assigned._id) openInstances
  FROM tasks t
  LEFT JOIN assigned ON t._id = assigned._task
  GROUP BY t._id, t.totalInstances
),

selected_task AS (
  SELECT _id
  FROM task_instances
  WHERE openInstances > 0
  LIMIT 1
)

INSERT INTO assigned(_id, _task)
SELECT $1, _id
FROM selected_task;

是$1传递给每个查询的随机 id。

症状

我们有大约 100 个经常请求任务的活跃用户。这按预期工作，除了可能在 1000 个请求中出现一次。然后，在并行请求时为相同的idassigned创建两行。我希望可序列化的执行回滚第二个，因为 openInstances 应该被第一个减少到 0。 _task

设置

我们使用 Postgres 10.3，查询是通过 Slick 3.2.3 从 Scala 代码运行的withTransactionIsolation(Serializable)。没有其他查询从表中删除或插入到assigned表中。

Postgres 日志显示请求在不同的会话中运行，并且SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;在每个任务分配查询之前执行。

我尝试用不同的样式重写查询，包括对子查询使用VIEWs WITH，并用和包围查询，BEGIN但COMMIT没有效果。

任何帮助表示赞赏。

编辑

我应该补充一点，有时确实会出现预期的序列化错误/回滚，然后我们的应用程序会重试查询。我在过去几个小时的日志中看到了这种正确的行为 10 次，但有 2 次它仍然错误地分配了两次相同的任务，如上所述。

标签： sqlpostgresqlconcurrencytransactionsserializable

解决方案

可序列化的隔离级别并不意味着事务实际上是串行的。它只保证读取已提交、可重复读取和不存在幻读。而且您所描述的行为看起来并不违法。

为避免重复记录，您可以简单地做

select ... from task_instances for update

由于这个“for update”子句，选定的行将在事务生命周期内被锁定。所以只有一个事务能够更新，第二个事务必须等到第一个事务被提交。结果，第二个事务将读取第一个事务更新的值 - 这是您需要的保证。

同样重要的是，如果您在这种情况下使用“select for update”，您甚至不需要 Serializable 隔离级别，读取提交就足够了。

推荐阅读