MySQL 事務提交與回滾實戰：多表操作的原子性保證

在涉及多張表的業務操作中，事務處理是確保資料一致性的核心機制。本文透過實際範例展示如何在儲存程序中正確實作事務的提交與回滾。

MySQL 事務控制語法

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

核心挑戰：多表插入的原子性

場景：在一個儲存程序中啟動事務，依序向三張表插入資料。每插入一張表需判斷是否成功，任一失敗需整體回滾，全部成功才提交。

關鍵點： 不能直接使用單純的 ROLLBACK，需結合條件判斷與流程控制，確保邏輯正確。

實戰範例：使用 LOOP 迴圈實現事務控制

BEGIN
    loop_label: LOOP
        START TRANSACTION;
        
        -- 插入第一張表
        INSERT INTO table1(f_user_id) VALUES(user_id);
        IF ROW_COUNT() < 1 THEN
            SET @ret = -1;
            ROLLBACK;
            LEAVE loop_label;
        END IF;
        
        -- 插入第二張表
        INSERT INTO table2(f_user_id) VALUES(user_id);
        IF ROW_COUNT() < 1 THEN
            SET @ret = -1;
            ROLLBACK;
            LEAVE loop_label;
        END IF;
        
        -- 插入第三張表
        INSERT INTO table3(f_user_id) VALUES(user_id);
        IF ROW_COUNT() < 1 THEN
            SET @ret = -1;
            ROLLBACK;
            LEAVE loop_label;
        ELSE
            SET @ret = 0;
            COMMIT;
            LEAVE loop_label;
        END IF;
    END LOOP;
    
    SELECT @ret; -- 返回執行結果
END

關鍵技術點解析

• ROW_COUNT()：返回上一條 INSERT/UPDATE/DELETE 影響的行數，用於判斷操作是否成功。
• LOOP + LEAVE：利用標籤迴圈模擬「嘗試-捕獲」邏輯，MySQL 預設自動提交，此結構確保回滾後不會意外提交。
• 變數 @ret：用戶定義變數（@ 開頭），用於在程序間傳遞執行狀態。
• 自動提交模式：MySQL 預設 autocommit=1，顯式 START TRANSACTION 會暫時關閉自動提交，直到 COMMIT 或 ROLLBACK。

替代方案：設定 autocommit=0

SET AUTOCOMMIT = 0;
-- 執行多條 SQL...
IF 失敗 THEN ROLLBACK; ELSE COMMIT; END IF;
SET AUTOCOMMIT = 1;

注意： 此模式下所有後續 SQL 都視為同一事務，直到明確結束，較難控制粒度，建議優先使用 BEGIN...COMMIT/ROLLBACK 區塊方式。

適用儲存引擎提醒

只有 InnoDB 和 BDB 儲存引擎支援事務處理！ 建表時務必指定 ENGINE=InnoDB。