SQL Server XACT_ABORT带排除功能

Question

SQL Server XACT_ABORT带排除功能

sql-serverxact-abort

3

我有一个较大的存储过程，其中利用了几个TRY/CATCH块来捕获和记录单个错误。我还将整个过程的内容包装在事务中，以便在沿途引发错误时能够回滚整个过程（以防止大量混乱的清理）；由于否则不会回滚整个事务，因此已启用XACT_ABORT。

关键组件：我的数据库中有一个表，每次运行此过程时都会插入一条记录，其中包含操作结果和出现问题的详细信息。

有趣的事情正在发生-实际上，当我最终弄清楚出了什么问题时，它非常明显...插入语句到我的日志表也被回滚了，因此，如果我不是在SSMS中运行这个过程，我将无法看到甚至没有运行，因为回滚会删除所有活动痕迹。

问题：是否可能在除此之外的整个事务回滚时保留此单个插入语句？我仍然希望保留在运行存储过程期间编译的错误消息。

非常感谢！

~ Eli

更新6/28：以下是我正在查看的代码示例。与@Alex和@gameiswar提出的示例的关键区别在于，在我的情况下，所有try/catch块都嵌套在单个事务中。这样做的目的是有多个catch（针对多个表），尽管最后一个更新失败，我们仍希望整个混乱都被回滚。

SET XACT_ABORT ON;  
BEGIN TRANSACTION  
    DECLARE @message AS VARCHAR(MAX) = '';  

    -- TABLE 1
    BEGIN TRY
        UPDATE TABLE xx 
        SET yy = zz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 1 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH

    -- TABLE 2
    BEGIN TRY
        UPDATE TABLE sss 
        SET tt = xyz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 2 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH
COMMIT TRANSACTION

- Eli

当设置XaCT_ABORT为on时，您无法排除单个语句成功。 - TheGameiswar

@TheGameiswar 有什么解决方法吗？ - Eli

请查看我的更新答案。 - TheGameiswar

3个回答

2

好的...我能够通过结合Alex和GameisWar提出的好建议，再加上T-SQL GOTO控制流语句来解决这个问题。

基本思路是将错误消息存储在一个变量中，它可以在回滚后保留，然后让Catch将您发送到一个FAILURE标签，该标签将执行以下操作：

回滚事务
使用上述变量中的数据向日志表插入记录
退出存储过程

我还使用了第二个GOTO语句，以确保成功运行将跳过FAILURE部分并提交事务。

下面是测试SQL代码段的示例。它非常完美，我已经在我们的生产环境中实施并测试成功。

非常感谢所有的帮助和输入！

SET XACT_ABORT ON               
DECLARE @MESSAGE VARCHAR(MAX) = '';

BEGIN TRANSACTION 
    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST');     -- WORKS FINE
    END TRY 
    BEGIN CATCH     
        SET @MESSAGE = 'ERROR - SECTION 1: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST2');        --WORKS FINE
        INSERT INTO TEST_TABLE VALUES ('ANOTHER TEST'); -- ERRORS OUT, DATA WOULD BE TRUNCATED
    END TRY 
    BEGIN CATCH 
        SET @MESSAGE = 'ERROR - SECTION 2: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

GOTO SUCCESS;

FAILURE:        
    ROLLBACK
    INSERT INTO LOGG SELECT @MESSAGE
    RETURN; 

SUCCESS:
COMMIT TRANSACTION

- Eli

1

我不了解细节，但据我所见，一般的逻辑可能是这样的。

--set XACT_ABORT ON --not include it
declare @result varchar(max) --collect details in case you need it
begin transaction
begin try
--your logic here
--if something wrong RAISERROR(...@result)
--everything OK
commit
end try
begin catch
--collect error_message() and other into @result
rollback
end catch
insert log(result) values (@result)

- Alex Kudryashev

你的代码片段与我的代码结构非常相似，尽管有一系列的try/catch块。我通过error_message()获取错误消息，并将其附加到一个简短的注释中，让我知道尝试的语句的一些细节...然后将该消息插入到永久表中。不同之处在于将插入语句放在catch块内。它确实将消息写入表中，但在回滚时（我没有明确提到），该记录也会丢失。 - Eli

所以我们要同步思考。仔细考虑中间 try/catch 的可用性。 - Alex Kudryashev

Alex不确定你在中间的try/catch块方面是什么意思...根据这个存储过程的需求，我们需要更新大约10个表的系列，您会想要分别捕获每个表，以便知道哪个表出了问题以及参数是什么等等。您似乎使用了与@TheGameiswar相同的理论，其中将错误保留在一个变量中，该变量将在回滚时保留。我会试着玩一下，看看会得到什么结果。我非常感谢您的建议！ - Eli

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- TheGameiswar · Accepted Answer

你可以尝试以下代码，这将确保你记录操作。它利用了表变量不会被回滚的特性。 仅示意代码:

create table test1
(
id int primary key
)

create table logg
(
errmsg varchar(max)
)



declare @errmsg varchar(max)

set xact_abort on
begin try
begin tran
insert into test1
select 1

insert into test1
select 1

commit
end try

begin catch
set @errmsg=ERROR_MESSAGE()
select @errmsg as "in block"
if @@trancount>0
rollback tran

end catch
set xact_abort off


select @errmsg as "after block";

insert into logg
select @errmsg


select * from logg