在使用 TRY CATCH 的 SQL Server 中送出事務的最佳實踐

在 SQL Server 程式碼塊中，放置送出事務的最佳位置是什麼？在 try catch 塊內還是在它外面？

例如，選項 A 或選項 B 是正確的方法還是它們是主觀的選擇？

選項 A

CREATE PROCEDURE DummyProc 
BEGIN TRY
     BEGIN TRANSACTION
     INSERT sometable(a, b) VALUES (@a, @b)
     INSERT sometable(a, b) VALUES (@b, @a)
     COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     DECLARE @msg nvarchar(2048) = error_message()  
     RAISERROR (@msg, 16, 1)
     RETURN 55555
  END CATCH   

選項 B

CREATE PROCEDURE DummyProc 
BEGIN TRY
     BEGIN TRANSACTION
     INSERT sometable(a, b) VALUES (@a, @b)
     INSERT sometable(a, b) VALUES (@b, @a)

  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     DECLARE @msg nvarchar(2048) = error_message()  
     RAISERROR (@msg, 16, 1)
     RETURN 55555
  END CATCH
  IF @@trancount > 0  COMMIT TRANSACTION

在選項 B 中，當它在塊外送出時是否有可能發生一些錯誤TRY-CATCH？

我發現這樣做的最佳方法是以下程式碼：

SET XACT_ABORT ON;

BEGIN TRY
   BEGIN TRANSACTION

   /*
       Code goes here
   */

   COMMIT TRANSACTION
END TRY
BEGIN CATCH

   DECLARE @ErrorMessage NVARCHAR(4000);
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;

   SELECT 
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

   RAISERROR (@ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );

   -- If >= SQL 2012 replace all code in catch block above with
   -- THROW;

   WHILE @@TRANCOUNT > 0
   BEGIN
       ROLLBACK TRANSACTION;
   END

END CATCH

注意使用 XACT_ABORT 確保錯誤被有效擷取，BEGIN 和 COMMIT 語句都在 TRY 塊內，以及 @@Trancount 的 WHILE - 這應該確保嵌套事務被回滾（並不總是適用）

THROW 語句還可以替換 2012 年以上 SQL 版本的 RAISERROR，以重新拋出擷取的異常/錯誤。

正如 Dan Guzman 在他的評論中所說，XACT_ABORT 對於擷取 TRY/CATCH 構造不會的錯誤很有用，包括超時、執行時排序錯誤等

選項A是正確的選擇。事務中的所有語句都可能工作，然後實際的 COMMIT 失敗，因此您將 COMMIT 保留在 TRY 塊中，以便擷取 COMMIT 的任何失敗，並且您可以優雅地處理此錯誤和回滾。

在 SE 上查看此答案。在您的範常式式碼中，如果我們假設其他使用者同時執行此過程，則 TRY 塊可能會成功完成，然後由於其他會話所做的更改而在 COMMIT 上失敗。

引用自：https://dba.stackexchange.com/questions/233079