SQL Server TRY...CATCH 与 XACT_STATE

SQL Server TRY...CATCH with XACT_STATE

提问by ElPresidente

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

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

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

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

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

该示例首先COMMIT TRANSACTION在 Try 块中放置 a，然后在 Catch 块中放置第二个 if 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_STATEreturn 1? That seems contradictory.

但是我认为 Catch 块只会在出现错误的情况下执行。那么 Catch 块如何执行并XACT_STATE返回 1 呢？这似乎是矛盾的。

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

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

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

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

采纳答案by Sudhanshu Mishra

@user1181412 My analysis is as follows: This comment:

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

-- A FOREIGN KEY constraint exists on this table.

--This statement will generate a constraint violation error

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

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

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.

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

At the top, you have

在顶部，你有

SET XACT_ABORT ON;

设置 XACT_ABORT 开启；

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;

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

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

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

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

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

（1 行受影响）事务是可提交的。提交事务。

回答by Houssam Nasser

XACT_STATEis a function that returns to the user the state of a running transaction.
XACT_STATEindicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.

(Keep in mind that usually errors happen on update / insert and not on select queries).

XACT_STATE是一个函数，它向用户返回正在运行的事务的状态。
XACT_STATE指示请求是否有活动的用户事务，以及该事务是否能够被提交。

（请记住，错误通常发生在更新/插入而不是选择查询上）。

There are 3 status of XACT_STATE :

XACT_STATE 有 3 种状态：

1: query inside the Transaction block is active and valid (didn't throw an error).
0: The query will not throw an error (for example ,a select query inside transaction without update/insert queries).
-1: The query inside transaction threw an error (when entering the catch block) and will do a completerollback (if we have 4 succeeded queries and 1 throw an error , all the 5 queries will roll back ).

1: Transaction 块内的查询是活动且有效的（没有抛出错误）。
0：查询不会抛出错误（例如，没有更新/插入查询的事务内的选择查询）。
-1：事务内部的查询抛出错误（当进入 catch 块时）并将执行完整回滚（如果我们有 4 个成功的查询和 1 个抛出错误，则所有 5 个查询都将回滚）。

Example :

例子 ：

    BEGIN TRY    
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table.  
        -- This statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION; 
    END TRY
    BEGIN CATCH
    -- Test XACT_STATE for 0, 1, or -1.

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

References :
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sqlhttp://www.advancesharp.com/blog/1017/sql-transaction-status-and-xact-state

参考资料：
https: //docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql http://www.advancesharp.com/blog/1017/sql-transaction-status精确状态