PostgreSQL原子读写行状态（含行不存在场景）的事务方案问询

现有方案的正确性与安全性 #

1. 正确性：该方案确实能实现预期的行锁效果。INSERT ... ON CONFLICT语句在并发场景下，第一个事务会插入新行（若不存在）或执行无操作更新（若行已存在），同时会获取该行的排他锁；后续事务会等待锁释放后再执行，从根本上避免了并发INSERT的主键冲突问题。之后的UPDATE操作在锁保护下执行，状态转换验证的逻辑是可靠的。
2. 安全性：从数据库锁机制层面是安全的，只要事务隔离级别为READ COMMITTED及以上（PostgreSQL默认级别），就能保证并发事务不会出现竞态条件。但该方案存在明显缺陷：引入的虚拟枚举值DUMMY_FOR_THE_ROW_LOCK违反了state字段的业务语义，若事务中途异常终止，可能会留下状态为虚拟值的脏数据，破坏数据完整性。

更优方案 #

方案1：合并INSERT/UPDATE与状态验证到单条语句 #

将状态转换规则直接嵌入INSERT ... ON CONFLICT DO UPDATE语句中，实现全原子操作，无需额外的UPDATE步骤，也避免了虚拟值的使用：

INSERT INTO user_group_membership (user_id, group_id, state)
VALUES (2, 3, 'invited')
ON CONFLICT (user_id, group_id) DO UPDATE
SET state = CASE
    -- 按需定义状态转换规则，例如仅允许从'left'转为'invited'
    WHEN user_group_membership.state = 'left' THEN 'invited'
    -- 不符合规则时保持原状态（也可通过抛出错误终止操作）
    ELSE user_group_membership.state
END
-- 可选：仅当符合转换规则时才执行更新，否则无操作
WHERE user_group_membership.state IN ('left')
RETURNING *;

• 优势：单语句原子操作，无额外锁步骤，数据始终符合业务语义，避免脏数据。
• 适用场景：状态转换规则可通过SQL表达式清晰描述。

方案2：合法初始状态占位+应用层验证 #

若状态转换规则过于复杂，无法在SQL中实现，可改用合法的初始状态替代虚拟值，同时保留锁机制：

-- 先获取行锁：行不存在则插入合法初始状态，行存在则无操作更新
INSERT INTO user_group_membership (user_id, group_id, state)
VALUES (2, 3, 'invited') -- 使用目标状态或合法初始状态
ON CONFLICT (user_id, group_id) DO UPDATE
SET state = user_group_membership.state -- 无操作更新，仅获取锁
RETURNING *;

-- 应用层逻辑：根据返回的当前状态验证转换规则
-- 示例：若当前状态为'banned'，则拒绝转换，抛出业务错误
-- 验证通过后执行更新（若需要调整状态）
UPDATE user_group_membership 
SET state = 'joined'
WHERE user_id = 2 AND group_id = 3;

• 优势：避免虚拟值，锁机制依然可靠，支持复杂的应用层状态验证。
• 注意：若插入的初始状态与最终目标状态不符，需确保后续UPDATE能覆盖，且事务全程保持原子性（避免中途失败导致初始状态残留）。

方案3：使用 advisory 锁（备选） #

若上述方案均不适用，可基于user_id和group_id生成唯一的 advisory 锁键，先获取锁再执行读写操作：

-- 获取advisory锁（使用bigint组合user_id和group_id，例如user_id::bigint << 32 | group_id::bigint）
SELECT pg_advisory_xact_lock(((2::bigint) << 32) | 3::bigint);

-- 执行SELECT验证状态，或INSERT/UPDATE操作
SELECT * FROM user_group_membership WHERE user_id = 2 AND group_id = 3 FOR UPDATE;
-- 后续根据查询结果执行INSERT或UPDATE，以及状态验证逻辑

• 劣势：需手动管理锁键生成，不如行级锁优雅，若锁未正确释放（如事务异常）可能导致锁残留。

虚拟值问题的妥善解决 #

核心思路是避免引入不符合业务语义的枚举值：

• 优先采用方案1，将状态转换与原子操作合并，无需占位值；
• 若必须拆分操作，使用方案2，用合法的业务状态（如目标状态或默认初始状态）作为占位，确保数据始终符合枚举约束；
• 绝对不要为锁机制修改枚举类型定义，避免破坏数据模型的严谨性。

内容的提问来源于stack exchange，提问作者Benjamin M