SQL Transaction - 小人物看世界

交易控制 (SQL Transaction)

這篇 SQL Transaction 教學將帶你認識資料庫交易（Transaction）的基本概念與用法。Transaction 是一組被視為單一工作單元的 SQL 操作，這些操作要嘛全部成功執行，要嘛全部不執行，確保資料庫的資料一致性與完整性。

在實際應用中，許多操作需要同時更新多筆資料（例如銀行轉帳需要同時扣款與入帳），如果只完成了一半就發生錯誤，可能會造成資料不一致的嚴重問題。Transaction 就是用來解決這類問題的機制，透過 BEGIN、COMMIT、ROLLBACK 等指令來控制操作的執行與回復。

ACID 特性

Transaction 遵循 ACID 四大特性，這是確保資料庫交易可靠性的基本原則：

特性	英文	說明
Atomicity（原子性）	Atomicity	交易中的所有操作視為一個整體，要嘛全部成功，要嘛全部失敗回復
Consistency（一致性）	Consistency	交易執行前後，資料庫必須從一個一致狀態轉換到另一個一致狀態
Isolation（隔離性）	Isolation	多個交易同時執行時，彼此之間不會互相干擾
Durability（持久性）	Durability	交易一旦提交成功，其結果會永久保存，即使系統故障也不會遺失

Transaction 語法 (SQL Transaction Syntax)

BEGIN TRANSACTION;

-- 執行 SQL 操作
SQL_statement_1;
SQL_statement_2;
...

COMMIT;    -- 提交交易，確認所有變更
-- 或
ROLLBACK;  -- 回復交易，撤銷所有變更

說明：

關鍵字	說明
BEGIN TRANSACTION	開始一個新的交易（部分資料庫可簡寫為 BEGIN 或 START TRANSACTION）
COMMIT	提交交易，將所有變更永久寫入資料庫
ROLLBACK	回復交易，撤銷自 BEGIN 以來的所有變更
SAVEPOINT	在交易中設定一個儲存點，可部分回復到該點
ROLLBACK TO SAVEPOINT	回復到指定的儲存點，而非回復整個交易

Transaction 用法 (Example)

假設我們有一個帳戶資料表 accounts 如下（若需了解如何建立資料表，請參考 CREATE TABLE 教學）：

id	name	balance
1	Alice	10000
2	Bob	5000
3	Charlie	8000

基本交易：銀行轉帳

使用 Transaction 來執行一筆銀行轉帳操作，將 Alice 的帳戶轉帳 2000 元給 Bob。透過交易確保扣款與入帳同時成功或同時失敗。

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 2000 WHERE name = 'Bob';

COMMIT;

執行後 accounts 資料表如下：

id	name	balance
1	Alice	8000
2	Bob	7000
3	Charlie	8000

使用 ROLLBACK 回復交易

如果在交易過程中發現操作有誤，可以使用 ROLLBACK 撤銷所有變更，讓資料回到交易開始前的狀態。

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 5000 WHERE name = 'Charlie';

-- 發現金額輸入錯誤，撤銷所有變更
ROLLBACK;

執行 ROLLBACK 後，accounts 資料表維持不變（回到交易開始前的狀態）：

id	name	balance
1	Alice	10000
2	Bob	5000
3	Charlie	8000

使用 SAVEPOINT 設定儲存點

SAVEPOINT 可以在交易中設定一個中間儲存點。當需要部分回復時，可以使用 ROLLBACK TO SAVEPOINT 回到該儲存點，而不用撤銷整個交易。

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 2000 WHERE name = 'Bob';

-- 發現對 Bob 的操作有誤，回復到 sp1
ROLLBACK TO SAVEPOINT sp1;

-- 改為正確的操作
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Charlie';

COMMIT;

執行後 accounts 資料表如下（Alice 扣款 1000 成功，Bob 的操作被回復，Charlie 入帳 1000 成功）：

id	name	balance
1	Alice	9000
2	Bob	5000
3	Charlie	9000

搭配錯誤處理的交易

在實務上，交易通常會搭配錯誤處理機制。以下以 SQL Server 的 TRY…CATCH 為例，示範當操作發生錯誤時自動回復交易。

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE accounts SET balance = balance - 3000 WHERE name = 'Alice';
    UPDATE accounts SET balance = balance + 3000 WHERE name = 'Bob';
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT '交易失敗，已回復所有變更';
END CATCH;

當 TRY 區塊中的任何操作發生錯誤時，程式會跳到 CATCH 區塊執行 ROLLBACK，確保資料不會處於不一致的狀態。

不同資料庫的 Transaction 語法差異

不同的資料庫系統在 Transaction 語法上略有差異，以下是常見資料庫的比較：

資料庫	開始交易	提交	回復
MySQL	START TRANSACTION	COMMIT	ROLLBACK
PostgreSQL	BEGIN	COMMIT	ROLLBACK
SQL Server	BEGIN TRANSACTION	COMMIT	ROLLBACK
Oracle	（自動開始）	COMMIT	ROLLBACK
SQLite	BEGIN TRANSACTION	COMMIT	ROLLBACK

搭配 INSERT 與 DELETE 的交易

Transaction 不僅適用於 UPDATE，也可以搭配 INSERT INTO 與 DELETE FROM 一起使用，確保多筆操作的一致性。

BEGIN TRANSACTION;

-- 新增一筆帳戶
INSERT INTO accounts (name, balance) VALUES ('David', 6000);

-- 從 Alice 轉帳給 David
UPDATE accounts SET balance = balance - 1500 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 1500 WHERE name = 'David';

COMMIT;

執行後 accounts 資料表如下：

id	name	balance
1	Alice	8500
2	Bob	5000
3	Charlie	8000
4	David	7500

Transaction 是資料庫操作中非常重要的概念，特別是在處理需要多步驟完成的操作時。搭配適當的錯誤處理機制，可以有效防止資料不一致的問題。如果你想了解更多資料操作的語法，可以參閱 SQL INSERT INTO、SQL UPDATE 與 SQL DELETE FROM 教學。

延伸閱讀

ㄚ槌