SQL Server 交易 (Transaction) 核心觀念：ACID 特性、隔離等級、鎖定機制與管理語法全解析

交易是資料庫一致性的基石。本文系統闡述交易的定義、ACID 四大特性、四種隔離等級 (Read Uncommitted 到 Serializable)、鎖定與封鎖關係、死鎖成因、常用 T-SQL 管理語法 (BEGIN/COMMIT/ROLLBACK/SAVE TRANSACTION) 與巢狀交易行為，並附上隔離等級選擇決策表，助你設計正確且高併發的資料庫操作邏輯。

什麼是交易？

• 單一邏輯工作單元，包含一或多條 T-SQL 陳述式 (SELECT/INSERT/UPDATE/DELETE)。
• 隱式交易：每條陳述式自動視為一個交易 (自動提交模式)。
• 顯式交易：以 BEGIN TRANSACTION 開啟，COMMIT 或 ROLLBACK 結束。
• 所有操作寫入交易記錄檔，透過 檢查點 機制定期將認可頁面刷新至資料檔，保證耐久性與可復原。

ACID 四大特性

交易管理核心語法

-- 開啟交易
BEGIN TRANSACTION [tran_name | @tran_name_var] [WITH MARK ['description']];

-- 認可交易 (寫入記錄檔認可標記，釋放鎖)
COMMIT TRANSACTION [tran_name | @tran_name_var];

-- 回滾交易 (撤銷所有操作，釋放鎖)
ROLLBACK TRANSACTION [tran_name | @tran_name_var | savepoint_name];

-- 設定儲存點 (支援部分回滾)
SAVE TRANSACTION savepoint_name;

-- 範例：巢狀交易與儲存點
BEGIN TRAN T1;
    INSERT INTO T1 VALUES (1);
    SAVE TRANSACTION SP1;
    BEGIN TRAN T2;  -- 實際上只是計數器 +1，不建立新交易
        INSERT INTO T2 VALUES (2);
        ROLLBACK TRANSACTION SP1;  -- 僅回滾到 SP1，T1 的 INSERT 保留
    COMMIT TRANSACTION T2;
COMMIT TRANSACTION T1;

關鍵規則：
• @@TRANCOUNT 遞增/遞減追蹤巢狀層級，只有最外層 COMMIT 真正寫入認可。
• ROLLBACK 無參數時回滾至最外層 BEGIN，@@TRANCOUNT 歸零。
• SAVE TRANSACTION 僅標記點，不影響 @@TRANCOUNT。

四種隔離等級 (由低到高)

補充：Snapshot 隔離 (Row Versioning based)

• Read Committed Snapshot (RCSI)：資料庫層級開啟，Read Committed 行為改為讀取版本存儲，讀不阻寫、寫不阻讀，無髒讀。
• Snapshot Isolation：交易層級指定，整個交易看到陳述式開始時的資料快照，支援樂觀併發，衝突時回滾。
• 啟用：ALTER DATABASE db SET READ_COMMITTED_SNAPSHOT ON / ALLOW_SNAPSHOT_ISOLATION ON (需單用戶模式)。

死鎖成因與預防

成因：交易 A 鎖資源 1 等資源 2，交易 B 鎖資源 2 等資源 1，形成循環等待。

預防原則：

1. 統一存取順序：所有交易按相同順序鎖定資源 (如先表 A 再表 B)。
2. 避免交易中等待使用者輸入：長時間持有鎖導致封鎖鏈。
3. 縮短交易範圍與持續時間：單一批次完成，減少網路往返。
4. 降低隔離等級：RCSI 或 READ UNCOMMITTED (NOLOCK) 減少共享鎖持有。
5. 使用繫結連接：同一應用多連線共享鎖空間。
6. 索引優化：減少掃描範圍 → 縮小鎖定粒度與時間。

隔離等級選擇決策表

深入理解交易與隔離機制，能讓你在一致性與併發性的天平上做出精準權衡，寫出既正確又高效的資料庫程式碼。