Using Transaction Procedure Template

image

問題描述

在 SQL Server 中撰寫具有交易(Transaction)控制的預存程序，若考慮預存程序會被另一個預存程序或觸發呼叫，如上圖 ProcedureA 呼叫 ProcedureB，在兩個預存程序中均可能發生錯誤需要進行 Rollback Transaction，此時交易控制不能如下段這般簡單
BEGIN TRY
 BEGIN TRANSACTION;

 -- Do Something

 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION;
END CATCH;

解決方案

此時需要做一點手腳，以下這個範本適用在主要預存程序或被呼叫附屬預存程序，撰寫時不需要額外傳遞交易狀態等參數。
DECLARE @hasOuterTransaction bit = case when @@trancount > 0 then 1 else 0 end;
DECLARE @rollbackPoint nvarchar(32)=replace(convert(nchar(36), newid()), N'-', N'');
BEGIN TRY
    IF @hasOuterTransaction = 1
    BEGIN 
  SAVE TRANSACTION @rollbackPoint;
    END
    ELSE
    BEGIN
  BEGIN TRANSACTION @rollbackPoint;
    END;

 -- Do Something

    IF @hasOuterTransaction = 0
    BEGIN
  COMMIT TRANSACTION @rollbackPoint;
    END;
END TRY
BEGIN CATCH
    IF xact_state() = 1
    BEGIN
  ROLLBACK TRANSACTION @rollbackPoint;
    END;

    DECLARE @error_message nvarchar(1000)=ERROR_MESSAGE();
    THROW 51000, @error_message, 1;

END CATCH;

重點在
  1. 需要判斷當交易已經開啟時，使用 SAVE 指令。
  2. 執行 BEGIN , COMMIT, ROLLBACK 指令時均指定 Transaction 名稱，COMMIT 與 ROLLBACK 是否要執行得視交易當下的狀態。

參考資源

SQL Server 2008 error handling best practice