# PostgreSQL Transaction（交易）簡介 Transaction 是一組被視為「單一工作單位」的資料庫操作。這些操作要嘛全部成功，要嘛全部取消，避免資料只更新一半而造成不一致。 例如：銀行轉帳需要同時完成： 1. 從帳戶 A 扣款 2. 將款項存入帳戶 B 如果扣款成功但存款失敗，就會造成資料錯誤，因此應將兩個操作放在同一個 Transaction 中。 --- ## 一、Transaction 的 ACID 特性 PostgreSQL 的 Transaction 具備 ACID 特性： ### 1. Atomicity（原子性） 交易中的所有操作視為一個整體： - 全部成功：`COMMIT` - 任一操作失敗：`ROLLBACK` ### 2. Consistency（一致性） 交易完成後，資料必須符合資料表的約束，例如： - `PRIMARY KEY` - `FOREIGN KEY` - `UNIQUE` - `CHECK` - `NOT NULL` ### 3. Isolation（隔離性） 同時執行的交易彼此隔離，避免讀取到不一致的資料。 ### 4. Durability（持久性） 交易一旦 `COMMIT`，即使資料庫重新啟動，已提交的資料仍應保留。 --- # 二、基本 Transaction 操作 PostgreSQL 常用的交易控制指令如下： ```sql BEGIN; -- 或 START TRANSACTION; ``` 開始交易。 ```sql COMMIT; ``` 提交交易，永久保存變更。 ```sql ROLLBACK; ``` 取消交易，還原尚未提交的變更。 --- ## 三、基本範例 假設有銀行帳戶資料表： ```sql CREATE TABLE accounts ( account_id integer PRIMARY KEY, owner_name text NOT NULL, balance numeric(12, 2) NOT NULL ); ``` 執行轉帳： ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2; COMMIT; ``` 上述兩個 `UPDATE` 都成功後，執行 `COMMIT`，交易才會正式完成。 如果中途發生錯誤，可以取消： ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1; -- 假設後續操作發生錯誤 ROLLBACK; ``` 執行 `ROLLBACK` 後，前面的扣款也會被還原。 --- # 四、檢查操作結果後再提交 實務上通常會檢查更新筆數或餘額是否足夠。 例如： ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1 AND balance >= 1000; -- 如果沒有更新任何資料，代表帳戶不存在或餘額不足 -- 此時應執行： ROLLBACK; ``` 如果條件符合，再繼續： ```sql UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2; COMMIT; ``` 在應用程式中，通常會由程式檢查每個 SQL 的執行結果，決定要 `COMMIT` 或 `ROLLBACK`。 --- # 五、Savepoint（儲存點） 如果不想直接取消整個 Transaction，可以使用 `SAVEPOINT`，只回復到某一個階段。 ```sql BEGIN; INSERT INTO users (user_id, username) VALUES (1, 'alice'); SAVEPOINT before_second_insert; INSERT INTO users (user_id, username) VALUES (2, 'bob'); -- 只回復第二個 INSERT ROLLBACK TO SAVEPOINT before_second_insert; -- 第一個 INSERT 仍然保留 COMMIT; ``` 相關指令： ```sql SAVEPOINT savepoint_name; ``` 建立儲存點。 ```sql ROLLBACK TO SAVEPOINT savepoint_name; ``` 回復到指定儲存點，但不結束整個 Transaction。 ```sql RELEASE SAVEPOINT savepoint_name; ``` 刪除儲存點。 --- # 六、交易隔離層級 PostgreSQL 支援以下交易隔離層級： ```sql READ COMMITTED REPEATABLE READ SERIALIZABLE ``` 可以在交易開始時指定： ```sql BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; ``` 或： ```sql SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` ## 1. READ COMMITTED PostgreSQL 預設層級。 ```sql BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` 特性： - 每個 SQL 指令看到的是該指令開始時已提交的資料 - 同一個 Transaction 中，不同 SQL 可能看到不同版本的資料 - 適合一般 CRUD 操作 ## 2. REPEATABLE READ ```sql BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` 特性： - 同一個 Transaction 內，多次讀取通常會看到一致的資料快照 - 可避免 Non-repeatable Read - 可能因為並行交易而發生序列化錯誤，需要重新執行交易 ## 3. SERIALIZABLE ```sql BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; ``` 特性： - 最嚴格的隔離層級 - 效果如同交易依序執行 - 並行衝突時可能產生錯誤，應由應用程式重試 --- # 七、鎖定資料列 如果需要確保某筆資料在交易期間不被其他交易修改，可以使用： ```sql BEGIN; SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE; ``` `FOR UPDATE` 會鎖定查詢到的資料列，其他交易若也要修改或使用相同鎖定，通常需要等待。 常見用途： - 扣款 - 庫存更新 - 預約名額 - 搶票 - 避免同一筆資料被同時修改 例如庫存扣除： ```sql BEGIN; SELECT stock FROM products WHERE product_id = 10 FOR UPDATE; UPDATE products SET stock = stock - 1 WHERE product_id = 10 AND stock > 0; COMMIT; ``` --- # 八、Autocommit（自動提交） 在許多工具或 PostgreSQL 用戶端中，預設會啟用 Auto-commit。 例如直接執行： ```sql INSERT INTO users (user_id, username) VALUES (1, 'alice'); ``` 如果 Auto-commit 開啟，這個 `INSERT` 通常會立即自動提交，相當於： ```sql BEGIN; INSERT INTO users (user_id, username) VALUES (1, 'alice'); COMMIT; ``` 如果要將多個 SQL 放在同一筆交易中，必須明確使用： ```sql BEGIN; -- 多個 SQL 操作 COMMIT; ``` 在 `psql` 中，也可以使用： ```sql \set AUTOCOMMIT off ``` 關閉自動提交。 --- # 九、錯誤發生後的處理 PostgreSQL 中，如果 Transaction 內的某個 SQL 發生錯誤，該 Transaction 通常會進入失敗狀態。 例如： ```sql BEGIN; INSERT INTO users (user_id, username) VALUES (1, 'alice'); -- 假設這裡發生 PRIMARY KEY 衝突 INSERT INTO users (user_id, username) VALUES (1, 'bob'); -- 後續 SQL 通常會出現： -- current transaction is aborted ROLLBACK; ``` 發生錯誤後，通常必須先執行： ```sql ROLLBACK; ``` 之後才能開始新的交易。 若使用 `SAVEPOINT`，則可以只回復部分操作： ```sql BEGIN; INSERT INTO users (user_id, username) VALUES (1, 'alice'); SAVEPOINT sp1; -- 可能失敗的操作 INSERT INTO users (user_id, username) VALUES (1, 'bob'); ROLLBACK TO SAVEPOINT sp1; COMMIT; ``` --- # 十、在應用程式中使用 Transaction 以 Python、Java、Node.js 等程式語言操作 PostgreSQL 時，通常流程如下： ```text 建立資料庫連線 ↓ BEGIN ↓ 執行多個 SQL ↓ 全部成功？ ├─ 是 → COMMIT └─ 否 → ROLLBACK ``` 伪程式碼如下： ```python try: connection.begin() execute("UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1") execute("UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2") connection.commit() except Exception: connection.rollback() raise ``` 使用連線池時，`COMMIT` 或 `ROLLBACK` 後，才應將連線歸還給連線池，避免未完成的交易影響下一個使用者。 --- # 十一、常見注意事項 ## 1. Transaction 不要開太久 長時間未提交的交易可能： - 持有資料列鎖 - 阻塞其他交易 - 造成資料庫膨脹 - 影響 Vacuum - 增加死結機率 因此應盡量縮短交易範圍。 ## 2. 注意 Deadlock（死結） 例如： - Transaction A 先鎖定資料列 1，再等待資料列 2 - Transaction B 先鎖定資料列 2，再等待資料列 1 PostgreSQL 會偵測死結並中止其中一個交易。應用程式通常需要捕捉錯誤並重新嘗試。 ## 3. Transaction 結束時要明確處理 每次使用 Transaction 後，都要確保執行： ```sql COMMIT; ``` 或： ```sql ROLLBACK; ``` 避免連線長期處於未完成交易狀態。 --- ## 總結 PostgreSQL 的 Transaction 主要透過以下指令控制： ```sql BEGIN; -- 執行一組 SQL 操作 COMMIT; ``` 若操作失敗，則使用： ```sql ROLLBACK; ``` 常見使用原則是： 1. 將必須同時成功的操作放在同一個 Transaction 中 2. 全部成功才 `COMMIT` 3. 發生錯誤就 `ROLLBACK` 4. 需要部分回復時使用 `SAVEPOINT` 5. 依需求選擇交易隔離層級 6. 盡量縮短交易時間，避免鎖定與死結問題