当 xact_abort 处于打开状态时，为什么 Sql Server 在引发错误之后继续执行？

我只是对 TSQL 中的一些东西感到惊讶

raiserror('Something bad happened', 16, 1);

将停止存储过程(或任何批处理)的执行。

但我的 ADO.NET 错误消息恰恰证明了相反的情况。我得到了异常消息中的两个提示错误错误消息，以及在此之后发生的下一个事件。

这是我的变通方法(这是我的习惯) ，但似乎没有必要:

if @somethingBadHappened
begin;
raiserror('Something bad happened', 16, 1);
return;
end;

医生说:

当 SETXACT _ ABORT 为 ON 时，如果 Transact-SQL 语句引发运行时错误，则整个事务将终止并回滚。

这是否意味着我必须使用显式事务？

59977 次浏览
小开

This is By DesignTM, as you can see on Connect by the SQL Server team's response to a similar question:

Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server.

Yes, this is a bit of an issue for some who hoped RAISERROR with a high severity (like 16) would be the same as an SQL execution error - it's not.

Your workaround is just about what you need to do, and using an explicit transaction doesn't have any effect on the behavior you want to change.

小开

If you use a try/catch block a raiserror error number with severity 11-19 will cause execution to jump to the catch block.

Any severity above 16 is a system error. To demonstrate the following code sets up a try/catch block and executes a stored procedure that we assume will fail:

assume we have a table [dbo].[Errors] to hold errors assume we have a stored procedure [dbo].[AssumeThisFails] which will fail when we execute it

-- first lets build a temporary table to hold errors
if (object_id('tempdb..#RAISERRORS') is null)
create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128));


-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
declare @tc as int;
set @tc = @@trancount;
if (@tc = 0)
begin transaction;
else
save transaction myTransaction;


-- the code in the try block will be executed
begin try
declare @return_value = '0';
set @return_value = '0';
declare
@ErrorNumber as int,
@ErrorMessage as varchar(400),
@ErrorSeverity as int,
@ErrorState as int,
@ErrorLine as int,
@ErrorProcedure as varchar(128);




-- assume that this procedure fails...
exec @return_value = [dbo].[AssumeThisFails]
if (@return_value <> 0)
raiserror('This is my error message', 17, 1);


-- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
if (@tc = 0)
commit transaction;
return(0);
end try




-- the code in the catch block will be executed on raiserror("message", 17, 1)
begin catch
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ERROR_PROCEDURE();


insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);


-- if i started the transaction
if (@tc = 0)
begin
if (XACT_STATE() <> 0)
begin
select * from #RAISERRORS;
rollback transaction;
insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
select * from #RAISERRORS;
insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
return(1);
end
end
-- if i didn't start the transaction
if (XACT_STATE() = 1)
begin
rollback transaction myTransaction;
if (object_id('tempdb..#RAISERRORS') is not null)
insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
else
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
return(2);
end
else if (XACT_STATE() = -1)
begin
rollback transaction;
if (object_id('tempdb..#RAISERRORS') is not null)
insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
else
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
return(3);
end
end catch
end
小开

Use RETURN immediately after RAISERROR() and it'll not execute the procedure further.

小开

As pointed out on the docs for SET XACT_ABORT, the THROW statement should be used instead of RAISERROR.

The two behave slightly differently. But when XACT_ABORT is set to ON, then you should always use the THROW command.

小开

microsoft suggests using throw instead of raiserror. Use XACT_State to determine commit or rollback for the try catch block

set XACT_ABORT ON;


BEGIN TRY
BEGIN TRAN;
    

insert into customers values('Mark','Davis','markdavis@mail.com', '55909090');
insert into customer values('Zack','Roberts','zackroberts@mail.com','555919191');
COMMIT TRAN;
END TRY


BEGIN CATCH
IF XACT_STATE()=-1
ROLLBACK TRAN;
IF XACT_STATE()=1
COMMIT TRAN;
SELECT ERROR_MESSAGE() AS error_message
END CATCH

提交答案