在Try Catch Block中提交 [英] Commit in Try Catch Block

问题描述

在SQL Server代码块中，提交事务的最佳位置是什么？在try内部或try catch之外。

In a SQL Server code block, what is the best place to place the commit transaction? Inside the try or outside the try catch.

例如，选项A或选项B是正确的方法吗？

For example, is option A or option B the correct approach?

选项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

推荐答案

我建议使用第一个构造，在SQL 2012及更高版本中，使用THROW重新引发原始错误。此外，将SET XACT_ABORT_ON添加到proc的下注中以确保在客户端超时的情况下立即回滚事务，因为在这种情况下不执行CATCH
块。

I suggest the first construct and, in SQL 2012 and later, use THROW to re-raise the original error. Also, add SET XACT_ABORT_ON to the betting of the proc to ensure the transaction is rolled back immediately in the event of a client timeout since the CATCH block is not executed in that case.

CREATE PROCEDURE DummyProc 
AS
SET XACT_ABORT ON;
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;
      THROW;
END CATCH;
GO

这篇关于在Try Catch Block中提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！