分析在處理巢狀交易時常見的錯誤

在 Postgres 寫程式有時候根本像在玩「找錯誤」大冒險。這一節我們來聊聊在處理巢狀交易時，會遇到哪些常見錯誤跟地雷。走起！

在 function 跟 procedure 裡面錯誤使用交易指令

錯誤： 嘗試在 FUNCTION 裡面用 COMMIT、ROLLBACK 或 SAVEPOINT。

為什麼： 在 PostgreSQL 裡，function（CREATE FUNCTION ... LANGUAGE plpgsql）永遠都在同一個外部交易裡跑，任何交易指令在 function 裡都不能用。硬要用就會 syntax error。

錯誤範例：

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- 錯誤：function 裡不能用交易指令
END;
$$ LANGUAGE plpgsql;

正確做法：

如果你要做「要嘛全做完要嘛全不做」的原子操作，就寫 function，不要自己下交易指令。如果你想分段 commit，請用procedure。

錯誤： 在 PL/pgSQL 的 procedure 裡用 ROLLBACK TO SAVEPOINT。

為什麼： PostgreSQL 17 只允許在procedure（CREATE PROCEDURE ... LANGUAGE plpgsql）裡用 COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT。但ROLLBACK TO SAVEPOINT 在 PL/pgSQL 裡不能用！硬用就 syntax error。

錯誤範例：

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;
$$;

巢狀 procedure 呼叫：限制跟常見錯誤

錯誤： 在已經開啟的 client 交易裡呼叫有 COMMIT/ROLLBACK 的 procedure。

為什麼： 有交易控制的 procedure 只適合在autocommit 模式下用（一個 procedure 一個交易），不然 procedure 裡面用 COMMIT 或 ROLLBACK 會出錯，因為 client 端已經有開啟交易了。

範例：

# 在 Python 用 psycopg2 預設 autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- my_proc 裡面 COMMIT 會出錯

正確做法：

• 呼叫 procedure 前，先把連線切到 autocommit 模式。
• 不要用 function 或 SELECT 來呼叫 procedure。

錯誤： 有交易控制（COMMIT, ROLLBACK）的 procedure，如果不是用 CALL（例如用 SELECT）呼叫，交易控制就不會生效。

為什麼： 只有用 CALL（或匿名 DO 區塊）呼叫 procedure 才能控制交易。從 function 呼叫不行。

鎖定跟死結（Deadlock）問題

鎖定就像不請自來的客人：一開始只是礙事，最後會搞亂全場。Deadlock 就是兩個交易互相等到天荒地老。來看個經典例子：

1. 交易 A 鎖住 orders 表的一行，然後想更新 products 表的一行。
2. 交易 B 鎖住 products 表的一行，然後想更新 orders 表的一行。

結果兩個交易都卡住，誰也動不了。就像兩台車同時想過彎，結果卡死在路口。

範例：

-- 交易 A
BEGIN;
UPDATE orders SET status = '處理中' 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 injection。像這樣：

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

如果 user_input 是 1; DROP TABLE orders;，那 orders 表就掰掰了。

怎麼避免？用 prepared statement：

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

這樣就能防止 SQL injection。

錯誤處理不當導致交易沒回滾

如果錯誤沒處理好，交易可能會卡在無效狀態。像這樣：

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, '待處理');

BEGIN;
-- 這裡有個會出錯的操作
INSERT INTO non_existing_table VALUES (1);
-- 出錯了，但交易沒結束

COMMIT; -- 錯誤：目前交易已經中斷

因為出錯，後面所有程式都卡住。

怎麼避免？ 用 EXCEPTION 區塊正確回滾：

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, '待處理');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '發生錯誤，交易會回滾。';
END;

怎麼避免錯誤：小撇步跟建議

• 寫複雜 procedure 前，先寫 pseudocode，把每個步驟跟可能出錯的點都列出來。
• 用 SAVEPOINT 來做局部回滾，但記得用完要釋放。
• 盡量避免長時間的交易——交易越久，越容易被鎖住。
• 巢狀 procedure 呼叫時，確保外部跟內部的交易 context 有同步好。
• 常用 EXPLAIN ANALYZE 檢查 procedure 效能。
• 把錯誤 log 到資料表或文字檔，debug 會輕鬆很多。

錯誤範例跟修正

範例 1：呼叫巢狀 procedure 出錯

錯誤程式：

BEGIN;

CALL process_order(5);

-- process_order 裡面有 ROLLBACK
-- 整個交易就失效了
COMMIT; -- 錯誤

修正後：

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- 只有出錯才回滾
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

範例 2：Deadlock 問題

錯誤程式：

-- 交易 A
BEGIN;
UPDATE orders SET status = '處理中' 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 = '處理中' WHERE id = 1;
COMMIT;

這些錯誤說明了為什麼交易要很小心才行。多練習，實戰經驗多了，現實人生（還有職涯）就比較不會遇到 ROLLBACK 啦！