本文 AI 產出，尚未審核

MySQL 交易（Transaction）

START TRANSACTION / COMMIT / ROLLBACK

簡介

在資料庫開發中，資料完整性與一致性是最根本的要求。即使是最簡單的增刪改操作，也可能因為網路斷線、程式錯誤或同時寫入衝突而留下不完整或不正確的資料。
MySQL 透過 交易（Transaction） 機制，讓一組相關的 SQL 敘述能夠 原子性（atomic）地執行：要麼全部成功，要麼全部失敗，資料庫永遠不會處於半完成的狀態。

本篇文章聚焦於 MySQL 中最常使用的三個指令：

  • START TRANSACTION（或 BEGIN）
  • COMMIT
  • ROLLBACK

我們將從概念說明、實作範例、常見陷阱與最佳實踐，最後延伸到真實的應用情境，幫助你在日常開發中正確、有效地使用交易。

核心概念

1. 什麼是交易？

交易是一組 邏輯上相關 的 SQL 語句，具備以下四大特性（ACID）：

特性 說明
Atomic（原子性） 所有語句要麼全部成功，要麼全部失敗。
Consistency（一致性） 交易執行前後，資料庫必須保持一致的狀態。
Isolation（隔離性） 交易之間互不干擾，避免讀到未提交的資料。
Durability（永久性） 一旦提交（COMMIT），變更即永久寫入磁碟，即使系統崩潰也不會遺失。

小提醒：MySQL 的預設儲存引擎 InnoDB 完全支援 ACID；若使用 MyISAM，則不支援交易。

2. 開始交易：START TRANSACTION / BEGIN

START TRANSACTION;
-- 或者簡寫
BEGIN;

兩者等價，會把連線的 自動提交模式（autocommit） 暫時關閉，直到執行 COMMIT 或 ROLLBACK 為止。此時，所有後續的 DML（INSERT、UPDATE、DELETE）都會被暫存於交易緩衝區。

3. 提交交易：COMMIT

COMMIT;

COMMIT 會把交易緩衝區的變更永久寫入磁碟，並自動恢復自動提交模式。此時其他連線才能看到這些變更。

4. 回滾交易：ROLLBACK

ROLLBACK;

若在交易過程中發生錯誤，或是業務邏輯判斷需要取消操作，執行 ROLLBACK 就會將所有變更撤銷，回到交易開始前的狀態。

程式碼範例

以下示範 5 個常見情境，讓你快速掌握交易的使用方式。

範例 1：簡單的扣款與入帳（原子性）

START TRANSACTION;

UPDATE accounts SET balance = balance - 100
WHERE user_id = 1 AND balance >= 100;

UPDATE accounts SET balance = balance + 100
WHERE user_id = 2;

COMMIT;

說明：若第一筆 UPDATE 因餘額不足而影響 0 行，整筆交易仍會提交。實務上應先檢查 ROW_COUNT()，若不足則 ROLLBACK。

範例 2：失敗時回滾（錯誤處理）

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 250);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, qty) VALUES
(@order_id, 101, 2),
(@order_id, 102, 1);

-- 假設第二筆 INSERT 因外鍵違反失敗
-- MySQL 會拋出錯誤，我們在應用程式端捕獲後執行 ROLLBACK

ROLLBACK;

說明：在程式碼（如 PHP、Node.js）捕獲例外後，務必呼叫 ROLLBACK，避免資料遺留下半完成的紀錄。

範例 3：使用 SAVEPOINT 與部分回滾

START TRANSACTION;

INSERT INTO inventory (product_id, qty) VALUES (200, 50);
SAVEPOINT after_inventory;

INSERT INTO shipments (product_id, qty, dest) VALUES (200, 30, 'TW');
-- 假設運送資料有誤，只想回到 inventory 的狀態
ROLLBACK TO SAVEPOINT after_inventory;

COMMIT;

說明：SAVEPOINT 允許在同一筆交易內設定多個回滾點，靈活控制哪一段需要撤銷。

範例 4：設定交易隔離等級（Isolation Level）

-- 讀取未提交的資料（Dirty Read）-- 只在測試環境使用
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

SELECT balance FROM accounts WHERE user_id = 3;

COMMIT;

說明：MySQL 支援四種隔離等級（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE），預設為 REPEATABLE READ，大多數情況不需要更改。

範例 5：自動提交模式的切換

-- 關閉自動提交，等同於每次手動 START TRANSACTION
SET autocommit = 0;

UPDATE products SET stock = stock - 5 WHERE product_id = 400;
UPDATE orders SET status = 'processed' WHERE order_id = 789;

COMMIT;   -- 必須手動提交
SET autocommit = 1;   -- 恢復預設

說明：直接關閉 autocommit 會讓所有後續語句自動納入同一筆交易，適合批次腳本或長時間執行的維護作業。

常見陷阱與最佳實踐

陷阱 可能的影響 解決方式
忘記 COMMIT 交易永遠不會釋放鎖，導致其他連線阻塞。 確保每條 START TRANSACTION 最終都有 COMMIT 或 ROLLBACK，可在程式碼中使用 finally 區塊保證。
在 READ COMMITTED 以外的隔離等級下使用長交易 產生大量的 undo log，佔用磁碟空間，甚至觸發「死鎖」。 盡量讓交易保持短小，只包住必要的語句。
混用自動提交與手動交易 可能導致部分語句提前提交，破壞原子性。 統一使用手動交易，或在需要時暫時關閉 autocommit。
忽略錯誤回傳 交易失敗卻仍執行 COMMIT，造成不一致資料。 在程式層捕捉例外，發生錯誤立即 ROLLBACK。
使用 MyISAM 引擎 完全不支援交易，所有 START TRANSACTION 會被忽略。 改用 InnoDB，或確認表格已轉換為支援交易的引擎。

最佳實踐

  1. 最小化交易範圍：只把必須一起成功的語句放入同一筆交易，減少 lock 持有時間。
  2. 明確檢查受影響列數：ROW_COUNT()、FOUND_ROWS() 能幫助判斷是否真的執行成功。
  3. 使用 SAVEPOINT：在複雜流程中加入回滾點，避免整筆交易回滾。
  4. 設定合理的隔離等級：大多數應用使用預設的 REPEATABLE READ；若讀取大量報表，可考慮 READ COMMITTED 以降低快照成本。
  5. 監控與日誌：將每次 START TRANSACTION、COMMIT、ROLLBACK 記錄到應用層日誌，便於排錯與審計。

實際應用場景

場景 為何需要交易 典型 SQL 流程
電商訂單處理 同時扣減庫存、產生訂單、更新客戶積分，必須全部成功。 START TRANSACTION → INSERT orders → UPDATE inventory → UPDATE members → COMMIT
銀行轉帳 兩筆帳戶餘額的變更必須同步完成，避免出現「負餘額」或「金額遺失」。 START TRANSACTION → UPDATE from_account → UPDATE to_account → IF ROW_COUNT()=2 THEN COMMIT ELSE ROLLBACK
批次匯入資料 大量寫入時若發現資料錯誤，需要一次回滾，保證匯入結果全或無。 SET autocommit=0; START TRANSACTION; LOAD DATA ...; IF error THEN ROLLBACK ELSE COMMIT; SET autocommit=1;
多階段審批流程 每個審批階段的變更需保留在同一筆交易中，確保流程不被中斷。 START TRANSACTION → UPDATE approvals → INSERT logs → COMMIT
報表快照 為了避免讀到未提交的變更，使用 READ COMMITTED 或 REPEATABLE READ 產生一致性快照。 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT ...; COMMIT;

總結

  • 交易是保證資料完整性的核心機制，START TRANSACTION、COMMIT、ROLLBACK 三指令構成了最基本的使用方式。
  • 充分理解 ACID 四大特性，才能在設計資料庫操作時正確運用交易。
  • 常見的陷阱包括忘記提交、長時間持有鎖、混用自動提交等，最佳實踐是保持交易短小、明確檢查執行結果、適時使用 SAVEPOINT。
  • 在電商、金融、批次匯入等實務場景中，交易是不可或缺的保護層，能讓系統在面對錯誤或異常時仍然維持 一致性 與 可靠性。

掌握了這些概念與技巧，你就能在 MySQL 上建立穩健、可擴充的資料庫應用，讓業務邏輯與資料安全同時得到保障。祝開發順利！