MySQL 事務提交與回滾常見誤區：自動回滾不存在，需手動控制或存儲過程處理

MySQL 事務控制的核心誤區與正確姿勢

許多開發者誤以為：「事務中任一語句失敗，數據庫自動回滾所有操作」。實際上 MySQL 不會自動回滾，必須顯式 ROLLBACK 或在應用層捕獲異常後回滾。

錯誤演示

CREATE TABLE test_tab (
  f1 INT PRIMARY KEY,
  f2 VARCHAR(11)
) ENGINE=InnoDB;

START TRANSACTION;
INSERT INTO test_tab VALUES (1, '2');  -- 成功
INSERT INTO test_tab VALUES (1, '3');  -- 主鍵衝突報錯 1062
COMMIT;  -- 未執行

結果：第一條 INSERT 已執行但未提交，若後續同連接執行任何 COMMIT/START TRANSACTION（隱式提交），第一條數據會被持久化！

正確理解

• 所有語句成功 → 手動 COMMIT
• 任一語句失敗 → 手動 ROLLBACK 撤銷所有
• 數據庫不會自動判斷成敗並回滾

方案一：應用層標志位控制（偽代碼）

bool m_flag = true;

void execute(MYSQL* conn, string sql) {
    if (mysql_real_query(conn, sql.c_str(), sql.length())) 
        m_flag = false;  // 標記失敗
}

bool commit_transaction() {
    if (!m_flag) {
        mysql_rollback(conn);  // 失敗回滾
        return false;
    }
    return mysql_commit(conn) == 0;  // 全部成功提交
}

方案二：存儲過程聲明異常處理器（推薦）

CREATE PROCEDURE sp_transfer()
BEGIN
    DECLARE t_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;
    INSERT INTO test_tab VALUES (1, '2');
    INSERT INTO test_tab VALUES (1, '3');  -- 失敗觸發 handler

    IF t_error = 1 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END;

CALL sp_transfer();

優勢：邏輯封裝在數據庫層，應用只需調用 CALL，無感知 SQL 細節，異常自動轉 ROLLBACK。

關鍵提醒

1. START TRANSACTION 會隱式提交前一個未完成事務
2. DDL（CREATE/DROP/ALTER）會隱式提交
3. 連接斷開相當於 ROLLBACK
4. 生產環境強烈建議用存儲過程或框架級事務管理器（如 Spring @Transactional）