sql - 可靠 SELECT + UPDATE 的事务与行标记

Question

问题:

给定表 table_a 和 table_b，每当 table_a 更新时，我都需要可靠地(并发地)执行这样的操作:

1. SELECT table_a 中的一些行。
2. 在应用程序代码中计算一些内容。
3. UPDATE table_b 中的一行。

我需要防止发生这样的事情(称之为场景 A)，其中 table_b 最终反射(reflect)了 table_a 的旧版本:

• worker1 从 table_a 获取行。
• table_a 已更新。
• worker2 从 table_a 获取行。
• worker2 更新 table_b。
• worker1 更新 table_b。

但这很好(将此场景称为 B)，因为 table_b 最终处于正确的状态:

• worker1 从 table_a 获取行。
• table_a 已更新。
• worker2 从 table_a 获取行。
• worker1 更新 table_b。
• worker2 更新 table_b。

交易:

一个解决方案是将整个事情包装在一个REPEATABLE READ 事务中。然后worker1的事务在场景A中失败，worker2的事务在场景B中失败。没有办法区分场景，唯一的选择就是重试失败的事务.

但这在两种情况下都是浪费:在场景 A 中，我们宁愿不重试 worker1 的事务，因为 table_b 已经完全更新。在场景 B 中，我们不想让 worker2 的交易一开始就失败，因为它在做正确的事情。

行标记:

如果我们从一开始就知道 table_b 中行的主键 (:b_id) 并且每个 worker 都有一些唯一的 ID (:worker_id)，我们可以尝试别的东西。将 mark 列添加到 table_b 并让每个工作人员在第 1 步之前执行此操作:

UPDATE table_b SET mark = :worker_id WHERE id = :b_id;

然后在第 3 步中添加一个 WHERE 子句:

UPDATE table_b SET ... WHERE ... AND mark = :worker_id;

现在 worker1 根据需要在这两种情况下都不会更新步骤 3 中的任何行。

行标记在这里是一种合理的方法吗？我遗漏了哪些缺点？这个问题的“规范”解决方案是什么？

澄清:我正在使用 PostgreSQL。

Answer 1

在表 b 上使用事务和 SELECT ... FOR UPDATE。

这将导致行锁定，这将阻止 worker 2 在 worker 1 提交之前更新表 b。如果您使用 select for update 启动您的工作流程，那么在 worker 1 提交之前，worker 2 无法启动。

第二种方法(可能更好)是将更新包装在执行选择的语句中，因此它是单个语句，并自动处理锁定。

行标记需要作为一个想法被丢弃，因为在 worker 1 提交之前， worker 2 不会看到标记的行....