PostgreSQL函数使用beginTransaction与commit防并发插入重复ID咨询

关于函数中使用事务控制的问题 #

PostgreSQL的普通函数（FUNCTION）不支持显式执行BEGIN/COMMIT事务控制语句，函数本身会运行在调用方的事务上下文当中，PL/pgSQL语法里的BEGIN只是代码块的起始标记，和事务开启无关。如果你确实需要显式控制事务，可以改用PostgreSQL 11及以上版本支持的存储过程（PROCEDURE），存储过程支持独立的事务提交/回滚操作。

无需修改表结构的可行解决方案 #

优先级最高的方案是使用PostgreSQL内置的咨询锁（Advisory Lock），不需要修改表结构、不需要额外权限，就可以实现全局串行化执行插入逻辑，从根源避免ID重复：

• 在ID生成、数据插入的逻辑执行前，先申请全局咨询锁，示例代码：SELECT pg_advisory_lock(10001);，其中10001是你自定义的、专门给这个用户表插入业务使用的唯一锁标识，不要和其他业务的锁冲突即可
• 执行你原本的ID生成、数据插入逻辑
• 插入完成后释放锁，示例代码：SELECT pg_advisory_unlock(10001);

提示：如果担心忘记释放锁，也可以使用事务级咨询锁pg_advisory_xact_lock(10001)，不需要手动调用unlock，当前事务结束后会自动释放锁。如果事务执行过程中报错异常终止，PostgreSQL也会自动释放当前会话持有的所有咨询锁，不会出现死锁问题。

如果你的ID生成逻辑是读取当前表最大ID+1的模式，也可以把查询和插入合并为单条原子SQL，减少并发冲突概率，示例：

INSERT INTO your_table (id, location_id, date_time)
SELECT COALESCE(MAX(id), 0) + 1, $1, $2 
FROM your_table;

不过这种方案在高并发场景下还是有概率出现冲突，建议搭配咨询锁使用更稳妥。

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