MySQL 預存程序中使用事務：完整範本與關鍵細節

前言

在預存程序裡正確處理事務，是保證批次業務資料一致性的關鍵。本文提供一個生產級範本，涵蓋變數宣告、異常捕捉、迴圈批次寫入、提交/回滾決策，並解析易踩的坑。

完整範本代碼

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `createBusiness`(IN p_batch_count INT)
BEGIN
  -- 1. 變數宣告
  DECLARE v_flag       INT DEFAULT p_batch_count;  -- 迴圈計數
  DECLARE v_uuid_str   CHAR(32);                   -- 32 位 UUID
  DECLARE v_now        TIMESTAMP;                  -- 當前時間
  DECLARE v_err        TINYINT DEFAULT 0;          -- 錯誤旗標
  
  -- 2. 異常處理器：任何 SQL 例外將 v_err 置 1
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_err = 1;

  -- 3. 開啟事務
  START TRANSACTION;

  -- 4. 批次迴圈
  WHILE v_flag > 0 DO
    SET v_uuid_str = REPLACE(UUID(), '-', '');     -- 產生 32 位 UUID
    SET v_now      = CURRENT_TIMESTAMP();

    INSERT INTO `biz_events` (
      `id`, `title`, `keyword`, `has_image`, `is_today_head`,
      `is_show_home`, `is_bigness`, `publish_time`, `origin_id`,
      `modify_time`, `is_analysis`
    ) VALUES (
      v_uuid_str,
      CONCAT('事件標題', v_flag),
      CONCAT('關鍵字', v_flag),
      1, 1, 0, 0,
      v_now,
      CONCAT('xxxxxxx', v_flag),
      v_now,
      1
    );

    SET v_flag = v_flag - 1;                       -- 遞減計數

    -- 測試用：第 7 圈人為製造錯誤
    -- IF v_flag = 7 THEN SET v_err = 1; END IF;
  END WHILE;

  -- 5. 根據旗標決定提交或回滾
  IF v_err = 0 THEN
    COMMIT;
    SELECT 'OK' AS result;
  ELSE
    ROLLBACK;
    SELECT 'ERR' AS result;
  END IF;
END//

DELIMITER ;

關鍵細節深度解析

1. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

• 捕捉所有 SQL 錯誤（死鎖、鍵衝突、資料截斷等）
• CONTINUE 讓程式繼續執行後續邏輯（這裡是迴圈繼續或跳到 IF 判斷）
• 若需針對特定錯誤碼（如 1213 死鎖）可用 DECLARE CONTINUE HANDLER FOR 1213 SET v_err = 1;

2. 變數賦值必須用 SET 或 SELECT ... INTO

-- 錯誤
v_flag = v_flag - 1;
-- 正確
SET v_flag = v_flag - 1;
-- 或
SELECT v_flag - 1 INTO v_flag;

3. WHILE 迴圈不可空身

MySQL 要求迴圈體至少有一條語句，否則語法錯誤。

4. 事務邊界與自動提交

• 預存程序預設 autocommit=ON，必須顯式 START TRANSACTION
• COMMIT/ROLLBACK 只影響當前會話的事務
• 若呼叫端已開啟事務，巢狀行為視版本而定，建議「要麼全在程序裡管、要麼全在應用層管」

5. 回滾驗證技巧

註解掉測試區塊的 IF v_flag = 7 THEN SET v_err = 1; END IF;，執行程序後查表：

CALL createBusiness(10);
SELECT COUNT(*) FROM biz_events;  -- 應為 0（全回滾）

進階擴展：存檔點與部分回滾

START TRANSACTION;
SAVEPOINT sp1;
INSERT ...;  -- 步驟 1
SAVEPOINT sp2;
INSERT ...;  -- 步驟 2 出錯
ROLLBACK TO SAVEPOINT sp1;  -- 僅回滾步驟 2
COMMIT;                      -- 步驟 1 生效

效能與最佳實踐建議

1. 批次大小控制：單次事務建議 100~1000 行，避免長事務鎖住資源、Undo Log 膨脹
2. 錯誤碼分級：死鎖（1213）可重試，鍵衝突（1062）需業務判斷，其他直接回滾
3. 避免在迴圈裡 COMMIT：會破壞原子性，改用應用層分批呼叫多個小事務
4. 監控長事務：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

小結

預存程序事務範本核心四步：宣告旗標 → 設異常處理器 → START TRANSACTION → 業務迴圈 → IF 旗標 COMMIT/ROLLBACK。掌握 DECLARE HANDLER、SET 賦值、SAVEPOINT 部分回滾，能應對 95% 的批次寫入一致性場景。