MySQL 事務無法回滾？存儲引擎選型與配置避坑指南

核心原因

表使用 MyISAM 引擎，不支援事務，COMMIT/ROLLBACK 無效果，隱性自動提交。

InnoDB vs MyISAM 關鍵差異

檢查與修改表引擎

-- 查看引擎
SHOW CREATE TABLE tbl_name;
SHOW TABLE STATUS LIKE 'tbl_name';

-- 修改引擎（會鎖表重建，大表慎用）
ALTER TABLE tbl_name ENGINE = InnoDB;

事務失效常見陷阱

1. 自動提交開啟：SET AUTOCOMMIT = 0 或連線字串加 autocommit=false。
2. DDL 隱性提交：CREATE/DROP/ALTER TABLE 會強制提交當前事務。
3. 非事務型表混用：事務中操作 MyISAM 表，該表變更無法回滾。
4. 儲存函數/觸發器：隱性提交，避免在事務中呼叫。

InnoDB 關鍵配置調優

[mysqld]
innodb_buffer_pool_size = 1G      # 實體記憶體 60-80%
innodb_flush_log_at_trx_commit = 1  # 1=最安全，0/2=高效能但可能丟 1 秒資料
innodb_file_per_table = 1          # 獨立表空間，利於管理
innodb_log_file_size = 256M        # 減少 checkpoint 頻率

驗證事務生效

SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 故意報錯或 ROLLBACK
ROLLBACK;
SELECT * FROM accounts WHERE id IN (1,2);  -- 餘額應未變