在不回滚事务的情况下将可恢复异常从存储过程发送到.net的方法

Question

我正在编写一个。net 7应用程序，它使用ADO.NET连接到Azure SQL数据库。

一些代码路径需要打开一个事务，并将多个命令(调用存储过程)作为同一事务的一部分发送到数据库。

存储过程大多是简单的原子CRUD操作，但如果应用实体及其底层存储不同(例如，为了性能或抽象)，它们也可能涉及多个语句。

我目前的需要是让存储过程返回一些值到应用程序层来表示一些部分故障但可恢复的状态(例如，对不存在的行进行UPDATE)。

在这些情况下，我也可能希望存储过程回滚事务到较早的保存点，但要避免回滚整个事务。

应用程序将使用自定义异常(意外的，可恢复的错误)来管理这些状态，所以我的第一种方法是在我的存储过程中使用THROW，因为异常可以很好地映射到ExecuteNonQuery()，并且还可以小心地映射到ExecuteReader()。

问题是，在我的存储过程中，我默认使用XACT_ABORT ON和TRY...CATCH，所以如果我从TRY块内部抛出一个自定义错误，事务将在批处理结束时回滚。

我考虑使用RAISERROR而不是THROW(根据微软文档，不应该尊重XACT_ABORT)。不过，在Azure SQL数据库上，我不能使用sp_addmessage，所以我只能返回错误50000;另外，微软建议THROW作为新的开发项目。

另外，我不认为我想设置XACT_ABORT OFF，因为如果抛出非自定义异常，我希望整个事务回滚，因为它实际上是一个意想不到的和不可恢复的情况。

我看到的唯一选项是自定义OUTPUT参数(例如，@State)或RETURN值，但这需要我确保输出/返回值始终映射到应用程序代码中，而不是默认抛出。

这些考虑是否正确?还有其他我不考虑的选择吗?

对于这种情况，我还使用返回值编写了这个存储过程模板草案。有什么我应该注意的吗?

create or alter procedure [App].[SampleProcedure]
as
declare
@savepointName char(32),
@shouldCommit bit;
begin
set xact_abort, nocount on;
set transaction isolation level read committed;
set @savepointName = replace(newid(), '-', '');
if @@trancount > 0
begin
set @shouldCommit = 0;
save transaction @savepointName;
end;
else
begin
set @shoudlcommit = 1;
begin transaction @savepointName;
end;
begin try
if /* some condition */ 1 = 0
begin
rollback transaction @savepointName;
return -1; /* Precondition failed */
end;
-- update set ... where ...;
if /* some condition */ 1 = 0
begin
rollback transaction @savepointName;
return -2; /* Condition failed */
end;
-- insert into ... values ...;
if isnull(@shouldCommit, 0) = 1
begin
commit transaction @savepointName;
return 0; /* Everything is fine */
end;
end try
begin catch
if @@trancount > 0
begin
rollback transaction;
end;
throw;
end catch
end;

Answer 1

经过一段时间，我得出了这些结论:

• "可采errors"也不例外。这样对待它们当然很方便，但是您可以检测并从中恢复的事实意味着它们只是您想到的罕见错误条件。
• 与c#异常相比，T-SQL异常针对的是需要回滚事务的异常、不可恢复的情况。在程序内部设置XACT_ABORT ON的最佳实践是证明。
• 根据前两点，返回值(或输出参数)是问题的正确解决方案。我选择返回值是因为它足以满足我的用例，并允许我保持EnableOptimizedParameterBinding启用。