SQL Transcation的一些總結

1.1.1 摘要相信大家對于SQL Transcation再熟悉不過，它確保了數據庫的數據一致性和安全性，尤其在對數據執行增刪時，如果發生異常和錯誤它就會觸發事務回滾，從而確保了我們數據的一致性和...

1.1.1 摘要

相信大家對于SQL Transcation再熟悉不過，它確保了數據庫的數據一致性和安全性，尤其在對數據執行增刪時，如果發生異常和錯誤它就會觸發事務回滾，從而確保了我們數據的一致性和安全性，下面我們將通過分四部分介紹事件（Transcation）。

1.1.2 正文

首先讓我們通過一個具體的例子介紹Transcation的使用，假如我們的數據庫中有一個表UserInfo，它包含三個字段分別為：UserID(自增)、UserName (nvarchar)和LuckyNumber (tinyint)，如下圖所示：

圖1 UserInfo表

UserInfo表的sql代碼如下：

-- The definition of UserInfo.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfo](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [LuckyNumber] [tinyint] NOT NULL
) ON [PRIMARY]

接著我們要把數據插入到表UserInfo中，這里使用一個存儲過程把數據插入到該表中，存儲過程SPAddDataToUserInfo的定義如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
CREATE PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Hard code inserted data.
    INSERT INTO UserInfo VALUES('JKhuang', 8);
    INSERT INTO UserInfo VALUES('JKRush', 23);
    INSERT INTO UserInfo VALUES('Jackson', 20111111);
END
GO

現在我們已經定義了一個存儲過程，接著讓我們執行該存儲過程。

圖2執行存儲過程的消息

通過上圖我們知道在執行存儲過程中發生了異常，而且是由于值“20111111”數據超出了tinyint的范圍產生的，現在讓我們看一下數據插入的情況。

圖3 UserInfo表中數據

我們發現只插入了兩行數據，而第三行數據沒有成功插入，但為了確保數據完整性，我們要把數據全部插入或全部不插入，這時我們可以考慮使用Transcation來確保數據完整性和安全性。

接著讓我們修改一下存儲過程SPAddDataToUserInfo，在存儲過程中添加Transcation。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRANSACTION

            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;

            -- Hard code inserted data.
            INSERT INTO UserInfo VALUES('JKhuang', 8);
            INSERT INTO UserInfo VALUES('Jackson', 20111111);
            INSERT INTO UserInfo VALUES('JKRush', 23);

    COMMIT TRANSACTION
    
END
GO

現在我們再執行一次存儲過程看一下是否全部不插入到表中。

圖4 UserInfo表中數據

我們發現結果和沒有添加Transcation處理一樣，數據依然插入到表中。這究竟是什么原因呢？也許細心的你已經發現了，我們沒有添加事務回滾——ROLLBACK。

但我們究竟要在哪里添加事務回滾（ROLLBACK）呢？或更具體地說：“究竟什么時候我們要觸發事務回滾（ROLLBACK）呢”？

由于我們數據插入失敗是因為插入過程發生了異常情況，那么我們就要捕獲異常和處理異常，那就是TRY/CATCH的設計了，好讓我們繼續完善我們的存儲過程吧。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        ROLLBACK TRANSACTION

    END CATCH
    
END
GO

現在我們給存儲過程添加了異常處理機制TRY/CATCH（注意：SQLSERVER 2005或之后的版本才支持TRY/CATCH），接下來讓我們再執行一次存儲過程。

圖5 UserInfo表中數據

看上去我們已經把問題的解決了，我們知道存儲過程可以內嵌存儲過程或函數，所以我們把上面的存儲過程SPAddDataToUserInfo內嵌到存儲過程SPMultiDataToUserInfo中，SPMultiDataToUserInfo的定義如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
CREATE PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        ROLLBACK TRANSACTION

    END CATCH
    
END
GO

我們知道存儲過程SPAddDataToUserInfo會發生異常，它會回滾事務（ROLLBACK），但SPMultiDataToUserInfo是發生回滾還是繼續插入數據呢？

圖6 執行存儲過程消息

圖7 UserInfo表中數據

在插入的過程發生了異常，檢查UserInfo表數據并沒有插入表中，這符合我們設計的意圖，但我們發現異常不僅僅是產生于插入的數據超出了數值的范圍，還包含事務計數異常。

由于定義了兩個存儲過程，而且SPAddDataToUserInfo內嵌在SPMultiDataToUserInfo中，在執行這兩個存儲過程中，它們都發生了異常并且進行事務回滾（因為User為空）。

由于插入的數據超出了數值的范圍的異常是我們特意引起的，而事務計數異常這是預期之外的異常。

接下來讓我們看一下究竟是什么原因引起了該異常，這里我們通過輸出Transactions計數來查看問題所在。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION
        PRINT ERROR_MESSAGE();
        PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION
        PRINT ERROR_MESSAGE();
        PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

上面我們通過輸出事務的計數（@@TRANCOUNT）來查看在事務回滾時，事務計數器的變化。

圖8 存儲過程執行消息

通過上圖我們發現在執行SPMultiDataToUserInfo和SPAddDataToUserInfo時，事務計數器分別加1，當遇到SPAddDataToUserInfo中的異常時，事務回滾事務計算器置零。

當執行SPMultiDataToUserInfo中的事務時，由于事務計算器（@@TRANCOUNT）已經置零，導致拋出異常，現在我們明白了導致事務計數異常的原因，所以我們在進行事務回滾之前必須判斷事務計算器（@@TRANCOUNT）是否為0，如果為0就不回滾事務。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
        PRINT 'Rolled back successful in SPAddDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
        PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

現在我們增加了事務計數器的判斷，當計數器為0時，不進行事務回滾，這樣就沒有了之前事務計數器異常了。

sqltranscation7

圖9 存儲過程執行消息

現在對于事務我們有了進一步的了解，而且把以上的事務定義成為一個日常通用的模板，如下給出了一個基本的Transaction模板。

-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
    BEGIN TRANSACTION
 
    --
    -- You code here.
    --
 
    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0)
        -- Adds store procedure
        -- Writes the error into ErrorLog table.
        ROLLBACK TRANSACTION
    END IF
END CATCH

1.1.3 總結

事務是作為單個邏輯工作單元執行的一系列操作。可以是一條SQL語句也可以是多條SQL語句。

事務具有四個特性

原子性：不可分隔、成則具成、敗則具敗。

一致性：事務在完成時，必須使所有的數據都保持一致狀態

隔離性：獨立的執行互不干擾。由并發事務所作的修改必須與任何其他并發事務所作的修改隔離。

持久性：務完成之后，它對于系統的影響是永久性的。該修改即使出現系統故障也將一直保持。

應用程序主要通過指定事務啟動和結束的時間來控制事務。

啟動事務：使用 API 函數和 Transact-SQL 語句，可以按顯式、自動提交或隱式的方式來啟動事務。

結束事務：您可以使用 COMMIT（成功）或 ROLLBACK（失敗）語句，或者通過 API 函數來結束事務。

事務模式分為：顯示事務模式、隱式事務模式、自動事務模式。在SQL常用的是顯示模式。

創建事務的原則：

盡可能使事務保持簡短很重要，當事務啟動后，數據庫管理系統 (DBMS) 必須在事務結束之前保留很多資源、以保證事務的正確安全執行。

特別是在大量并發的系統中，保持事務簡短以減少并發資源鎖定爭奪，將先得更為重要。

1、事務處理，禁止與用戶交互，在事務開始前完成用戶輸入。

2、在瀏覽數據時，盡量不要打開事務

3、盡可能使事務保持簡短。

4、考慮為只讀查詢使用快照隔離，以減少阻塞。

5、靈活地使用更低的事務隔離級別。

6、靈活地使用更低的游標并發選項，例如開放式并發選項。

7、在事務中盡量使訪問的數據量最小。

posted @ 2011-12-11 22:42 JK_Rush 閱讀(11455) 評論(25) 收藏舉報

刷新頁面返回頂部

SQL Transcation的一些總結

1.1.1 摘要

1.1.2 正文

1.1.3 總結

關于作者:

公告