MySQL 事務回滾兩大疑問：AUTO_INCREMENT 不回滾與物理/邏輯 Undo 機制

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

原因： InnoDB 將自增計數器保存在內存中，不持久化到磁碟。計數器特性：

• 只增不減：INSERT 遞增，DELETE/ROLLBACK 不遞減。
• 服務重啟時，執行 SELECT MAX(id) FROM table FOR UPDATE 重新載入最大值到計數器。

結論： 自增值不連續是正常現象，業務邏輯不應依賴連續性。

問題二：事務對表操作是物理操作嗎？

Redo Log（重做日誌）— 物理層面：

• 記錄頁面的物理變更（如「頁 X 偏移 Y 寫入值 Z」）
• Commit 時先寫 Redo Log（WAL），再刷臟頁到磁碟
• 崩潰恢復：讀 Redo Log 重放物理操作

Undo Log（回滾日誌）— 邏輯層面：

• 記錄邏輯反操作：INSERT 對應 DELETE，UPDATE 對應反向 UPDATE
• 儲存於共享表空間的 Undo Segment（rollback segment）
• Rollback 時執行邏輯反操作，而非直接恢復舊頁面
• Undo 佔用的空間不立即回收，由 master thread 定期 purge

關鍵差異對照

實務啟示

• 長事務會導致 Undo Segment 膨脹，阻塞 Purge，影響性能與磁碟空間。
• 自增主鍵不回滾特性可用於生成唯一業務單號，但不可作為連續流水號。
• 大量刪除後想重置自增值：TRUNCATE TABLE 或 ALTER TABLE t AUTO_INCREMENT = 1（需無資料）。