PostgreSQL AFTER INSERT触发器并发死锁问题及解决方案咨询

问题背景 #

我们在PostgreSQL的表上创建了AFTER INSERT触发器my_trg，满足条件时执行函数f，该函数会读写同表中的其他行。高负载场景下出现死锁，死锁详情如下：

deadlock detected DETAIL: Process 27874 waits for ShareLock on transaction 316579928; blocked by process 27882. Process 27882 waits for ShareLock on transaction 316579930; blocked by process 27874.

死锁成因可归纳为：

• 两个独立事务同时执行INSERT操作
• 事务进入触发器函数执行阶段
• 触发器尝试读写同一组行，但锁获取顺序不一致，形成循环等待

触发器用于统计场景（如数值递增），典型冲突序列：

TRIGGER-1 READ  tab.row.k # => k=0
TRIGGER-2 READ  tab.row.k # => k=0
TRIGGER-1 WRITE tab.row.k # => k=1
TRIGGER-2 attempts to WRITE tab.row.k but fails 

核心问题分析 #

原触发器函数中的UPDATE语句会动态扫描符合条件的行并逐个加锁。当两个事务涉及多组交叉行（比如插入不同子节点但共享祖先节点）时，可能出现锁顺序不一致的情况：事务1先锁行A再锁行B，事务2先锁行B再锁行A，最终形成循环等待触发死锁。

缓解方案 #

1. 统一顺序提前锁定目标行（推荐） #

可以且应该在触发器内锁定目标行，但必须保证所有事务按固定顺序获取锁，消除循环等待的可能。具体做法是先用SELECT ... FOR UPDATE按固定规则（如path字典序）锁定所有需要更新的行，再执行UPDATE操作。

修改后的触发器函数：

CREATE OR REPLACE FUNCTION f() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.deleted THEN
        RETURN NULL;
    END IF;

    -- 先按固定顺序锁定所有待更新的祖先节点
    PERFORM 1
    FROM data
    WHERE path @> (SELECT path FROM data WHERE id = NEW.parent_id)
    ORDER BY path -- 按path排序，确保所有事务锁顺序完全一致
    FOR UPDATE;

    -- 执行统计更新
    UPDATE data
    SET
        file_count = file_count + 1,
        size = COALESCE(size, 0) + COALESCE(NEW.size, 0)
    WHERE
        path @> (
            SELECT path
            FROM data
            WHERE id = NEW.parent_id
        );

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. 其他可选方案 #

• 乐观锁重试：在UPDATE语句中加入当前值判断（如file_count = file_count），更新失败时重试。但高负载下重试成本高，仅适合低冲突场景。
• 调整隔离级别：改用REPEATABLE READ隔离级别，但会延长锁持有时间，可能加剧锁等待，不推荐高负载场景使用。
• 批量插入：合并多个INSERT操作，减少触发器触发次数，降低冲突概率，需业务场景允许。

关键注意事项 #

• 锁顺序的一致性是解决死锁的核心，必须确保所有事务以完全相同的顺序获取锁。
• SELECT ... FOR UPDATE会将快照读转为当前读并加排他锁，避免后续更新时出现锁等待顺序混乱的问题。

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