MySQL 事務回滾深度解析：AUTO_INCREMENT 不回滾與物理/邏輯操作機制

問題一：為什麼 AUTO_INCREMENT 不會回滾？

現象： 事務中 INSERT 後 ROLLBACK，自增 ID 不會減少，下次插入繼續遞增。

根因：

1. 計數器在內存中： InnoDB 的自增計數器值保存在內存，非持久化到磁盤
2. 單向遞增： 運行期間只隨 INSERT 遞增，不隨 DELETE/ROLLBACK 減少
3. 啟動初始化： MySQL 啟動時執行 SELECT MAX(id) FROM table FOR UPDATE 獲取最大值初始化計數器

結論： 自增 ID 設計為**單調遞增、不重用**，保證唯一性與主從同步一致性，非 Bug 而是特性**。

問題二：MySQL 事務是物理操作還是邏輯操作？

Redo Log (重做日誌) - 物理操作

• 記錄**頁級別的物理修改** (如「頁 123 偏移 456 寫入 0xABCD」)
• COMMIT 時先寫 redo log (WAL 機制)，再刷盤
• 崩潰恢復時：讀取 redo log 重放物理操作，確保持久性

Undo Log (回滾日誌) - 邏輯操作

• 記錄**邏輯反操作** (如「插入一行」→「刪除該行」；「更新 A 為 B」→「更新 B 為 A」)
• 存儲於共享表空間的 undo segment 中
• ROLLBACK 時執行邏輯反操作，**非物理頁恢復**
• Undo 空間不即時回收，需 master thread 定期清理 (Purge)

對比總結表

實戰啟示

1. 不要依賴自增 ID 連續性： 業務邏輯勿假設 ID 無間隙
2. 大事務慎用： 長事務產生大量 Undo Log，佔用表空間，延遲 Purge，影響效能
3. 監控指標： 關注 History list length (Undo 積壓量)，過大說明 Purge 跟不上
4. 主從同步： 自增 ID 不回滾特性保證了基於 Statement 格式同步時主從 ID 一致

驗證示例

CREATE TABLE t_auto (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20)) ENGINE=InnoDB;

START TRANSACTION;
INSERT INTO t_auto (name) VALUES ('A');  -- id=1
INSERT INTO t_auto (name) VALUES ('B');  -- id=2
ROLLBACK;

INSERT INTO t_auto (name) VALUES ('C');  -- id=3 (非 1)
SELECT * FROM t_auto;  -- 僅顯示 id=3