事務操作(BEGIN/COMMIT/ROLLBACK/SAVE TRANSACTION)

標籤：

BEGIN TRANSACTION

標記一個顯式本地事務的起始點。 BEGIN TRANSACTION 使 @@TRANCOUNT 按 1 遞增。

BEGIN TRANSACTION 代表一點，由串連引用的資料在該點邏輯和物理上都一致的。 如果遇上錯誤，在 BEGIN TRANSACTION 之後的所有資料改動都能進行復原，以將資料返回到已知的一致狀態。 每個事務繼續執行直到它無誤地完成並且用 COMMIT TRANSACTION 對資料庫作永久的改動，或者遇上錯誤並且用 ROLLBACK TRANSACTION 語句擦除所有改動。

BEGIN TRANSACTION 為發出本語句的串連啟動一個本地事務。 根據當前交易隔離等級的設定，為支援該串連所發出的 Transact-SQL 陳述式而擷取的許多資源被該事務鎖定，直到使用 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 陳述式完成該事務為止。 長時間處於未完成狀態的事務會阻止其他使用者訪問這些鎖定資源，也會阻止日誌截斷。

雖然 BEGIN TRANSACTION 啟動一個本地事務，但是在應用程式接下來執行一個必須記錄的操作（如執行 INSERT、UPDATE 或 DELETE 語句）之前，它並不被記錄在交易記錄中。 應用程式能執行一些操作，例如為了保護 SELECT 語句的交易隔離等級而擷取鎖，但是直到應用程式執行一個修改操作後日誌中才有記錄。

文法

BEGIN { TRAN | TRANSACTION }

    [ { transaction_name | @tran_name_variable }

      [ WITH MARK [ ‘description‘ ] ]

    ]

[ ; ]

參數

transaction_name

分配給事務的名稱。 transaction_name 必須符合標識符規則，但標識符所包含的字元數不能大於 32。 僅在最外面的 BEGIN...COMMIT 或 BEGIN...ROLLBACK 嵌套語句對中使用事務名。 transaction_name 始終是區分大小寫，即使 SQL Server 執行個體不區分大小寫也是如此。

@tran_name_variable

使用者定義的、含有有效事務名稱的變數的名稱。 必須用 char、varchar、nchar 或 nvarchar 資料類型聲明變數。 如果傳遞給該變數的字元多於 32 個，則僅使用前面的 32 個字元；其餘的字元將被截斷。

WITH MARK [ ‘description‘ ]

指定在日誌中標記事務。 description 是描述該標記的字串。 長於 128 個字元的 description 先截斷為 128 個字元，然後才儲存到 msdb.dbo.logmarkhistory 表中。

如果使用了 WITH MARK，則必須指定事務名。 WITH MARK 允許將交易記錄還原到命名標記。

COMMIT TRANSACTION

標誌一個成功的隱性事務或明確交易的結束。僅當事務引用的所有資料在邏輯上都正確時，才應發出 COMMIT TRANSACTION 命令。 如果 @@TRANCOUNT 為 1，COMMIT TRANSACTION 使得自從事務開始以來所執行的所有資料修改成為資料庫的永久部分，釋放事務所佔用的資源，並將 @@TRANCOUNT 減少到 0。如果 @@TRANCOUNT 大於 1，則 COMMIT TRANSACTION 使 @@TRANCOUNT 按 1 遞減並且事務將保持活動狀態。

如果所提交的事務是 Transact-SQL 分散式交易，COMMIT TRANSACTION 將觸發 MS DTC 使用兩階段交易認可協議，以便提交所有涉及該事務的伺服器。 如果本地事務跨越同一資料庫引擎執行個體上的兩個或多個資料庫，則該執行個體將使用內部的兩階段交易認可來提交所有涉及該事務的資料庫。當 @@TRANCOUNT 為 0 時發出 COMMIT TRANSACTION 將會導致出現錯誤；因為沒有相應的 BEGIN TRANSACTION。

不能在發出一個 COMMIT TRANSACTION 語句之後復原事務，因為資料修改已經成為資料庫的一個永久部分。

僅當事務計數在語句開始處為 0 時，資料庫引擎才會增加語句內的事務計數。

文法

COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

[ ; ]

參數

transaction_name

SQL Server 資料庫引擎忽略此參數。 transaction_name 指定由前面的 BEGIN TRANSACTION 分配的事務名稱。 transaction_name 必須符合標識符規則，但不能超過 32 個字元。 transaction_name 通過向程式員指明 COMMIT TRANSACTION 與哪些 BEGIN TRANSACTION 相關聯，可作為協助閱讀的一種方法。

@tran_name_variable

使用者定義的、含有有效事務名稱的變數的名稱。 必須用 char、varchar、nchar 或 nvarchar 資料類型聲明變數。 如果傳遞給該變數的字元數超過 32，則只使用 32 個字元，其餘的字元將被截斷。

DELAYED_DURABILITY

請求將此事務與延遲持久性一起提交的選項。 如果已用 DELAYED_DURABILITY = DISABLED 或DELAYED_DURABILITY = FORCED 更改了資料庫，則忽略該請求。

ROLLBACK TRANSACTION

將明確交易或隱性交易回復到事務的起點或事務內的某個儲存點。 可以使用 ROLLBACK TRANSACTION 清除自事務的起點或到某個儲存點所做的所有資料修改。 它還釋放由事務控制的資源。ROLLBACK TRANSACTION 語句不產生顯示給使用者的訊息。 如果在預存程序或觸發器中需要警告，請使用 RAISERROR 或 PRINT 語句。 RAISERROR 是用於指出錯誤的首選語句。

文法

ROLLBACK { TRAN | TRANSACTION }

     [ transaction_name | @tran_name_variable

     | savepoint_name | @savepoint_variable ]

[ ; ]

參數

transaction_name

是為 BEGIN TRANSACTION 上的事務分配的名稱。 transaction_name 必須符合標識符規則，但只使用事務名稱的前 32 個字元。 嵌套事務時，transaction_name 必須是最外面的 BEGIN TRANSACTION 語句中的名稱。 transaction_name 始終是區分大小寫，即使 SQL Server 執行個體不區分大小寫也是如此。

@ tran_name_variable

使用者定義的、含有有效事務名稱的變數的名稱。 必須用 char、varchar、nchar 或 nvarchar 資料類型聲明變數。

savepoint_name

是 SAVE TRANSACTION 語句中的 savepoint_name。 savepoint_name 必須符合有關標識符的規則。 當條件復原應隻影響事務的一部分時，可使用 savepoint_name。

@ savepoint_variable

是使用者定義的、包含有效儲存點名稱的變數的名稱。 必須用 char、varchar、nchar 或 nvarchar 資料類型聲明變數。

SAVE TRANSACTION

在事務內設定儲存點。儲存點可以定義在按條件取消某個事務的一部分後，該事務可以返回的一個位置。 如果將交易回復到儲存點，則根據需要必須完成其他剩餘的 Transact-SQL 陳述式和 COMMIT TRANSACTION 語句，或者必須通過將交易回復到起始點完全取消事務。 若要取消整個事務，請使用 ROLLBACK TRANSACTION transaction_name 語句。 這將撤消事務的所有語句和過程。在事務中允許有重複的儲存點名稱，但指定儲存點名稱的 ROLLBACK TRANSACTION 語句只將交易回復到使用該名稱的最近的 SAVE TRANSACTION。

文法

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }

[ ; ]

參數

savepoint_name

分配給儲存點的名稱。 儲存點名稱必須符合標識符的規則，但長度不能超過 32 個字元。 transaction_name始終是區分大小寫，即使 SQL Server 執行個體不區分大小寫也是如此。

@savepoint_variable

包含有效儲存點名稱的使用者定義變數的名稱。 必須用 char、varchar、nchar 或 nvarchar 資料類型聲明變數。 如果長度超過 32 個字元，也可以傳遞到變數，但只使用前 32 個字元。

樣本

以下樣本說明如果活動事務是在執行預存程序之前啟動的，如何使用事務儲存點僅復原預存程序所做的修改。

USE AdventureWorks2012;GOIF EXISTS (SELECT name FROM sys.objects           WHERE name = N‘SaveTranExample‘)    DROP PROCEDURE SaveTranExample;GOCREATE PROCEDURE SaveTranExample    @InputCandidateID INTAS   -- 檢查是否是在活動的事務裡面調用該預存程序（嵌套事務）   -- @TranCounter = 0 表示不是在活動事務裡面調用   -- @TranCounter > 0表示在該預存程序調用之前已經有一個活動的事務    DECLARE @TranCounter INT;    SET @TranCounter = @@TRANCOUNT;    IF @TranCounter > 0       -- 在該預存程序調用之前已經有一個活動的事務。建立一個儲存點，如果這個預存程序出錯，只復原到執行預存程序之前的操作。        SAVE TRANSACTION ProcedureSave;    ELSE        -- 建立一個新的事務        BEGIN TRANSACTION;    -- Modify database.    BEGIN TRY        DELETE HumanResources.JobCandidate            WHERE JobCandidateID = @InputCandidateID;.        IF @TranCounter = 0            -- @TranCounter = 0 只在這個預存程序裡面有事務，必須提交事務            COMMIT TRANSACTION;    END TRY    BEGIN CATCH                                -- 錯誤發生，需要去判斷復原層級        IF @TranCounter = 0            -- 事務只在此預存程序中，復原整個事務            -- Roll back complete transaction.            ROLLBACK TRANSACTION;        ELSE            -- 事務在此預存程序開始之前已經建立（嵌套事務）              -- XACT_STATE()，用於報告當前正在啟動並執行請求的使用者事務狀態的純量涵式。 XACT_STATE 指示請求是否有活動的使用者事務，以及是否能夠提交該事務。              --  XACT_STATE() = 1 ，當前請求有活動的使用者事務。 請求可以執行任何操作，包括寫入資料和提交事務。              --  XACT_STATE() = 0，當前請求沒有活動的使用者事務。              --  XACT_STATE() = -1 ，當前請求具有活動的使用者事務，但出現了致使事務被歸類為無法提交的事務的錯誤。 請求無法提交事務或復原到儲存點；它只能請求完全復原事務。              -- 請求在復原事務之前無法執行任何寫操作。 請求在復原事務之前只能執行讀操作。 交易回復之後，請求便可執行讀寫操作並可開始新的事務。            IF XACT_STATE() <> -1              -- 復原到此預存程序開始之前的錯作。                ROLLBACK TRANSACTION ProcedureSave;        -- 輸出錯誤資訊        DECLARE @ErrorMessage NVARCHAR(4000);        DECLARE @ErrorSeverity INT;        DECLARE @ErrorState INT;        SELECT @ErrorMessage = ERROR_MESSAGE();        SELECT @ErrorSeverity = ERROR_SEVERITY();        SELECT @ErrorState = ERROR_STATE();        RAISERROR (@ErrorMessage,                   @ErrorSeverity,                   @ErrorState                   );    END CATCHGO

事務操作(BEGIN/COMMIT/ROLLBACK/SAVE TRANSACTION)