事务内部使用TSQL的Try/Catch还是相反？

Question

我正在编写一个脚本，将从多个表中删除记录，但在删除之前，必须返回一个计数以供用户在提交之前确认。

这是该脚本的概要。

BEGIN TRANSACTION SCHEDULEDELETE
    BEGIN TRY
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
    END TRY
    BEGIN CATCH 
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

            ROLLBACK TRANSACTION SCHEDULEDELETE
            PRINT 'Error detected, all changes reversed.'
    END CATCH

--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.

--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.

这是我第一次编写事务，将TRY/CATCH块放在事务内是否正确/最佳做法，还是应该将事务放在TRY块内?

在此脚本中，重要的因素是用户必须手动提交事务。

- Devasta

---

在 try/catch 块之外。 - cyan

除非是单用户模式数据库，否则不要等待最终用户提交事务。 - dean

@dean 那有问题吗？基本上，用户将被告知他们应该期望删除 X 条记录，并且如果数字匹配，立即提交。在事务运行时，这些表不会被写入（尽管其他表会被写入）。 - Devasta

我赞同@dean所说的。除非你的用户在这方面有一定经验，否则你将会遇到大麻烦。即使用户已经有经验，误操作也迟早会发生。如果可能的话，最好先填补信息空缺，以防止无法创建可靠的脚本。如果必须在提交之前确认结果，那么我建议默认将脚本设置为回滚状态。但即使是这样做也不是很好的实践。 - Kahn

如果他们事先知道受影响的记录数量，最好使用参数进行匹配时提交（并且没有发现错误），并在不匹配时回滚并输出原因。从长远来看，尽可能减少用户错误和暂时疏忽的空间是更好的选择，您可以将这些事情自动化，它们就越可靠。 - Kahn

@Kahn 这位用户是一名比我更有经验的数据库管理员。这个脚本最多每6个月运行一次。虽然这个脚本是100%可靠的（著名的最后一句话！），但我希望它能再增加一个检查，以防万一。 - Devasta

Answer 1

只有在TRY块内并且在实际语句之前打开事务并立即提交，不要等待控件到达批处理的末尾再提交事务。

如果您在TRY块中遇到问题并且已经打开了一个事务，则控制将跳转到CATCH块。在那里简单地回滚您的事务并进行其他所需的错误处理。

我添加了一个小检查，使用@@TRANCOUNT函数检查是否有未完成的事务，然后再回滚事务。在这种情况下，它并没有太多意义。当您在TRY块中执行一些验证检查（例如检查参数值和其他内容，并在TRY块中引发错误），然后再打开事务时，它才更有用。这种情况下，即使没有打开事务，控制也会跳转到CATCH块。在那里，您可以检查是否有未完成的事务，如果有则回滚。在您的情况下，除非在事务内部发生错误，否则您不需要检查是否有未完成的事务，因为您不会进入CATCH块。

在执行DELETE操作之后不要问是否需要提交或回滚。在打开事务之前进行所有这些验证。一旦打开事务，请立即提交，在出现任何错误时进行错误处理（您正在使用几乎所有的错误函数获取详细信息，这很好）。

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

- M.Ali

---

@Dai TRY/CATCH自2005年以来就一直存在于SQL Server中，在SQL Server 2005之前（即SQL Sever 2000和更早版本），我们无法做很多事情，或者做最简单的事情也需要非常复杂的方式。所以我希望你不要使用SQL Server 2000 :)。只是让你知道，在2005年之前，我们在每个语句后都使用@@Error函数来检查语句是否出错。 - M.Ali

@Ant_222，您能否详细阐述一下您的主张呢？我已经详细说明了我所提出的主张的原因。您只是发表了一个声明，但并没有提供任何理由来证明您所建议的方法是正确的。 - M.Ali

@Ant_222，很不幸你说的话完全胡说八道。如果在 try 块之外打开一个事务并且在 catch 块内不回滚，那么你会留下未关闭的事务。一旦打开了事务，你总是必须要回滚或提交事务。你声称如果一个事务失败了就不需要回滚是彻头彻尾的胡扯，抱歉说这句话伙计。至于我在 try 块内打开事务的原因，请仔细阅读我的答案中的第三段。(在打开事务之前进行额外的验证检查) 暗示暗示。 - M.Ali

注意你的措辞，Ali。如果 BEGIN TRAN 失败（我没有写 如果事务失败！），那么就没有什么可以 ROLLBACK 的了，一切都很好。另一方面，如果 BEGIN TRAN 成功，我们进入 TRY 块，从而保证要么 ROLLBACK，要么 COMMIT 成功启动的事务。所以你错了。你对 @@TRANCOUNT 的测试只是为错误放置的 BEGIN TRAN 找到的一种解决方法。将其放在 TRY 之前，你就不需要它了。如果你坚持认为我的方法是错误的，请发一个小的 T-SQL 脚本来打破它。好吗？ - Anton Shepelev

@M.Ali和@Ant_22，这是一次非常对抗性的讨论，我认为两种方法都有其优点。我的直觉是将TRAN放在TRY \ CATCH之外，在阅读了你们的整个讨论后，我感觉技术上并不重要（？），所以我会坚持使用TRAN -> TRY，因为在我的脑海中更有意义。在创建TRAN的块之外处理ROLLBACK就不太对。 - Morvael

Answer 2

除了M.Ali和dean上面提供的好建议外，这里还有一些为那些想在SQL SERVER中使用新的TRY CATCH THROW范式提供一点帮助的内容：

（我找不到完整的语法，所以在这里添加它）

GIST: 这里

这里是示例存储过程代码（来自我的GIST）：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROC [dbo].[pr_ins_test]
@CompanyID INT
AS
 
SET NOCOUNT ON
 
BEGIN
 
    DECLARE @PreviousConfigID INT
    
    BEGIN TRY
        BEGIN TRANSACTION MYTRAN; -- Give the transaction a name
        SELECT 1/0  -- Generates divide by zero error causing control to jump into catch
 
        PRINT '>> COMMITING'
        COMMIT TRANSACTION MYTRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN 
            PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MYTRAN; -- The semi-colon is required (at least in SQL 2012)
            
            
        END; -- I had to put a semicolon to avoid error near THROW
        THROW
    END CATCH
END

- Sudhanshu Mishra

---

仅仅是一点观察 - THROW 应该放在 BEGIN/END 之外，这样无论事务是否开启，都会抛出异常。 - Mark G

@MarkG 不太确定我理解了，如果THROW在BEGIN/END Catch之外，执行此存储过程将始终引发异常，除非您在提交tran后跟随一个return语句。就目前而言，仅将throw移动到外面是无用的。 - Sudhanshu Mishra

我怀疑@MarkG的意思是THROW应该在END之后但在END CATCH之前。这将确保无论@@TRANCOUNT如何，都会抛出异常。 - BrianB

我尝试在Visual Studio的数据库项目中添加THROW语句，但出现了错误。 - Rich

我处理 THROW 语句中的分号的方式是直接写 ;THROW; - 只要能用就行。我喜欢这样做是为了避免忘记添加分号。 - dyslexicanaboko

微软建议在事务中使用TRY-CATCH语句。 - variable

Answer 3

除非是单用户模式数据库，否则不要等待终端用户提交事务。

简单来说，这与阻塞有关。您的事务将对正在更新的资源采取一些独占锁，并将保持这些锁直到事务结束（提交或回滚）。没有人能够触摸这些行。如果使用版本存储清理，则会出现一些不同的问题。

最好首先发出选择查询以确定符合条件的行数，向最终用户展示此信息，在他确认后再进行实际删除。

- dean

---

这是正确的解决方案。但一定要记住适合删除的行，并精确地删除它们，而不是在用户确认操作时删除任意内容。 - Anton Shepelev