MySQL 事務提交與回滾實戰：存儲過程中多表原子性操作模板

MySQL 事務提交與回滾實戰：存儲過程中多表原子性操作模板

在存儲過程中跨多表寫入時，必須確保「全成功或全失敗」。本文提供標準事務控制模板，解決 ROLLBACK 直接使用失效的常見誤區，採用 LOOP + LEAVE 模式實現精準流程控制。

一、事務控制關鍵字

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

MySQL 預設 AUTOCOMMIT=1，單條語句自動提交。存儲過程中需顯式開啟事務。

二、多表插入原子性模板

BEGIN
    loop_label: LOOP
        START TRANSACTION;

        -- 表 1
        INSERT INTO table1 (f_user_id) VALUES (user_id);
        IF ROW_COUNT() < 1 THEN
            SET @ret = -1;
            ROLLBACK;
            LEAVE loop_label;
        END IF;

        -- 表 2
        INSERT INTO table2 (f_user_id) VALUES (user_id);
        IF ROW_COUNT() < 1 THEN
            SET @ret = -1;
            ROLLBACK;
            LEAVE loop_label;
        END IF;

        -- 表 3
        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

三、關鍵設計要點

1. ROW_COUNT() 判斷上一條 INSERT/UPDATE/DELETE 影響行數，為 0 視為失敗；
2. 使用 LOOP + LEAVE 替代 IF...ELSE 深層巢狀，邏輯清晰、易維護；
3. AUTOCOMMIT=1 環境下，ROLLBACK 後無需額外 COMMIT，下一輪 START TRANSACTION 隱式結束上一事務；
4. 回傳值 @ret：0 成功，-1 失敗，應用層據此判斷業務流向。

四、擴展：Savepoint 部分回滾

若需「表 1 成功、表 2 失敗時僅回滾表 2」，可在各步驟後設 SAVEPOINT sp1，失敗時 ROLLBACK TO SAVEPOINT sp1，最後統一 COMMIT。