PostgreSQL插入transaction表时与user表UPDATE操作发生死锁的解决方案咨询

看起来你遇到的是典型的PostgreSQL事务循环锁导致的死锁问题，我来帮你拆解一下原因和可行的解决方案，结合你说的“100%确保user和user_store的行不会被删除、主键也不会修改”这个前提，咱们可以针对性处理。

先搞清楚死锁到底怎么来的 #

从你给出的日志来看，两个进程互相等待对方事务持有的锁，形成了循环等待链。结合你的场景，大概率是这样的：

• 你的存储过程p_add_payment()里，除了插入transaction表，可能还涉及对user_store表的操作（或者其他关联表），它的锁顺序是：先锁user_store → 再插入transaction时需要锁user表（因为外键检查）；
• 而UPDATE user的事务，锁顺序是：先锁user表 → 如果后续还有操作需要锁user_store表（或者你没提到的其他关联表），就会形成循环等待：进程A持有user_store锁等user锁，进程B持有user锁等user_store锁，死锁就触发了。

另外，插入transaction时PostgreSQL会自动对user表的对应行加ShareLock（共享锁），目的是确保在插入过程中，这个user行不会被删除或主键修改（外键约束的要求）；而UPDATE user会加RowExclusiveLock（行排他锁），这两种锁本身是兼容的，但架不住锁顺序搞反了，就容易出死锁。

针对你的情况，可行的解决方案 #

方案1：统一事务的锁顺序（最安全推荐）

解决死锁最根本的方法就是让所有涉及这些表的事务，都按照完全一致的顺序获取锁。比如，不管是存储过程还是UPDATE user的操作，都先锁user表，再锁user_store表。

具体操作：
在你的存储过程p_add_payment()开头，先显式地获取user和user_store的锁，顺序固定为user在前：

-- 假设存储过程接收user_id和user_store_id参数
SELECT 1 FROM "user" WHERE id = p_user_id FOR NO KEY UPDATE;
SELECT 1 FROM "user_store" WHERE id = p_user_store_id FOR NO KEY UPDATE;

这样，存储过程会先锁住user行，再去处理其他操作；而UPDATE user的事务本身会先锁住user行，后续如果要操作user_store，也是在user锁之后。两个事务的锁顺序完全一致，就不会出现循环等待，自然也就不会死锁了。

方案2：跳过外键检查的锁（谨慎使用，仅限你能100%保证数据完整性的情况）

既然你明确说不会删除user/user_store行，也不会修改它们的主键，那可以跳过插入transaction时的外键锁检查，但这个方法有风险，一定要确保你的业务逻辑能严格保证数据的有效性。

有两种实现方式：

1. 手动检查外键+无锁插入
   先手动确认user和user_store的行存在，然后再插入transaction。手动检查时可以用非阻塞的锁，避免等待：

   -- 检查user行存在，用SKIP LOCKED跳过被锁的行
   SELECT 1 FROM "user" WHERE id = 2 FOR SHARE SKIP LOCKED;
   SELECT 1 FROM "user_store" WHERE id = 3 FOR SHARE SKIP LOCKED;
   -- 如果上面的查询返回结果，说明行存在且当前没被锁，执行插入
   INSERT INTO transaction(id, user_id, user_store_id) VALUES(1, 2, 3);
   

   注意：如果SELECT返回空，说明目标行被其他事务锁住了，这时候你需要在应用层或者存储过程里做重试逻辑，否则插入会失败。

2. 临时禁用外键触发器
   这种方法更激进，直接禁用transaction表的外键约束触发器，插入时就不会做外键检查，自然也不会加锁：

   -- 在事务开头执行，仅对当前事务生效
   SET LOCAL session_replication_role = replica;
   -- 执行插入操作
   INSERT INTO transaction(id, user_id, user_store_id) VALUES(1, 2, 3);
   -- 事务结束后会自动恢复，也可以手动设置回来
   SET LOCAL session_replication_role = default;
   

   警告：这个操作会绕过所有约束检查，包括外键、唯一性等，一旦插入了无效的user_id或user_store_id，会直接导致数据不一致，所以只有在你能100%确保插入数据有效的情况下才能用。

方案3：调整事务隔离级别（效果有限，不优先推荐）

可以尝试把事务隔离级别降到READ COMMITTED（PostgreSQL默认就是这个），或者如果用了更高的级别比如REPEATABLE READ，可以考虑降级。不过这个方法不一定能彻底解决死锁，只能减少发生概率，因为核心问题还是锁顺序的问题。

总结 #

最安全可靠的是方案1，统一锁顺序；如果业务场景确实允许，并且你能保证数据完整性，可以考虑方案2的手动检查方式。尽量避免直接禁用约束的激进操作，除非你对数据一致性有绝对的把控。

备注：内容来源于stack exchange，提问作者ard