# MySQL Transaction（交易）概念 Transaction 是一組「不可分割」的資料庫操作。這些操作要嘛全部成功，要嘛全部失敗，避免資料只更新一半而造成不一致。 例如銀行轉帳： 1. 從帳戶 A 扣款 2. 將款項存入帳戶 B 這兩個動作應該放在同一個 Transaction 中。若第二步失敗，第一步也必須復原。 --- ## 一、Transaction 的 ACID 特性 ### 1. Atomicity（原子性） Transaction 中的所有操作視為一個整體： - 全部成功：`COMMIT` - 任一步驟失敗：`ROLLBACK` ### 2. Consistency（一致性） Transaction 執行前後，資料必須符合資料庫定義的規則，例如： - 主鍵不可重複 - 外鍵關聯必須正確 - 欄位限制必須符合 ### 3. Isolation（隔離性） 多個 Transaction 同時執行時，彼此不應產生不合理的干擾。 MySQL 常見隔離等級： - `READ UNCOMMITTED` - `READ COMMITTED` - `REPEATABLE READ`，MySQL InnoDB 預設值 - `SERIALIZABLE` ### 4. Durability（持久性） Transaction 一旦執行 `COMMIT`，資料即使遇到系統重啟，也應該能保留。 --- # 二、MySQL Transaction 的基本條件 MySQL 的 Transaction 通常使用支援交易的儲存引擎，最常見的是： ```sql InnoDB ``` 可以查看資料表使用的儲存引擎： ```sql SHOW TABLE STATUS LIKE 'accounts'; ``` 或： ```sql SHOW CREATE TABLE accounts; ``` 建立 InnoDB 資料表： ```sql CREATE TABLE accounts ( account_id INT PRIMARY KEY, owner_name VARCHAR(50), balance DECIMAL(10, 2) ) ENGINE = InnoDB; ``` MyISAM 等不支援 Transaction 的儲存引擎，無法使用完整的 `COMMIT` 與 `ROLLBACK` 功能。 --- # 三、MySQL 的自動提交模式 MySQL 預設通常啟用 `autocommit`： ```sql SELECT @@autocommit; ``` 若結果為： ```text 1 ``` 表示每一個 SQL 指令執行成功後會自動提交。 例如： ```sql UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; ``` 在 `autocommit = 1` 的情況下，這個 `UPDATE` 執行成功後會立即提交，之後通常無法再用 `ROLLBACK` 還原。 關閉自動提交： ```sql SET autocommit = 0; ``` 重新開啟： ```sql SET autocommit = 1; ``` 不過實務上更常直接使用： ```sql START TRANSACTION; ``` 來開始一個明確的 Transaction。 --- # 四、基本 Transaction 操作 ## 1. 開始 Transaction 可以使用： ```sql START TRANSACTION; ``` 也可以使用： ```sql BEGIN; ``` 或： ```sql BEGIN WORK; ``` 最常見的是 `START TRANSACTION`。 --- ## 2. 提交 Transaction ```sql COMMIT; ``` `COMMIT` 會將本次 Transaction 中的所有修改永久寫入資料庫。 --- ## 3. 復原 Transaction ```sql ROLLBACK; ``` `ROLLBACK` 會取消本次 Transaction 中尚未提交的資料異動。 --- # 五、轉帳範例 假設有以下帳戶資料： ```sql CREATE TABLE accounts ( account_id INT PRIMARY KEY, owner_name VARCHAR(50), balance DECIMAL(10, 2) ) ENGINE = InnoDB; INSERT INTO accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00); ``` 進行 Alice 轉帳 100 元給 Bob： ```sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; ``` 執行 `COMMIT` 後，結果為： - Alice：900 - Bob：600 若中途發現錯誤，可以復原： ```sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- 假設此時發現收款帳戶不存在或其他錯誤 ROLLBACK; ``` 執行 `ROLLBACK` 後，Alice 的餘額會恢復原狀。 --- # 六、搭配條件檢查 實務上通常要檢查扣款是否成功，例如避免餘額不足： ```sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1 AND balance >= 100; -- 應在應用程式中檢查 UPDATE 影響筆數 -- 若影響筆數為 0，表示帳戶不存在或餘額不足 UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; ``` 如果第一個 `UPDATE` 沒有更新任何資料，應由應用程式執行： ```sql ROLLBACK; ``` 而不是繼續執行轉帳。 --- # 七、使用 Savepoint 部分復原 Transaction 中可以建立儲存點： ```sql START TRANSACTION; INSERT INTO orders(order_id, customer_id) VALUES (101, 1); SAVEPOINT order_created; INSERT INTO order_items(order_id, product_id, quantity) VALUES (101, 10, 2); -- 若只想取消儲存點之後的操作 ROLLBACK TO SAVEPOINT order_created; -- 最後仍可選擇提交前面的操作 COMMIT; ``` 常用指令： ```sql SAVEPOINT sp1; ``` 回到儲存點： ```sql ROLLBACK TO SAVEPOINT sp1; ``` 刪除儲存點： ```sql RELEASE SAVEPOINT sp1; ``` 注意：`ROLLBACK TO SAVEPOINT` 不會結束整個 Transaction；`COMMIT` 或完整的 `ROLLBACK` 才會結束 Transaction。 --- # 八、查詢 Transaction 隔離等級 查看目前隔離等級： ```sql SELECT @@transaction_isolation; ``` 設定目前 Session 的隔離等級： ```sql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` 設定常見隔離等級： ```sql SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ``` 也可以只針對下一個 Transaction 設定： ```sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; ``` --- # 九、鎖定資料列 在需要確保資料不被其他 Transaction 同時修改時，可以使用悲觀鎖： ```sql START TRANSACTION; SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE; ``` `FOR UPDATE` 會對查詢到的資料列加上排他鎖，直到： ```sql COMMIT; ``` 或： ```sql ROLLBACK; ``` 才會釋放。 例如： ```sql START TRANSACTION; SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; COMMIT; ``` 另一種共享鎖可使用： ```sql SELECT * FROM accounts WHERE account_id = 1 FOR SHARE; ``` --- # 十、Transaction 的注意事項 ## 1. 不要忘記 `COMMIT` 或 `ROLLBACK` 如果 Transaction 沒有結束，可能會： - 長時間持有鎖 - 阻塞其他使用者 - 造成死結或效能問題 --- ## 2. DDL 可能造成隱含提交 某些資料定義語言指令，例如： ```sql CREATE TABLE ALTER TABLE DROP TABLE TRUNCATE TABLE ``` 可能會造成隱含的 `COMMIT`。 因此，不應把一般資料異動和 DDL 混在同一個 Transaction 中，期待能使用 `ROLLBACK` 完整復原。 --- ## 3. Transaction 通常只對同一個資料庫連線有效 例如： - 連線 A 開始 Transaction - 連線 B 無法直接看到連線 A 尚未提交的變更 應用程式使用資料庫連線池時，要特別注意： - Transaction 必須在同一個 connection 中完成 - 連線歸還連線池前，應確實 `COMMIT` 或 `ROLLBACK` --- ## 4. Transaction 應盡量短 避免在 Transaction 中執行： - 長時間運算 - 等待使用者輸入 - 網路呼叫 - 大量不必要的查詢 Transaction 持續越久，鎖定資料的時間通常越長，越容易造成效能問題。 --- # 十一、完整操作流程 一般 Transaction 的流程如下： ```sql START TRANSACTION; -- 讀取資料 -- 檢查條件 -- 新增、修改或刪除資料 IF 操作全部成功 THEN COMMIT; ELSE ROLLBACK; END IF; ``` 在實際應用程式中，通常由程式處理錯誤： ```text begin transaction try: 執行 SQL 1 執行 SQL 2 執行 SQL 3 commit except error: rollback ``` --- ## 總結 MySQL Transaction 的核心指令是： ```sql START TRANSACTION; COMMIT; ROLLBACK; ``` 基本使用方式： ```sql START TRANSACTION; -- 多個相關的 SQL 操作 COMMIT; ``` 若發生錯誤： ```sql ROLLBACK; ``` 使用 Transaction 時，應選擇支援交易的 InnoDB 儲存引擎，並注意自動提交、隔離等級、資料列鎖定、DDL 隱含提交，以及 Transaction 的生命週期管理。