在Postgres中插入状态转换

# 在Postgres中插入状态转换 来源：https://thoughtbot.com/blog/inserting-state-transitions-in-postgres 在《在Postgres中建模状态转换》(https://thoughtbot.com/blog/modeling-state-transitions-in-postgres)中，我们通过一个追加写入的`user_statuses`表替换了`users`表中的`status`列。该设计提供了完整的历史记录，且能对最常见情况进行高效查询。 但它引入了一个值得处理的边界情况：当两个事务同时尝试更改同一用户的状态时会发生什么？在使用列的方案中，数据库层面的覆盖是无害的。而在追加写入模型中，两条记录都会保留，因此必须显式处理这个问题。 ## 竞态条件 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#the-race-condition) 假设用户Alice处于`pending`状态。两位管理员同时更改她的状态：一位批准，另一位拒绝。 如果时机不巧，两个事务都会在提交前读取当前状态，并各自插入一行记录： | 步骤 | 事务A | 事务B | | :--- | :--- | :--- | | 1 | `BEGIN` | | | 2 | 读取当前状态：`pending` | | | 3 | | `BEGIN` | | 4 | | 读取当前状态：`pending` | | 5 | 插入`approved` | | | 6 | `COMMIT` | | | 7 | | 插入`denied` | | 8 | | `COMMIT` | 两者都成功。`user_statuses`表现在如下所示： | id | user_id | status | created_at | | :--- | :--- | :--- | :--- | | 1 | 1 | `pending` | 2026-07-10 09:00:00 | | 2 | 1 | `approved` | 2026-07-15 11:00:00 | | 3 | 1 | `denied` | 2026-07-15 11:00:01 | Alice在一秒内既被批准又被拒绝。两位管理员都看到`pending`状态，并各自独立地对其采取行动，对彼此的决定一无所知。 ## 在users上使用状态列如何避免此问题 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#how-a-status-column-on-users-avoids-this) 在`users`表上使用状态列（更常见的设计），两个事务都会执行： ```sql -- 事务A UPDATE users SET status = 'approved' WHERE id = 1; -- 事务B UPDATE users SET status = 'denied' WHERE id = 1; ``` Postgres会对更新进行序列化，因此最后写入者胜出。只有一个列存储一个值，数据库永远不会达到矛盾的状态。 尽管如此，*在实际应用中，静默覆盖不一定无害*。第二位管理员在不知情的情况下撤销了第一位管理员的决定。如果转换伴随副作用，比如发送邮件或调用外部API，即使只有一个转换应该生效，两个副作用也会被触发。 ## 为何追加写入模型无法免费获得序列化 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#why-append-only-doesn39t-get-serialization-for-free) 使用列时，一个值会覆盖另一个值。使用插入时，两行都会出现在表中，并且历史记录中会包含一个本不该发生的转换。没有覆盖来掩盖这个问题。无论哪种方式，两种方法本身都不能防止并发转换。 ## 在追加写入模型中处理并发 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#handling-concurrency-in-an-append-only-model) 我们需要一种方式让第二个事务等待第一个事务完成。`SELECT ... FOR UPDATE`通过锁定一行直到事务结束来实现此目的。`users`父行是一个自然的选择，因为它已存在且每个用户唯一： ```sql BEGIN; -- 锁定用户行，直到此事务完成 SELECT id FROM users WHERE id = 1 FOR UPDATE; -- 读取当前状态 -- 检查转换是否有效 -- 插入新状态 COMMIT; ``` 以下是两个并发事务发生的情况： | 步骤 | 事务A | 事务B | | :--- | :--- | :--- | | 1 | `BEGIN` | | | 2 | `SELECT ... FOR UPDATE`（获取锁） | | | 3 | | `BEGIN` | | 4 | | `SELECT ... FOR UPDATE`（被阻塞） | | 5 | 读取当前状态：`pending` | | | 6 | 插入`approved` | | | 7 | `COMMIT`（释放锁） | | | 8 | |（解除阻塞，获取锁）| | 9 | | 读取当前状态：`approved` | | 10 | | ... | 事务B现在看到的当前状态是`approved`，而不是`pending`。它可以据此做出明智的决策。 这在READ COMMITTED (https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED)（Postgres默认的事务隔离级别）下工作。无需更改配置。 ## 添加转换检查 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#adding-a-transition-check) 锁序列化了访问，但并不拒绝无效转换。除非我们进行检查，否则事务B仍会运行其插入： ```sql BEGIN; SELECT id FROM users WHERE id = 1 FOR UPDATE; -- 读取当前状态 SELECT status FROM user_statuses WHERE user_id = 1 ORDER BY created_at DESC, id DESC LIMIT 1; -- 返回：'approved' -- 'approved' -> 'denied' 是有效的转换吗？ -- 不是。回滚。 ROLLBACK; ``` 转换规则是应用逻辑。一个简单的允许转换映射就足够了： ``` null -> pending pending -> approved, denied approved -> (终态) denied -> pending ``` 事务B读取`approved`，检查映射，然后回滚，因为`approved`到`denied`是不允许的。Alice保持`approved`状态。 同时使用锁和检查的完整序列： | 步骤 | 事务A | 事务B | | :--- | :--- | :--- | | 1 | `BEGIN` | | | 2 | 锁定用户行 | | | 3 | | `BEGIN` | | 4 | | 锁定用户行（被阻塞） | | 5 | 读取状态：`pending` | | | 6 | `pending -> approved`？有效。插入。 | | | 7 | `COMMIT` | | | 8 | |（解除阻塞）| | 9 | | 读取状态：`approved` | | 10 | | `approved -> denied`？无效。 | | 11 | | `ROLLBACK` | ## 可序列化隔离级别呢？ (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#what-about-serializable-isolation) Postgres提供了另一种方法：将事务隔离级别设置为`SERIALIZABLE` (https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE)。两个事务不是提前锁定，而是乐观地进行。在提交时，Postgres检查结果是否与某种串行执行顺序一致。如果不一致，它会中止一个事务并抛出序列化错误。 这也可以防止上述竞态条件，但实际操作中存在缺点： **误报。** Postgres使用谓词锁跟踪读取 (https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE)。这些锁最初是元组粒度的，但为节省内存会升级到页面或关系级别 (https://wiki.postgresql.org/wiki/Serializable)。当这种情况发生时，两个操作不同用户的事务（其行恰好位于同一堆页面）即使数据不重叠也会发生冲突。 **重试逻辑。** 被中止的事务收到的是错误，而不是阻塞等待。应用程序必须捕获错误并重试，这增加了复杂性。使用`SELECT FOR UPDATE`时，第二个事务只需等待，然后使用最新数据继续执行。 **开销。** 在所有可序列化事务中跟踪谓词锁需要内存和CPU成本。Postgres提供了调整参数 (https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-TRANSACTION)来控制这一点，但这增加了运维复杂性。 对于此问题，`SELECT FOR UPDATE`是更简单、更可预测的选择。成本很低：只需主键查找和一个仅在事务持续期间持有的行级锁。 ## 总结 (https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#wrap-up) 任何验证转换或触发副作用（如邮件和API调用）的实际应用都需要一个锁来序列化并发状态转换，无论您使用列还是追加写入表。列的方法通过静默覆盖掩盖了问题，但副作用仍然会触发两次。 在父行上使用`SELECT FOR UPDATE`，第二个事务会阻塞，直到第一个事务提交，然后读取更新后的状态。锁内的转换检查会拒绝无效转换。由于您无论如何都需要锁，追加写入模型并不会增加复杂性。它只是使并发需求变得明确，并且作为回报，您获得了完整的历史记录。 此处显示的转换检查使用了一个硬编码的映射。在实践中，此验证位于您的应用程序代码中。