SQL 語言分類視角解析 COMMIT 與 ROLLBACK 用法：DDL/DML/DCL 隱性提交機制詳解

SQL 三大語言類別

COMMIT/ROLLBACK 核心機制

交易與回滾段

• DML 執行後，資料變更寫入回滾段（Undo Log），等待提交或回滾。
• COMMIT：確認變更，釋放鎖，回滾段標記可覆寫。
• ROLLBACK：撤銷變更，利用回滾段還原資料，釋放鎖。
• SELECT 不產生回滾段記錄（唯讀），結果集來自共用緩衝區/資料頁。

三種提交模式

隱式提交觸發指令清單

ALTER, AUDIT, COMMENT, CONNECT, CREATE, DISCONNECT, DROP, 
EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME, TRUNCATE

關鍵含義：若在交易區塊中執行上述指令，**當前交易會被隱式提交**，後續 ROLLBACK 無法撤銷交易開始至該指令前的 DML 操作。

實務陷阱與最佳實踐

陷阱 1：DDL 打斷交易

BEGIN TRAN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
CREATE TABLE tmp_log (...); -- 隱式提交！前面 UPDATE 已提交，無法回滾
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK; -- 僅回滾第二個 UPDATE，第一個已提交

對策：交易區塊嚴禁混入 DDL/DCL 隱性提交指令。結構變更放在交易外，或使用只支援交易 DDL 的資料庫（PostgreSQL、SQL Server 部分 DDL 支援交易回滾）。

陷阱 2：AUTOCOMMIT 導致資料不一致

-- AUTOCOMMIT ON 時
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 已提交
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 失敗（如鎖等待超時）
-- 結果：扣款成功、存款失敗，資料不一致

對策：批次業務務必 SET AUTOCOMMIT OFF / BEGIN TRAN 明確控制。

陷阱 3：SELECT 共用緩衝區誤解

SELECT 結果來自共用緩衝區（Buffer Pool），非回滾段。未提交的變更對其他連線**不可見**（隔離級別 READ COMMITTED），但同連線後續 SELECT 可見（隔離級別允許髒讀時）。

進階交易控制

-- 存檔點：大交易中建立部分回滾點
SAVEPOINT sp1;
UPDATE ...;
SAVEPOINT sp2;
DELETE ...;
ROLLBACK TO sp1; -- 僅回滾至 sp1，sp1 後操作保留
RELEASE SAVEPOINT sp1; -- 釋放存檔點

-- 交易隔離級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT;

跨資料庫差異提醒

總結

1. DML 需顯式 COMMIT，交易邊界由開發者控制。
2. DDL/DCL 觸發隱式提交，勿在交易中混用。
3. 關閉 AUTOCOMMIT 進行批次業務處理。
4. 理解隔離級別，正確處理並發一致性。
5. 善用 SAVEPOINT 實現大交易局部回滾。

從 SQL 語言分類理解 COMMIT/ROLLBACK，能避開隱性提交陷阱，建立健壯的交易控制習慣。