PostgreSQL如何获取表列下一个可用值并避免并发重复冲突？

可以通过PostgreSQL函数实现需求，无需修改应用业务逻辑，仅需要将应用原有的「获取下一个可用值」的查询替换为对该函数的调用即可。

实现原理 #

原来的并发冲突来自于「读取当前最大值+计算下一个值」的过程是非原子性的，多个事务可以同时读到相同的最大值，最终生成重复值。我们可以通过PostgreSQL的锁机制保证取值过程的原子性，同时利用函数封装整个逻辑，对上层应用透明。

示例实现代码 #

假设你要取值的源表为source_table，目标列名为target_col，函数实现如下：

CREATE OR REPLACE FUNCTION get_next_available_val()
RETURNS INTEGER AS $$
DECLARE
    next_val INTEGER;
BEGIN
    -- 对源表加排他锁，保证当前事务持有锁期间其他事务无法读取该列最大值
    LOCK TABLE source_table IN EXCLUSIVE MODE;
    -- 计算下一个可用值，逻辑和原有实现完全一致
    SELECT COALESCE(MAX(target_col), 0) + 1 INTO next_val FROM source_table;
    RETURN next_val;
END;
$$ LANGUAGE plpgsql VOLATILE;

应用侧仅需要将原来的取值SQL替换为SELECT get_next_available_val();即可，返回值和原有逻辑完全一致。

注意事项 #

• 如果允许生成的值存在少量缺口（比如事务回滚导致的跳号），优先推荐使用PostgreSQL自带的序列对象，性能远高于锁表方案，且天然保证并发取值不重复。创建序列的语句如下：

  CREATE SEQUENCE source_table_target_col_seq OWNED BY source_table.target_col;
  -- 同步序列初始值为当前列的最大值
  SELECT setval('source_table_target_col_seq', COALESCE(MAX(target_col), 0)) FROM source_table;
  

  后续取值直接调用nextval('source_table_target_col_seq')即可。
• 如果你要求值必须严格连续无缺口，上述带表锁的函数即可满足需求，锁仅在函数执行期间持有（通常为毫秒级），执行完成后自动释放，不会影响其他业务操作。
• EXCLUSIVE MODE锁仅阻塞其他事务对表的写入和同样的EXCLUSIVE锁请求，普通读操作不受影响，对业务侧的影响极小。
• 如果需要支持多个不同列的取值，只要针对不同的列编写不同的函数，或者给函数加参数传递表名、列名即可，注意动态拼接SQL的时候要使用format()函数处理标识符，避免SQL注入风险。

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