SQL服务器2005：混淆使用XACT_ABORT=ON与TRY… CATCH

Question

我对使用XACT_ABORT ON和TRY有点困惑...CATCH构造，用于在try块中出现错误时尝试回滚CATCH块中的事务。

我有一个这样结构的存储过程（当然这里简化了）：

CREATE PROCEDURE dbo.usp_clean_and_re_Insert
AS
   SET XACT_ABORT ON;

   BEGIN TRY

      BEGIN TRANSACTION

      -- first clear the table
      DELETE FROM dbo.table1

      -- re-populate the table
      INSERT INTO dbo.table1
      (col1, col2, col3)
      SELECT  1
              ,dbo.fn_DoSomething('20150101')
              ,dbo.fn_DoSomething('20150123')

      COMMIT TRANSACTION

   END TRY

BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should 
--     be rolled back.
-- XACT_STATE = 0 means there is no transaction and
--     a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;
END CATCH;

所以SP打算这样工作：如果事务在任何时候失败，它都应该回滚。所以当插入位失败时，删除位应该回滚，即表应该处于与以前相同的状态。

现在，假设在运行时dbo.fn_DoSomething()函数不可用(它被一个DBA错误地删除了)。上面写的SP按预期工作，即事务回滚，表保持不变，SSMS中显示的错误消息如下所示:

Msg 208，第16级，状态1，过程usp_clean_and_re_Insert，第15行无效对象名称'dbo.fn_DoSOmething'

但是由于某种原因，CATCH块中的PRINT语句似乎没有执行，即我在SSMS中看不到它们？Microsoft在TRY上留档… CATCH表示，如果在TRY块中执行期间发生错误，则执行将传递给CATCH块（https://msdn.microsoft.com/en-us/library/ms175976（v=sql.90）. aspx）。

但是，如果我去掉XACT_ABORT，事情会变得更加奇怪:

>

打印语句仍然不会出现在SSM中

正确显示与上述相同的错误，即：。

Msg 208，第16级，状态1，过程usp_clean_and_re_Insert，第15行无效对象名称'dbo.fn_DoSOmething'

Msg 266，第16级，状态2，过程usp_clean_and_re_Insert，执行后的第52行事务计数表示缺少提交或回滚事务语句。前一个计数=0，当前计数=1

这会导致表被锁定，直到我断开 SSMS（SP 运行的查询窗口）的连接，之后表将再次变为可用，并且所有结果都保持不变（因此数据库引擎必须隐式回滚不可提交的事务）。

阅读有关此错误消息的其他帖子（例如：EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配。以前的计数= 1，当前计数= 0），我知道我需要检查CATCH块中的XACT_STATE并回滚不可提交的事务（这是相同的建议：https://msdn.microsoft.com/en-us/library/ms189797.aspx），但这正是我在上面的SP中所做的，但是在我断开SSMS之前，事务不会回滚（没有XACT_ABORT ON）？

我很困惑！总而言之:

> < li>

为什么我在SSMS看不到书面声明？

为什么CATCH块中的ROLLBACK TRANSACTION在从存储过程中删除XACT_ABORTON时不执行？

如果XACT_ABORT ON似乎独立完成任务，为什么要使用TRY…CACTH呢？一、 e.如果我删除Try..catch并在其上保留XACT_ABORT，则会回滚事务，因此为什么我需要在catch块中使用隐式回滚事务进行Try-catch？

Answer 1

我认为，而且我可能是错的，在这种情况下，XACT_ABORT将不起作用，因为你实际上还没有开始交易。您的函数不存在，这意味着 SQL Server 甚至在您接触数据库之前就使您的事务失败。阅读手册页XACT_STATE，以及提供的示例，看起来您必须在读/写时实际失败。您的查询不会走那么远，因为优化程序发现您有语法错误（调用不存在的函数，在应该执行它时您尚未创建该函数）。

如果您阅读本页（XACT_STATE的手册页）和可用的示例，则强烈建议仅在遇到约束错误时设置XACT_ STATE，这仅在实际尝试更改数据时才会发生：

https://msdn.microsoft.com/en-us/library/ms189797.aspx

作为一种测试方法，您可以让探查器监视 dbo.table1 上的事务。我敢打赌，在查询中XACT_ABORT ON，删除不会发生。删除它后，优化程序可能会选择允许运行删除的不同执行计划。