PostgreSQL 存储过程：3 个真实应用场景

CREATE OR REPLACE PROCEDURE transfer_funds_naive(
    p_sender_id INT,
    p_receiver_id INT,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 第 1 步：从发送方扣除
    UPDATE accounts 
    SET balance = balance - p_amount 
    WHERE id = p_sender_id;

-- 第 2 步：加到接收方
    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE id = p_receiver_id;
    COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE transfer_funds_robust(
    p_sender_id INT,
    p_receiver_id INT,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_locked_id INT;
    v_sender_balance DECIMAL;
BEGIN
    -- 1. 确定性锁定阶段
    -- 我们对 ID 排序，确保总是先锁定较小的 ID。
    -- FOR UPDATE 子句获取 RowExclusiveLock。
    FOR v_locked_id IN 
        SELECT id 
        FROM accounts 
        WHERE id IN (p_sender_id, p_receiver_id) 
        ORDER BY id ASC 
        FOR UPDATE
    LOOP
        -- 我们只是迭代以安全地获取锁。
        -- 此循环内暂时不需要任何操作。
    END LOOP;

-- 2. 业务逻辑与验证阶段
    -- 现在我们拥有锁，可以安全地读取余额。
    SELECT balance INTO v_sender_balance 
    FROM accounts 
    WHERE id = p_sender_id;

    IF v_sender_balance < p_amount THEN
        -- 异常/回滚时锁会自动释放
        RAISE EXCEPTION '余额不足。账户：%，可用：%，请求：%', 
                        p_sender_id, v_sender_balance, p_amount
              USING ERRCODE = 'P0001'; -- 自定义错误码
    END IF;

    -- 3. 执行阶段
    UPDATE accounts 
    SET balance = balance - p_amount 
    WHERE id = p_sender_id;

    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE id = p_receiver_id;

    -- 4. 提交并释放锁
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 如果发生任何错误，回滚事务以防止部分更新。
        ROLLBACK;
        -- 重新抛出错误，让应用层知道失败
        RAISE;
END;
$$;

CREATE OR REPLACE PROCEDURE process_bulk_payouts(p_batch_size INT DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_record RECORD;
    v_processed_count INT := 0;
BEGIN
    -- 遍历所有待处理付款
    FOR v_record IN 
        SELECT id, user_id, amount 
        FROM payouts 
        WHERE status = 'PENDING'
        -- 注意：如果在循环内提交，请不要在这里使用 FOR UPDATE，
        -- 因为 COMMIT 会释放所有锁并关闭标准游标。
    LOOP
        BEGIN -- 隐式子事务开始
            
            -- 1. 业务逻辑
            UPDATE accounts 
            SET balance = balance + v_record.amount 
            WHERE id = v_record.user_id;
            
            UPDATE payouts 
            SET status = 'COMPLETED', processed_at = NOW() 
            WHERE id = v_record.id;
            
            v_processed_count := v_processed_count + 1;

            -- 2. 批量提交策略
            -- 每 N 条记录提交一次，以释放锁并防止内存膨胀
            IF v_processed_count % p_batch_size = 0 THEN
                COMMIT;
                RAISE NOTICE '已提交 % 条记录的批次', p_batch_size;
            END IF;
            
        EXCEPTION
            WHEN OTHERS THEN
                -- 3. 失败处理
                -- 此特定 v_record 的子事务自动回滚。
                -- 我们记录失败并让循环继续。
                INSERT INTO payout_errors (payout_id, error_message, created_at) 
                VALUES (v_record.id, SQLERRM, NOW());
                
                UPDATE payouts 
                SET status = 'FAILED' 
                WHERE id = v_record.id;
                
                -- 我们必须立即提交错误日志，否则未来的
                -- 回滚可能会抹掉我们的错误跟踪。
                COMMIT; 
        END;
    END LOOP;

    -- 对未达到批次大小限制的剩余记录进行最终提交
    COMMIT;
    RAISE NOTICE '批量付款处理完成。';
END;
$$;

CREATE OR REPLACE PROCEDURE book_seat_naive(
    p_user_id INT,
    p_seat_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    -- 第 1 步：检查可用性（检查）
    SELECT status INTO v_status 
    FROM seats 
    WHERE id = p_seat_id;

    -- 第 2 步：进行预订（行动）
    IF v_status = 'AVAILABLE' THEN
        -- 模拟一些业务逻辑延迟（例如检查用户余额）
        PERFORM pg_sleep(0.1); 
        UPDATE seats 
        SET status = 'BOOKED', user_id = p_user_id 
        WHERE id = p_seat_id;
        INSERT INTO tickets (user_id, seat_id, created_at) 
        VALUES (p_user_id, p_seat_id, NOW());
        
        COMMIT;
    ELSE
        RAISE EXCEPTION '座位已被占用！';
    END IF;
END;
$$;

CREATE OR REPLACE PROCEDURE book_seat_robust(
    p_user_id INT,
    p_seat_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    -- 1. 原子检查并锁定阶段
    -- NOWAIT 确保如果另一个事务持有锁，
    -- 此查询立即抛出 "could not obtain lock" 错误 (55P03)
    SELECT status INTO v_status 
    FROM seats 
    WHERE id = p_seat_id 
    FOR UPDATE NOWAIT;

    -- 2. 验证阶段
    IF v_status != 'AVAILABLE' THEN
        -- 座位被我们锁定，但过去已经被预订了
        RAISE EXCEPTION '座位 % 不再可用。', p_seat_id
              USING ERRCODE = 'P0002';
    END IF;

    -- 3. 执行阶段
    UPDATE seats 
    SET status = 'BOOKED', user_id = p_user_id 
    WHERE id = p_seat_id;

    INSERT INTO tickets (user_id, seat_id, created_at) 
    VALUES (p_user_id, p_seat_id, NOW());

    -- 4. 提交并释放行锁
    COMMIT;
    
EXCEPTION
    WHEN lock_not_available THEN -- 捕获特定的 NOWAIT 异常 (55P03)
        ROLLBACK;
        RAISE EXCEPTION '座位当前正被另一位用户预订。请重试。'
              USING ERRCODE = 'P0003';
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;