SQL Server事務編程相關總結

爲了保證數據的一致性，事務在數據庫編程中經常用到，這裏對T-SQL中事務的寫法做一下總結與解讀。

T-SQL中三種事務寫法

• SQL Sever 2000時代的寫法，每條語句後都加@@Error判斷

/*如果只在最後一句加上IF @@ERROR <> 0 的判斷，是不行的，因爲@@ERROR只對最近最後執行的那條SQL有效，這樣中間出現的錯誤就判斷不了*/BEGIN TRAN;    INSERT INTO dual VALUES(-1);     IF @@ERROR <> 0     GOTO ErrM;    -- 除數爲0，出現運行時錯誤    INSERT INTO dual VALUES (1/0);    IF @@ERROR <> 0     GOTO ErrM;    INSERT INTO dual VALUES (1/1);    IF @@ERROR <> 0     GOTO ErrM;COMMIT TRAN;ErrM:    ROLLBACK TRAN;

• SQL Server 2005新增寫法之一，開頭加上 Set xAct_Abort On

SET XACT_ABORT ON;BEGIN TRAN;    INSERT INTO dual VALUES(-1);     INSERT INTO dual VALUES (1/0);    INSERT INTO dual VALUES (1/1);COMMIT TRAN;

• SQL Server 2005新增寫法之二，Try..Catch

BEGIN TRY    BEGIN TRAN;        INSERT INTO dual VALUES(-1);         INSERT INTO dual VALUES (1/0);        INSERT INTO dual VALUES (1/1);    COMMIT TRAN;END TRYBEGIN CATCH    ROLLBACK TRAN;END CATCH

關於Try..Catch

• Try..Catch並不能捕獲所有異常
  如下兩種異常，Try..Catch並不能捕獲：
  
  • 編譯錯誤，如語法錯誤等
  • 語句級重新編譯，如存儲過程中某個表名不存在等

例如，如下存儲過程，在新建時沒有問題，但在運行時就會報錯，而且CATCH中內容沒有執行：

Create PROC sp_MyTest ASBEGIN TRY    BEGIN TRAN;        INSERT INTO TableNotExist VALUES (0);    COMMIT TRAN;END TRYBEGIN CATCH    SELECT         ERROR_NUMBER() AS ErrorNumber,        ERROR_MESSAGE() AS ErrorMessage;        ROLLBACK TRAN;END CATCH;

• 嵌套事務回滾的問題
  如下代碼，兩個Try..Catch的事務處理嵌套，結果出現了報錯：

BEGIN TRY    BEGIN TRANSACTION;        INSERT INTO dual VALUES(1);            BEGIN TRY                BEGIN TRANSACTION;                    INSERT INTO dual VALUES(1/0);                COMMIT TRANSACTION;            END TRY            BEGIN CATCH                ROLLBACK TRANSACTION;            END CATCH     COMMIT TRANSACTION;END TRYBEGIN CATCH         ROLLBACK TRANSACTION;END CATCH

問題原因在於ROLLBACK默認是回滾所有事務，即直到最外面的事務。
可以通過在RollBack前加一個IF @@TRANSCOUNT > 0解決，或者開始事務前，設一個 SAVE TRANSACTION。

關於Set xAct_Abort On

這是從SQL Server 2005開始出現的一個選項，默認爲OFF，遇到異常時，只會回滾引發異常的那條語句，而繼續執行後續語句，如果設爲ON，則從異常語句開始回滾整個事務，不再繼續執行後續語句。
所以上面那個存儲過程sp_MyTest中Try..Catch無法處理的異常，如果一定要保證數據一致性，可以加上這句Set xAct_Abort On。