设置xact_abort并尝试一起捕捉

Question

我的sp中有一个try catch块，try中只有一个insert语句。捕捉检查错误代码，如果是pk违规，如果是则做更新。但是有时我会得到“当前事务不能被提交，不能支持写入日志文件的操作。回滚事务。

在批处理结束时检测到不可提交的事务。事务被回滚了“，所以我添加了xact_abort，但后来我不断得到”EXECUTE之后的事务计数表明 BEGIN 和 COMMIT 语句的数量不匹配“，我发现了这一点。http://www.ashishsheth.com/post/2009/08/14/Set-XACT_ABORT-ON-and-TryCatch-block-in-Sql-Server-2005.aspx

如果这是真的。如果我的 try 块中存在错误且启用了 xact_abort，我的捕获代码将不会运行？

Answer 1

让我补充一下，在这个特定场景中（尝试插入，如果PK冲突，则捕获并更新），最好使用if EXISTS（选择…）来查看行是否存在，并将更新语句放在那里。将INSERT语句放在ELSE块中。更干净。

Answer 2

至少在SQL SERVER 2008中，将XACT _中止设置为ON会导致跳过CATCH块的错误是不正确的:

这是我使用北风数据库尝试的代码

SET XACT_ABORT OFF
BEGIN TRY
    SELECT 1,  @@TRANCOUNT
BEGIN TRAN
    UPDATE [dbo].[Categories]
    SET Description='BLAH'
    WHERE [CategoryID]=2
    SELECT 2,  @@TRANCOUNT

    SELECT 1/0 as whoops


COMMIT
    SELECT 3,  @@TRANCOUNT

END TRY
BEGIN CATCH
    SELECT 'In Catch. Error occured', 4,  @@TRANCOUNT

     IF (XACT_STATE()) = 0
    BEGIN
        SELECT
            N'There is no transaction'

    END;


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

    -- Test whether the transaction is committable.
    IF (XACT_STATE()) = 1
    BEGIN
        SELECT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;

END CATCH

显然，这将在命中 SELECT 1/0 语句时强制出现错误。使用 SET XACT_ABORT OFF 时，当到达 CATCH 块时，XACT_STATE（） 函数返回的值为 1，从而导致代码运行该代码， 该代码使事务符合要求。当 SET XACT_ABORT 打开时，CATCH 块中返回的值为 -1，因此执行 ROLL 支持事务的代码。

这是基于：

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