在父/子存储过程中分别打开/关闭XACT_ABORT有什么影响？ [英] What is the effect of having XACT_ABORT on/off in parent/child stored procedures respectively?

问题描述

我正在尝试改进当前系统的错误处理，以产生更有意义的错误消息。我有一个根存储过程，可以对其他嵌套存储过程进行多次调用。

I'm trying to improve the error handling of a current system to produce more meaningful error messages. I have a "root" stored procedure that makes several calls to other nested stored procedures.

在根sp中， XACT_ABORT 设置为 ON 但在嵌套过程中， XACT_ABORT 设置为 OFF 。我想从较低级别的程序中捕获特定错误，而不是获取根程序的错误。

In the root sp, XACT_ABORT is set to ON but in the nested procedures, XACT_ABORT is set to OFF. I want to capture the specific errors from the lower level procedures rather than getting the root procedure's error.

我经常看到错误，在批处理结束时检测到 uncommittable事务，事务正在回滚。

I often see the error, uncommittable transaction is detected at the end of the batch, the transaction is being rolled back.

使用 XACT_ABORTs 有这些混合环境有什么影响吗？

Is there any effect to having these "mixed" environments with the XACT_ABORTs?

此外，如果您对高级错误处理有任何建议，那将是非常感谢。我想我想使用 sp_executesql ，所以我可以传递参数来获取错误输出，而无需修改所有存储过程，并使用 RAISERROR 调用父进程的 CATCH 块。

Also, if you have any suggestions for advanced error handling, that would be much appreciated. I think I would like to use sp_executesql so I can pass parameters to get error output without having to modify all of the stored procedures and use RAISERROR to invoke the parent procedure's CATCH block.

推荐答案

根据 Andomar的回答和 MSDN：

SET XACT_ABORT的设置设置为执行或运行时，而不是
解析时间

The setting of SET XACT_ABORT is set at execute or run time and not at parse time

ie XACT_ABORT 不会从创建会话复制到每个过程，因此在内部未显式设置此选项的任何PROC将在运行时从环境会话继承设置时间，这可能是灾难性的。

i.e. XACT_ABORT will not be 'copied' from the creation session to each procedure, so any PROC which doesn't explicitly set this option internally will inherit the setting from the ambient session at run time, which can be disastrous.

FWIW，作为一般规则，我们始终确保 XACT_ABORT 在全球开放并做一个绒毛检查，以确保我们的PROC没有覆盖这个设置。

FWIW, as a general rule, we always ensure that XACT_ABORT is ON globally and do a lint check to ensure none of our PROCs have overridden this setting.

请注意， XACT_ABORT 不是一个银弹，但是 - 例如由RAISERROR 提交的错误的错误将不会终止批次。但是，在 SQL 2012中的THROW关键字

Note that XACT_ABORT isn't a silver bullet, however - e.g. errors that have been raised by your PROC with RAISERROR won't terminate the batch. However, it seems that this is improved with the THROW keyword in SQL 2012

如你所建议的，并且根据，结构化异常处理（TRY / CATCH）是一种更干净，更强大的处理机制的机制例外。

As you've suggested, and as per Remus Rusanu's observation, structured exception handling (TRY / CATCH) is a much more clean and robust mechanism for handling of exceptions.

这篇关于在父/子存储过程中分别打开/关闭XACT_ABORT有什么影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！