SQL Server TRY...CATCH 与 XACT_STATE [英] SQL Server TRY...CATCH with XACT_STATE

问题描述

我有一个关于 TRY CATCH 块的 MSDN 文档的问题.查看这篇文章并向下滚动到示例 C使用 TRY…CATCH 和 XACT_STATE"

I have a question regarding the MSDN documentation for TRY CATCH blocks. Check out this article and scroll down to Example C "Using TRY…CATCH with XACT_STATE"

http://msdn.microsoft.com/en-us/library/ms175976.aspx

该示例首先在 Try 块中放置一个 COMMIT TRANSACTION，然后在 XACT_STATE()=1 时在 Catch 块中放置第二个.

The example first places a COMMIT TRANSACTION within the Try block, and then places a second one in the Catch block if XACT_STATE()=1.

但是我认为 Catch 块只会在出现错误时执行.那么 Catch 块如何执行并且 XACT_STATE 返回 1 呢?这似乎是矛盾的.

However I thought a Catch block will only execute in case of an error. So how could both the Catch block execute and XACT_STATE return 1? That seems contradictory.

XACT_STATE 文档中有一条未回答的评论提出了同样的问题

There is an unanswered comment within the XACT_STATE documentation which asks this same question

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

推荐答案

@user1181412 我的分析如下:这条评论:

@user1181412 My analysis is as follows: This comment:

-- 此表上存在 FOREIGN KEY 约束.

-- A FOREIGN KEY constraint exists on this table.

--此语句会产生约束违反错误

--This statement will generate a constraint violation error

是您问题的答案.发生的事情是，当 DELETE 语句执行时，它会生成一个约束冲突错误，并且后续的 COMMIT 没有执行.事务的 XACT_STATE 现在为 1，并且 CATCH 块正在执行.

is the answer to your question. What is happening is that when the DELETE statement executes, it generates a constraint violation error and the subsequent COMMIT does not execute. The XACT_STATE of the transaction is now 1 and the CATCH block is executing.

在顶部，您有

设置 XACT_ABORT 开启；

SET XACT_ABORT ON;

这会导致事务状态不可提交，因此此代码块将回滚事务:

This causes the transaction state to be uncommittable and hence this code block will rollback the transaction:

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

但是，如果您更改为SET XACT_ABORT OFF;"然后 CATCH 块将被命中，尽管事务状态将可提交"为 XACT_STATE = 1.

However, if you change to "SET XACT_ABORT OFF;" then the CATCH block would be hit albeit the transaction state will be "committable" as XACT_STATE = 1.

注意:删除仍然不会完成，因为约束违规仍然存在，但您会看到打印:

NOTE: Delete would still not be done as the constraint violation is still there, but you would see this printed:

(1 行受影响)事务是可提交的.提交交易.

(1 row(s) affected) The transaction is committable.Committing transaction.

这篇关于SQL Server TRY...CATCH 与 XACT_STATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！