SQL Server的事務和錯誤處理

SQL Server的事務和錯誤處理

事務

事務組合一系列任務為一個執行單元。每個事務以特定的任務開始，以特定的任務結束。當所有的任務成功時事務成功，當任何一個任務失敗時，事務失敗。所以一個事務只有兩個結果：失敗或成功。
使用者能用下列指令組合兩個以上的T-SQL語句到一個事務中：
Begin Transaction 
Rollback Transaction 
Commit Transaction 
一組指令中出現任何錯誤，就需要使用Rollback撤銷執行。如果一個事務中的所有語句都按順序執行成功，那麼就需要把所有的改變記錄到資料庫中：Commit到資料庫。

USE pubs   

  

DECLARE @intErrorCode INT  

  

BEGIN TRAN   

    UPDATE Authors   

    SET Phone = '415 354-9866'  

    WHERE au_id = '724-80-9391'  

  

    SELECT @intErrorCode = @@ERROR   

    IF (@intErrorCode <> 0) GOTO PROBLEM   

  

    UPDATE Publishers   

    SET city = 'Calcutta', country = 'India'  

    WHERE pub_id = '9999'  

  

    SELECT @intErrorCode = @@ERROR   

    IF (@intErrorCode <> 0) GOTO PROBLEM   

COMMIT TRAN   

  

PROBLEM:   

IF (@intErrorCode <> 0) BEGIN  

PRINT 'Unexpected error occurred!'  

    ROLLBACK TRAN   

END  

在真正處理開始之前，使用BEGIN TRAN標記：下面所有的動作作為一個事務。裡邊包含兩個update語句。如果沒有出錯，當執行到COMMIT TRAN所有的改變被提交到資料庫，預存程序完成。如果在執行update出錯，通過檢測@@ERROR，跳轉到PROBLEM標籤，顯示錯誤資訊，復原這期間所有的修改。
注意：確保BEGIN TRAN 和COMMIT 或 ROLLBACK成對出現。

嵌套事務 

SQL Server 容許你嵌套事務，這個特性意味著，即使上一個事務沒有完成，也能開啟一個新事務。T-SQL容許你通過嵌套BEGIN TRAN嵌套事務。 內建變數@@TRANCOUNT可顯示事務的嵌套層級。0表示沒有嵌套，1表示嵌套一層，依此類推。

COMMIT提交本層事務，但是直到最外層的事務提交了，所有的改變才儲存到磁碟。它僅僅時減小@@TRANCOUNT的值。ROLLBACK不論在那層都能復原所有的事務。
當你開始一個事務，@@TRANCOUNT變數自動從0增加到1；當提交了數量減少1個。當復原了，則數量減為0。正如你所見：COMMIT和ROLLBACK並不對稱。如果你嵌套事務，COMMIT一層較少1，1。ROLLBACK命令復原所有事務2。COMMIT 和 ROLLBACK不同在於嵌套錯誤處理。

 圖1:一個commit對應一個BeginTransaction，把@@Trancount數量減1。

圖2：Rollback一直復原整個事務

從圖1和圖2可以看出：你可以嵌套事務，使用@@TRANCOUNT檢測嵌套的層級。也能瞭解COMMIT 和ROLLBACK不對稱的特性。COMMIT減少@@TRANCOUNT的值， ROLLBACK讓值變為0。只有最後一個COMMIT才真正提交事務。不管你嵌套多少事務，只有最後一個COMMIT起作用。

USE pubs   

SELECT 'Before BEGIN TRAN', @@TRANCOUNT  --  @@TRANCOUNT ：0   

  

BEGIN TRAN   

    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：1   

  

    DELETE sales   

    BEGIN TRAN nested   

        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT   

                   -- @@TRANCOUNT ：2   

  

        DELETE titleauthor   

    COMMIT TRAN nested   

                   -- 出了減少@@TRANCOUNT的值，不做任何事情

  

  

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT   

                   -- @@TRANCOUNT ：1   

  

ROLLBACK TRAN   

  

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：0    

  

-- 因為ROLLBACK TRAN 復原整個事務。 @@TRANCOUNT 被設定為 0.   

  

  

SELECT TOP 5 au_id FROM titleauthor   

USE pubs   

SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：0   

  

BEGIN TRAN   

    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：1   

  

    DELETE sales   

    BEGIN TRAN nested   

        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT   

               -- @@TRANCOUNT ：2   

  

        DELETE titleauthor   

    ROLLBACK TRAN   

     

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT   

    -- @@TRANCOUNT ：0 因為ROLLBACK TRAN 復原整個事務。 @@TRANCOUNT 被設定為 0.  

  

  

IF (@@TRANCOUNT > 0) BEGIN  

    COMMIT TRAN -- 永遠不會執行到這裡，因為事務被復原了。   

  

    SELECT 'After COMMIT TRAN', @@TRANCOUNT   

END  

  

SELECT TOP 5 au_id FROM titleauthor   

在這個例子中，永遠不會執行到COMMIT TRAN，因為ROLLBACK TRAN復原了所有的事務。除非ROLLBACK TRAN帶有一個儲存點調用，否則ROLLBACK TRAN一直復原所有事務，把@@TRANCOUNT設定為0。

SAVE TRAN 和儲存點 

儲存點提供了事務部分復原的機制。使用者能在事務內部設定儲存點或標記。儲存點定義一個當事務被撤銷時，復原的位置。SQL Server容許用

戶通過SAVE TRAN語句定義儲存點。申明儲存點不影響@@TRANCOUNT的值，復原到儲存點也不影響@@TRANCOUNT。復原必須ROLLBACK TRAN帶有儲存點的名稱，否則復原整個事務。

USE pubs   

SELECT 'Before BEGIN TRAN main', @@TRANCOUNT   

   -- @@TRANCOUNT :0   

  

  

BEGIN TRAN main   

    SELECT 'After BEGIN TRAN main', @@TRANCOUNT   

   -- @@TRANCOUNT :1   

  

    DELETE sales   

    SAVE TRAN sales  -- 設定一個儲存點   

  

    SELECT 'After SAVE TRAN sales', @@TRANCOUNT   

   -- @@TRANCOUNT 值仍然是 1   

  

  

    BEGIN TRAN nested   

        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT   

        -- @@TRANCOUNT :2   

  

        DELETE titleauthor   

        SAVE TRAN titleauthor  -- Mark a save point   

  

        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT   

        -- @@TRANCOUNT 仍然是 2   

  

    ROLLBACK TRAN sales   

  

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT   

     -- @@TRANCOUNT 仍然是 2   

  

  

    SELECT TOP 5 au_id FROM titleauthor   

  

IF (@@TRANCOUNT > 0) BEGIN  

    ROLLBACK TRAN   

    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT   

    -- @@TRANCOUNT ：0 因為ROLLBACK TRAN 復原整個事務。 @@TRANCOUNT 被設定為 0.  

  

END  

       

SELECT TOP 5 au_id FROM titleauthor   

錯誤處理 

下面的例子是一個和資料庫互動的很理想化的預存程序。當一個錯誤出現時，你所能做的是：終止代碼執行順序。要麼把代碼路由到別的代碼

，或者返回終止程式。@@ERROR這個系統變數常用來實現錯誤處理。它包含SQL語句最後執行錯誤的代碼。當代碼執行成功， @@ERROR的值是0
當代碼執行完成，立即使用IF語句判斷@@ERROR的值。這是因為當下一個語句執行成功時，@@ERROR的值會被重設為0。所以必須立即捕獲。經常在執行完NSERT, UPDATE, 或 DELETE語句後，你需要立即測試@@ERROR值的變化。

這種解決方案有很多重複的處理，特別是如果你的商務邏輯需要2個以上的T-SQL語句時。比較優雅的解決方案是組合所有的代碼，使用一個通用的錯誤處理。

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),    

                          @title VARCHAR(20), @title_type CHAR(12))   

AS  

  

BEGIN TRAN   

    INSERT titles(title_id, title, type)   

    VALUES (@title_id, @title, @title_type)   

  

    IF (@@ERROR <> 0) BEGIN  

        PRINT 'Unexpected error occurred!'  

        ROLLBACK TRAN   

        RETURN 1   

    END  

  

    INSERT titleauthor(au_id, title_id)   

    VALUES (@au_id, @title_id)   

  

    IF (@@ERROR <> 0) BEGIN  

        PRINT 'Unexpected error occurred!'  

        ROLLBACK TRAN   

        RETURN 1   

    END  

  

COMMIT TRAN   

  

RETURN 0