分析在使用嵌套事务时的常见错误

在Postgres里写代码，有时候就像闯关游戏一样——“找出你的bug”。这一块我们就来聊聊用嵌套事务时经常遇到的坑和常见错误。走起！

在函数和过程里错误地用事务命令

错误： 试图在FUNCTION里用COMMIT、ROLLBACK或者SAVEPOINT。

为什么： 在PostgreSQL里，函数（CREATE FUNCTION ... LANGUAGE plpgsql）总是在同一个外部事务里跑，函数里不能用任何事务命令。你要是用了，直接语法报错。

错误示例：

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- 错误：函数里不能用事务命令
END;
$$ LANGUAGE plpgsql;

正确做法：

如果你要做“要么全成要么全失败”的原子操作，就别在函数里写事务命令。如果你想分阶段提交，用过程吧。

错误： 在PL/pgSQL过程里用ROLLBACK TO SAVEPOINT。

为什么： PostgreSQL 17里，过程（CREATE PROCEDURE ... LANGUAGE plpgsql）里只允许COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT。但是ROLLBACK TO SAVEPOINT在PL/pgSQL里不能用！写了就语法报错。

错误示例：

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- 错误！不能用
END;
$$;

正确做法：

想“部分回滚”，用BEGIN ... EXCEPTION ... END块——它会自动建savepoint，块里出错就会回滚到块开头。

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- 可能出错的操作
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '在BEGIN ... EXCEPTION ... END块里回滚';
    END;
END;
$$;

嵌套调用过程：限制和常见错误

错误： 在已经开启的客户端事务里调用带COMMIT/ROLLBACK的过程。

为什么： 带事务控制的过程只能在autocommit模式下正常用（一过程一事务），否则你在过程里用COMMIT或ROLLBACK就会报错：客户端已经开了事务。

示例：

# 在Python里psycopg2默认autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- 在my_proc里COMMIT会报错

正确做法：

• 调用过程前，把连接切到autocommit模式。
• 别通过函数或者SELECT调用过程。

错误： 带事务控制（COMMIT, ROLLBACK）的过程如果不是用CALL命令（比如用SELECT）调用，是不会生效的。

为什么： 只有用CALL（或者匿名DO块）才能控制事务。函数里不能调用。

锁和死锁（Deadlock）问题

锁就像不速之客：一开始碍事，后来直接搅局。死锁就是两个事务互相等到天荒地老。典型例子：

1. 事务A锁住了orders表的一行，想更新products表的一行。
2. 事务B锁住了products表的一行，想更新orders表的一行。

结果谁也走不下去。就像两辆车同时想拐进一个窄巷，最后堵死。

示例：

-- 事务A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;

-- 事务B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- 现在事务A想更新`products`同一行，
-- 事务B想改`orders`那一行。
-- 死锁！

怎么避免？

1. 总是按同样顺序更新数据，比如先orders，再products。
2. 别让事务跑太久。
3. 用LOCK要谨慎，锁粒度越小越好。

动态SQL（EXECUTE）用错了

动态SQL要是乱用，绝对是大坑。最常见的就是SQL注入。比如：

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

如果user_input里有1; DROP TABLE orders;，那orders表就拜拜了。

怎么避免？用预处理语句：

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

这样就能防SQL注入了。

错误处理不当导致事务没回滚

如果错误没处理好，事务可能会卡在无效状态。比如：

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');

BEGIN;
-- 某个操作出错了
INSERT INTO non_existing_table VALUES (1);
-- 出错了，但事务没结束

COMMIT; -- 错误：当前事务已中断

一旦出错，后面的代码就卡住了。

怎么避免？ 用EXCEPTION块来正确回滚：

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '出错了，事务会回滚。';
END;

怎么避免这些坑：建议和小技巧

• 写复杂过程前，先写伪代码，把每一步和可能出错的地方都列出来。
• 用SAVEPOINT做局部回滚，记得用完要释放。
• 尽量别让事务太久——越久越容易被锁。
• 嵌套调用过程时，确保外部和内部事务上下文同步。
• 用EXPLAIN ANALYZE检查过程性能。
• 把错误日志写到表或文本文件里，方便调试。

错误示例和修正

示例1：嵌套过程调用出错

有bug的代码：

BEGIN;

CALL process_order(5);

-- process_order里ROLLBACK了
-- 整个事务都废了
COMMIT; -- 错误

修正后的代码：

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- 只在出错时回滚
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

示例2：死锁问题

有bug的代码：

-- 事务A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;
-- 等`products`

-- 事务B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- 等`orders`

修正：

-- 两个事务都按同样顺序操作：
-- 先`products`，再`orders`。
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Processing' WHERE id = 1;
COMMIT;

这些坑说明，事务操作必须小心又有经验。但说真的，练得多了，现实（和职场）里被ROLLBACK的机会就少多了。