PostgreSQL循环异常处理避免死锁：无需多BEGIN-END的解决方案咨询

你的函数在常规场景运行正常，但扩容测试时出现死锁，核心原因是嵌套的BEGIN-END异常块会创建子事务——PL/pgSQL中每个异常块对应一个子事务，子事务持有的锁在异常处理后不会立即释放，高并发下锁冲突概率会大幅提升。以下是两种无需依赖嵌套BEGIN-END块的解决方案：

方案1：用UPSERT原子操作替代异常捕获 #

针对唯一约束冲突场景，PostgreSQL的INSERT ... ON CONFLICT（UPSERT）是原子性操作，无需事后捕获异常，也不会产生子事务，从根源避免锁竞争问题。

修改后的函数示例：

CREATE OR REPLACE FUNCTION get_or_create_abc(input_dict json)
RETURNS TABLE(success_list text[], failed_list text[]) 
LANGUAGE plpgsql
AS $BODY$
DECLARE
    key text;
    value json;
    row json;
    a text;
    x text;
    y text;
    z text;
    counter integer := 0;
    success_list text[] := '{}';
    failed_list text[] := '{}';
BEGIN
    FOR key, value IN SELECT * FROM json_each(input_dict)
    LOOP
        FOR row IN SELECT json_array_elements(value)
        LOOP
            a := row->>0;
            x := row->>1;
            y := row->>2;
            z := row->>3;
            counter := 0;
            
            WHILE counter < 3 LOOP
                -- 用UPSERT替代原异常捕获逻辑，原子处理存在性判断+插入/更新
                INSERT INTO abc_table (a, x, y, z)
                VALUES (a, x, y, z)
                ON CONFLICT (a) DO UPDATE SET 
                    x = EXCLUDED.x,
                    y = EXCLUDED.y,
                    z = EXCLUDED.z;
                
                -- 操作成功，加入成功列表并退出重试循环
                success_list := array_append(success_list, a);
                EXIT;
                
                -- 仅处理其他非唯一约束的意外错误
                EXCEPTION
                    WHEN OTHERS THEN
                        counter := counter + 1;
                        IF counter >= 3 THEN
                            failed_list := array_append(failed_list, a || ':' || SQLERRM);
                            EXIT;
                        END IF;
            END LOOP;
        END LOOP;
    END LOOP;
    
    RETURN QUERY SELECT success_list, failed_list;
END;
$BODY$;

ALTER FUNCTION get_or_create_abc(json) OWNER TO test_user;

核心优势 #

• UPSERT是原子操作，不会出现预查询与插入之间的竞态问题，比先查后插更可靠
• 仅保留最外层异常块处理重试逻辑，彻底避免嵌套子事务
• 唯一约束冲突被直接处理，无需进入异常分支，减少锁的持有时间

方案2：预检查+条件执行（适合非UPSERT场景） #

如果无法使用UPSERT，可通过预查询提前判断是否会触发约束，减少进入异常块的概率：

-- 内层循环逻辑示例
WHILE counter < 3 LOOP
    -- 预检查是否存在冲突记录
    IF NOT EXISTS (SELECT 1 FROM abc_table WHERE a = $1) THEN
        BEGIN
            INSERT INTO abc_table (a, x, y, z) VALUES (a, x, y, z);
            success_list := array_append(success_list, a);
            EXIT;
        EXCEPTION
            WHEN OTHERS THEN
                -- 仅处理意外错误，不处理唯一约束（已通过预检查规避）
                counter := counter + 1;
        END;
    ELSE
        -- 处理记录已存在的场景（如更新或标记成功）
        UPDATE abc_table SET x = x, y = y, z = z WHERE a = a;
        success_list := array_append(success_list, a);
        EXIT;
    END IF;
    
    IF counter >= 3 THEN
        failed_list := array_append(failed_list, a);
    END IF;
END LOOP;

注意事项 #

• 预检查和插入之间存在极短的竞态窗口，高并发下仍可能出现重复插入，建议结合INSERT ... ON CONFLICT兜底
• 仅在无法使用UPSERT的场景下采用此方案

关键优化原则 #

• 移除嵌套异常块：每个异常块对应一个子事务，嵌套会导致锁层级增多、释放延迟
• 优先原子操作：用UPSERT类原子操作替代“尝试操作+捕获异常”的模式，从根源减少子事务
• 简化重试逻辑：将重试逻辑放在外层循环，避免内层反复创建子事务

死锁排查补充 #

如果修改后仍存在死锁，可通过查询pg_locks视图查看锁持有情况，或开启log_lock_waits和log_deadlocks参数，定位具体的锁竞争对象。

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