Postgres中跨表的乐观并发控制

Question

我正在寻找一种在Postgres中管理跨越多个表的乐观并发控制的方法。同时，我也试图将业务逻辑保持在数据库之外。我的一个表设置大致如下：

CREATE TABLE master
(
    id SERIAL PRIMARY KEY NOT NULL,
    status VARCHAR NOT NULL,
    some_value INT NOT NULL,
    row_version INT NOT NULL DEFAULT(1)
)

CREATE TABLE detail
(
    id SERIAL PRIMARY KEY NOT NULL,
    master_id INT NOT NULL REFERENCES master ON DELETE CASCADE ON UPDATE CASCADE,
    some_data VARCHAR NOT NULL
)

master.row_version会在行更新时由触发器自动递增。

客户端应用程序执行以下操作：

1. 从master表中读取记录。
2. 基于记录的值计算一些业务逻辑，这可能需要涉及几分钟的延迟和用户交互。
3. 根据步骤2中的逻辑将记录插入到detail表中。

如果自第一步读取记录时master.row_version的值已更改，则希望拒绝步骤3。乐观并发控制似乎是正确的答案（唯一的答案？），但我不确定如何跨两个表管理它。

我考虑使用Postgres中带有master表相关记录的行级锁的函数可能是解决方法。但我不确定这是我的最佳/唯一选项，或者会是什么样子（我对Postgres语法有点陌生）。

鉴于客户端应用程序是用C#编写的，我正在使用Npgsql。我不知道它是否可以帮助我？如果可能的话，我想避免使用函数，但我无法找到使用纯SQL的方法，而且匿名代码块（至少在Npgsql中）不支持我需要的I/O操作。

- Snixtor

---

您还可以在主表中锁定“年轻”的交易记录，直到详细信息更新。这样，您就知道在进行计算时没有其他人可以修改数据。更大的问题是您认为什么是正确的。 - SMW

关于“年轻”的事务，您是否指的是SERIALIZABLE隔离级别？ - Snixtor

也许你可以检查 COUNT(detail.id) where master_id = $1，然后使用Laurenz展示的技术再次进行相同的检查比较，如果有变化尝试插入NULL以引发异常。 - heyhugo

Answer 1

我后来得出结论，行锁可用于“典型”的悲观并发控制方法中，但与行版本相结合时可以产生一种“混合”方法，具有某些有意义的优点。

毫不奇怪，选择悲观、乐观或“混合”并发控制取决于应用程序的需求。

悲观并发控制

典型的悲观并发控制方法可能是这样的：

1. 开始数据库事务。 2. 从“主”表中读取（和锁定）记录。 3. 执行业务逻辑。 4. 将记录插入“详细信息”表中。 5. 提交数据库事务。

如果步骤3的业务逻辑运行时间很长，这种方法可能不理想，因为它会导致运行时间很长的事务（通常不利），以及对“主”表中的记录进行长时间锁定，这可能对并发性造成问题。

乐观并发控制

仅使用乐观并发控制的方法可能更像这样：

1. 从“主”表中读取记录（包括行版本）。 2. 执行业务逻辑。 3. 开始数据库事务。 4. 在“主”表中增加记录行版本号（进行乐观并发控制检查）。 5. 将记录插入“详细信息”表中。 6. 提交数据库事务。

在这种情况下，数据库事务的持续时间较短，任何（隐含的）行锁定的持续时间也较短。但是，在“主”表中增加记录行版本号可能会对并发操作产生一些误导。想象一下几个这种情景的并发操作，它们将开始因为行版本已经被增加而在乐观并发控制检查失败，即使记录上的有意义属性没有被更改。

混合并发控制

“混合”方法同时使用悲观锁定和（某种程度上的）乐观锁定，如下所示：

1. 从“主”表中读取记录（包括行版本）。 2. 执行业务逻辑。 3. 开始数据库事务。 4. 根据其ID和行版本号重新从“主”表中读取记录（某种乐观并发控制检查）并锁定该行。 5. 将记录插入“详细信息”表中。 6. 提交数据库事务。

如果步骤4无法获取记录，则应将其视为乐观并发控制检查失败。自步骤1以来，记录已更改，因此业务逻辑不再有效。

与典型的悲观并发控制场景类似，这涉及事务和显式行锁定，但是事务+锁定的持续时间不再包括执行业务逻辑所需的时间。

和乐观并发控制场景一样，记录需要一个版本。但不同的是，版本号不会被更新，这意味着依赖于该行版本的其他操作不会受到影响。

混合方法的示例

混合方法可能更有利的一个例子：

博客有一个post表和一个comment表。只有当post.comments_locked标志为false时，才能向帖子添加评论。添加评论的过程可以使用混合方法，确保用户可以同时添加评论而不会出现任何并发异常。

博客所有者可能会编辑他们的post，在这种情况下，可以采用传统的乐观并发控制方法。博客所有者可以拥有长时间运行的编辑过程，而不会受到用户添加评论的影响。当post更新到数据库时，版本将增加，这意味着任何正在进行中的添加评论操作都将失败，但它们可以轻松地使用从数据库重新获取post记录并重试该过程的数据库获胜方法来重试。

- Snixtor

---

master和detail表中的记录之间是否存在约束？如果存在，则在此处没有解决方案，因为仅更新master表中的记录的后续事务将使数据库不一致。另一方面，如果没有约束，则基于过期读取在details表中插入结果没有问题，因此在这种情况下，所有这些努力似乎都是无用的。或者我是否遗漏了您的问题或答案中未明确解释的内容？ - mljrg

这对我真的很有帮助，因为我一直在尝试找出解决这个问题的方案。在我的观点中，应用程序负责确保其所做的每个更改是一致的。本答案中涉及应用程序在依赖于主表情况下对“详情”表进行更改。应用程序对“主表”的任何更改都需要对“详情”表进行类似的检查。 - Dark Nebula

Answer 2

如果你想使用乐观并发控制，那么锁定是行不通的，请参考维基百科上关于该主题的文章：

OCC假设多个事务经常可以在不干扰彼此的情况下完成。 在运行时，事务使用数据资源而不对这些资源进行锁定。

你可以使用更复杂的INSERT语句。 如果$1是原始的row_version，而$2和$3是要插入到detail中的master_id和some_data，则运行以下命令：

WITH m(id) AS
     (SELECT CASE WHEN master.row_version = $1
                  THEN $2
                  ELSE NULL
             END
      FROM master
      WHERE master.id = $2)
INSERT INTO detail (master_id, some_data)
   SELECT m.id, $3 FROM m

如果 row_version 发生了更改，那么这将尝试将 NULL 插入到 detail.id 中，这将导致一个错误：
ERROR: null value in column "id" violates not-null constraint
您可以将其翻译为更有意义的错误消息。

- Laurenz Albe

---

为什么要锁定？我可以理解这可能有点“多余”，因为乐观并发控制通常消除了锁定的需要，但我认为它们不是互斥技术的任何技术原因。 - Snixtor

乐观并发控制被定义为不使用锁，详见维基百科文章（https://en.wikipedia.org/wiki/Optimistic_concurrency_control）。因此，您所要求的是一种自相矛盾的说法。乐观并发控制的整个理念在于希望没有人会更改数据，而您无需使用锁来确保。 - Laurenz Albe

我引用了维基百科上的文章来支持我的说法（请记住，在PostgreSQL中，读操作不会锁定任何行）。当然，你可以自由地对常识持有不同意见。如果你认为悲观并发控制是你要走的路，请尽管去做。但如果你使用行锁，就没有必要确保在你读取它之后该行没有被更改，因为锁定将阻止这种情况的发生。 - Laurenz Albe

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Snixtor

行锁和其他锁一样，只在事务结束前保持。如果您想使用悲观并发控制，您可以SELECT row_version FROM master WHERE ... FOR SHARE/UPDATE，然后必须保持事务处于打开状态，直到步骤2完成（出于本评论范围之外的原因，这是一个坏主意）。在此期间，没有人可以更改master中的行，无需检查。您可以像我在答案中描述的那样使用所谓的悲观CC或乐观CC；混合它们不会带来任何好处。 - Laurenz Albe