萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 交易(Transaction)完整指南:ACID 特性、分類、語法、巢狀與限制操作

交易是資料庫並發控制與一致性保障的基石。本文系統講解交易的四大 ACID 特性、三類啟動模式、核心 T-SQL 語法、錯誤處理模式、巢狀交易行為、不可包含的操作清單,並提供生產級錯誤處理範本。

什麼是交易?

不可分割的工作邏輯單元,包含一組 DML 操作(INSERT/UPDATE/DELETE),**要麼全做、要麼全不做**。

ACID 四大特性

特性英文核心含義
原子性Atomicity全部提交或全部回滾,無中間狀態
一致性Consistency交易前後資料庫滿足所有約束(主鍵、FK、CHECK、Trigger)
隔離性Isolation並發交易互不干擾,透過鎖/MVCC 實現
持久性Durability提交後永久生效,即使崩潰也可透過 Redo Log 恢復

三類交易啟動模式

模式特徵適用場景
顯式交易BEGIN TRAN ... COMMIT/ROLLBACK多語句業務邏輯、需精確控制邊界
自動提交單句自動 COMMIT/ROLLBACK(預設)簡單單句操作
隱式交易SET IMPLICIT_TRANSACTIONS ON 後,首句隱含 BEGINANSI 相容、遷移應用

核心語法

BEGIN TRAN[SACTION] [transaction_name | @tran_var]
  [WITH MARK ['description']]  -- 標記點,供日誌還原

COMMIT TRAN[SACTION] [transaction_name | @tran_var]

ROLLBACK TRAN[SACTION] [transaction_name | @tran_var | savepoint_name]

SAVE TRAN[SACTION] savepoint_name  -- 存儲點,部分回滾

標準錯誤處理模式 (TRY...CATCH + XACT_STATE)

BEGIN TRY
    BEGIN TRAN;
    
    UPDATE Accounts SET Balance -= 100 WHERE Id = 1;
    UPDATE Accounts SET Balance += 100 WHERE Id = 2;
    -- 故意錯誤: UPDATE Accounts SET Balance += 100 WHERE Id = 999;
    
    COMMIT TRAN;
    PRINT '轉帳成功';
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0  -- 1=可提交, -1=不可提交(必須回滾), 0=無交易
        ROLLBACK TRAN;
    
    DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrState INT = ERROR_STATE();
    
    RAISERROR(@ErrMsg, @ErrSeverity, @ErrState);  -- 重拋錯誤給呼叫端
    -- 或寫入錯誤日誌表
END CATCH;

巢狀交易與 @@TRANCOUNT

  • BEGIN TRAN 遞增 @@TRANCOUNT
  • COMMIT 遞減 @@TRANCOUNT,歸零時真正提交
  • ROLLBACK **直接歸零並全盤回滾**,忽略巢狀層級
  • 存儲點 SAVE TRAN 可實現部分回滾
BEGIN TRAN;           -- @@TRANCOUNT = 1
  BEGIN TRAN;         -- @@TRANCOUNT = 2
    SAVE TRAN sp1;
    UPDATE ...;
    ROLLBACK TRAN sp1; -- 僅回滾到存儲點
  COMMIT TRAN;        -- @@TRANCOUNT = 1
COMMIT TRAN;          -- @@TRANCOUNT = 0, 真正提交

不可包含在交易中的操作

這些操作會隱含提交或無法回滾:

  • DDL:CREATE/ALTER/DROP DATABASE, CREATE/ALTER/DROP TABLE
  • 備份/還原:BACKUP/RESTORE DATABASE/LOG
  • 磁碟管理:DISK INIT
  • 權限:GRANT/REVOKE/DENY
  • 系統配置:RECONFIGURE, sp_configure 生效
  • 複寫:DUMP TRAN (舊版)
  • 統計更新:UPDATE STATISTICS
  • LOAD DATABASE (舊版)

隔離等級與並發現象

隔離等級髒讀不可重複讀幻讀鎖行為
READ UNCOMMITTEDYYY無共享鎖
READ COMMITTED (預設)NYY讀取即釋放共享鎖
REPEATABLE READNNY保持共享鎖至結束
SNAPSHOTNNN版本存儲
SERIALIZABLENNN範圍鎖
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 會話級
SELECT * FROM sys.dm_tran_active_snapshot_database_transactions;  -- 監控版本存儲

死鎖與超時處理

  • SET LOCK_TIMEOUT 5000:鎖等待 5 秒自動報錯 (-1222)
  • SET DEADLOCK_PRIORITY LOW/NORMAL/HIGH:影響犧牲者選擇
  • 死錯圖分析:Trace Flag 1222、system_health Extended Events、sys.dm_tran_locks

交易管理的精髓在於:**縮小範圍、縮短持有、明確邊界、優雅失敗**。將業務邊界映射為交易邊界,配合 TRY...CATCH 與 XACT_STATE(),是寫出健壯資料存取層的關鍵。

copyright © 萬盛學電腦網 all rights reserved