高并发写入表使用触发器时如何避免死锁错误

核心原因 #

你遇到的死锁是高并发下多事务无序获取clothes表行锁导致的循环等待，和触发器逻辑本身复杂度无关：
PostgreSQL的行锁会持有到事务提交为止，当两个及以上并发事务同时按不同顺序修改clothes表的不同行时，就会触发死锁：

• 事务A先插入了clothing_id=1的sales记录，持有clothes表id=1的行锁，接下来要修改id=2的行
• 事务B先插入了clothing_id=2的sales记录，持有clothes表id=2的行锁，接下来要修改id=1的行
  两个事务互相等待对方释放锁，就会触发死锁错误。

可行解决方案 #

• 方案1：统一锁获取顺序（最优，从根源解决）
  如果你的业务存在单事务批量插入多条sales记录的场景，插入前先把所有待插入的sales数据按clothing_id做固定顺序（全升序或全降序）排序后再执行插入，保证所有事务都是按相同顺序获取clothes表的行锁，破坏死锁的循环等待条件。
• 方案2：改行级触发器为语句级批量处理
  如果你使用的是PostgreSQL 10及以上版本，可以把行级触发器替换为语句级触发器，通过过渡表获取本次语句插入的所有数据，去重并按clothing_id排序后再批量UPSERT到clothes表，既大幅减少了锁竞争的频率，也能保证锁获取顺序一致。
  参考代码：

  CREATE OR REPLACE FUNCTION clothing_price_update_batch() RETURNS trigger AS $clothing_price_update_batch$
  BEGIN
    INSERT INTO clothes(clothing_id, last_price, sale_date)
    SELECT DISTINCT ON (clothing_id) clothing_id, price, "timestamp"
    FROM new_rows
    ORDER BY clothing_id, "timestamp" DESC -- 取同id最新的价格
    ON CONFLICT (clothing_id) DO UPDATE 
    SET last_price = EXCLUDED.last_price, sale_date = EXCLUDED.sale_date;
    RETURN NULL;
  END;
  $clothing_price_update_batch$ LANGUAGE plpgsql;
  
  CREATE TRIGGER clothing_price_update_batch_trigger AFTER INSERT ON sales
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE PROCEDURE clothing_price_update_batch();
  

• 方案3：增加死锁重试逻辑
  如果都是单条插入的小事务，死锁概率极低的场景，可以在业务代码中捕获数据库死锁异常，对失败的插入操作做1-3次自动重试即可，PostgreSQL触发死锁时会自动回滚其中一个事务，重试后一般都能执行成功。

额外优化 #

因为你提到sales表数据插入完成后不会再执行更新操作，你可以把原有触发器的监听事件从BEFORE INSERT OR UPDATE改成AFTER INSERT，去掉不需要的UPDATE监听，减少不必要的触发器触发次数，进一步降低锁冲突概率。

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