MySQL 交易 (Transaction) – ACID 概念深入解析

---

簡介

在資料庫系統中，交易是保證資料完整性與一致性的核心機制。無論是線上購物、金融系統，或是任何需要同時修改多筆資料的應用，都必須依賴交易來避免「半完成」的狀況。
MySQL 透過 ACID（Atomicity、Consistency、Isolation、Durability）四大特性，提供一套可靠的交易保證。掌握 ACID 的概念，不僅能寫出正確的程式碼，還能在高併發環境下維持資料庫的穩定與效能。

本篇文章將以 淺顯易懂 的方式說明 ACID 各層面的意義、在 MySQL 中的實作方式，並提供實務範例、常見陷阱與最佳實踐，幫助初學者到中級開發者快速上手。

---

核心概念

1. Atomicity（原子性）

原子性 表示一個交易要麼全部成功，要麼全部失敗，絕不會出現「只執行了一半」的情況。

MySQL 透過 undo log 來實作原子性：當交易內的任一語句失敗，系統會自動回滾（ROLLBACK）已執行的改變，使資料恢復到交易開始前的狀態。

範例 1：簡單的原子性交易

-- 開始交易
START TRANSACTION;

-- 第一步：扣除使用者 A 的餘額
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 第二步：給予使用者 B 的餘額
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 若任一步驟出錯，整個交易將回滾
COMMIT;   -- 成功則提交
-- ROLLBACK;   -- 若需要手動回滾，可使用此指令

重點：START TRANSACTION 後的所有語句都屬於同一個交易，只有 COMMIT 成功執行後，變更才會永久寫入磁碟。

---

2. Consistency（相容性）

相容性 確保交易執行前後，資料庫的所有約束（constraint）仍然成立。包括主鍵唯一、外鍵參照、CHECK 條件等。

若交易違反了任一約束，MySQL 會自動拋出錯誤並回滾，保證資料不會進入不合法的狀態。

範例 2：外鍵相容性

-- 假設有兩張表：orders (訂單) 與 customers (顧客)
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

START TRANSACTION;

-- 嘗試插入一筆不存在的顧客
INSERT INTO orders (id, customer_id, amount) VALUES (1, 999, 250.00);

-- 這裡會因外鍵違規而失敗，整個交易會自動回滾
COMMIT;

提示：若想在交易內先插入顧客再插入訂單，務必確保 先插入父表（customers），再插入子表（orders），才能維持相容性。

---

3. Isolation（隔離性）

隔離性 決定了同時執行的多筆交易之間，彼此的可見程度。MySQL 提供四種隔離等級（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE），其中 REPEATABLE READ 為 InnoDB 的預設等級，兼具效能與一致性。

隔離等級	可能發生的問題
READ UNCOMMITTED	髒讀 (Dirty Read)
READ COMMITTED	不可重複讀 (Non‑repeatable Read)
REPEATABLE READ	幻讀 (Phantom Read)（除非使用鎖）
SERIALIZABLE	完全隔離，效能最低

範例 3：觀察不同隔離等級的差異（以 READ COMMITTED 為例）

-- 連線 A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;   -- 假設回傳 500

-- 連線 B（同時執行）
UPDATE accounts SET balance = balance - 200 WHERE user_id = 1;
COMMIT;

-- 回到連線 A，重新執行相同查詢
SELECT balance FROM accounts WHERE user_id = 1;   -- 會看到 300，產生 *不可重複讀*
COMMIT;

實務建議：大多數線上服務選擇 REPEATABLE READ，若需要防止幻讀，可在查詢時加上 FOR UPDATE 鎖定列。

---

4. Durability（永續性）

永續性 表示一旦交易被提交（COMMIT），即使系統發生斷電或崩潰，資料也必須保留下來。MySQL 透過 redo log（重做日誌）將變更先寫入磁碟，再將資料寫回資料檔案，確保提交後的資料不會遺失。

範例 4：檢視 redo log 的作用（概念說明）

-- 假設已啟用 innodb_flush_log_at_trx_commit = 1（預設值）
START TRANSACTION;
INSERT INTO logs (msg) VALUES ('交易已提交');
COMMIT;   -- 此時 redo log 已同步寫入磁碟，斷電後仍能恢復

注意：若將 innodb_flush_log_at_trx_commit 設為 2 或 0，會提升效能但降低永續性，請依需求斟酌。

---

程式碼範例（實務常用）

以下提供 3~5 個 完整範例，涵蓋 ACID 各特性在 MySQL 中的典型使用情境。

範例 5：銀行轉帳（完整交易）

-- 交易流程：從 A 轉 200 元到 B，若任一步驟失敗則回滾
START TRANSACTION;

-- 1. 檢查 A 的餘額是否足夠
SELECT balance INTO @bal FROM accounts WHERE user_id = 1 FOR UPDATE;
IF @bal < 200 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '餘額不足';
END IF;

-- 2. 扣款
UPDATE accounts SET balance = balance - 200 WHERE user_id = 1;

-- 3. 加款
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

COMMIT;

關鍵：使用 FOR UPDATE 鎖住讀取的列，避免同時有其他交易修改造成 不可重複讀 或 幻讀。

範例 6：訂單建立與庫存扣減（相容性 + 鎖定）

START TRANSACTION;

-- 1. 插入訂單
INSERT INTO orders (order_id, customer_id, total) VALUES (1001, 5, 1500);

-- 2. 扣減庫存（使用行鎖防止超賣）
UPDATE products SET stock = stock - 3
WHERE product_id = 42 AND stock >= 3
FOR UPDATE;

-- 若庫存不足，UPDATE 會影響 0 行，可自行檢查並回滾
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
END IF;

COMMIT;

範例 7：多筆資料批次寫入（提升效能）

START TRANSACTION;

INSERT INTO audit_log (user_id, action, ts) VALUES
(1, 'login', NOW()),
(2, 'logout', NOW()),
(3, 'update_profile', NOW());

-- 若其中一筆因外鍵違規失敗，整個交易會回滾
COMMIT;

範例 8：讀寫分離環境下的交易（Isolation）

-- 主庫 (primary) 用於寫入
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC123';
COMMIT;

-- 從庫 (replica) 用於報表查詢
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT qty FROM inventory WHERE sku = 'ABC123';
COMMIT;

範例 9：設定永續性參數（Durability）

-- 讓每筆交易都同步寫入 redo log（最安全）
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 若追求效能，可改為 2（每秒寫一次），但有資料遺失風險
-- SET GLOBAL innodb_flush_log_at_trx_commit = 2;

---

常見陷阱與最佳實踐

陷阱	說明	解決方案
忘記 COMMIT	交易開啟後未提交，會造成鎖定持續，其他連線被阻塞。	確保所有 START TRANSACTION 都配對 COMMIT 或 ROLLBACK。可使用 try…catch 或 stored procedure 包裝。
使用 AUTOCOMMIT=1	默認自動提交會破壞原子性。	在需要交易的程式區段先 SET autocommit=0;，結束後恢復。
過度鎖定	SELECT … FOR UPDATE 鎖住過多列，導致併發效能下降。	只鎖定必要的行，或改用 樂觀鎖（版本號）配合 UPDATE … WHERE version = ?。
忽視錯誤回傳	只檢查最後一個語句的結果，可能漏掉前面的失敗。	每個 DML 後檢查 ROW_COUNT() 或捕捉 SQLSTATE，必要時立即 ROLLBACK。
不恰當的隔離等級	使用 SERIALIZABLE 會大幅降低效能。	依需求選擇 REPEATABLE READ（預設）或 READ COMMITTED，只有在必須防止幻讀時才升級。
設定 innodb_flush_log_at_trx_commit 為 0	系統崩潰時可能遺失最近的交易。	僅在 批次寫入、測試環境使用，正式環境建議保持 1。

最佳實踐要點

1. 明確的 Transaction Scope：將交易範圍限制在最小必要的程式區段。
2. 錯誤處理：使用 DECLARE ... HANDLER 或程式語言的例外機制，保證在任何錯誤發生時都能 ROLLBACK。
3. 合理使用鎖：FOR UPDATE、LOCK IN SHARE MODE 只在真的需要防止競爭時使用。
4. 監控與排程：定期檢查 SHOW ENGINE INNODB STATUS、performance_schema，確保沒有長時間的鎖。
5. 測試 ACID 行為：在開發環境模擬斷電、崩潰，驗證 COMMIT 後資料的永續性。

---

實際應用場景

場景	為何需要 ACID	典型實作
線上支付	必須確保「扣款」與「產生訂單」同時成功，避免金錢流失或重複扣款。	使用 START TRANSACTION + FOR UPDATE 鎖住帳戶列，最後 COMMIT。
庫存管理	防止同時多筆訂單導致超賣。	在扣庫存前檢查 stock >= qty，若不足 ROLLBACK。
金融交易	交易的永續性極為關鍵，任何遺失都會產生重大風險。	innodb_flush_log_at_trx_commit = 1，結合雙寫 (primary + replica) 方案。
會員積分	積分加減必須保持一致，避免同時加分與扣分產生不正確的總額。	使用 REPEATABLE READ，在同一交易內完成所有積分操作。
批次匯入	大量資料寫入時，若中途失敗需回復至匯入前的狀態。	以 START TRANSACTION 包住整批 INSERT，失敗則 ROLLBACK。

---

總結

• ACID 是交易的四大保證：Atomicity（原子性）確保全有或全無；Consistency（相容性）讓資料永遠符合約束；Isolation（隔離性）防止多交易互相干擾；Durability（永續性）保證提交後資料不會遺失。
• MySQL 透過 InnoDB 引擎的 undo log、redo log、行鎖與多層隔離等級，完整實作了 ACID。
• 在實務開發中，務必 明確劃分交易範圍、妥善處理錯誤、選擇適當的隔離等級，並根據系統需求調整永續性參數。
• 只要遵守上述 最佳實踐，就能在高併發、關鍵業務的情境下，保持資料的正確性與系統的穩定性。

掌握 ACID，才能寫出可靠的 MySQL 應用！