主页 / dba / 问题 / 290443
Accepted
youcantryreachingme
Asked: 2021-04-26 15:31:20 +0800 CST

存储过程出错时，为什么要提交部分事务？

  • 772

我有一个存储过程，它首先声明一些变量，然后包含begin tran;在此之后，它对提供的参数执行一些验证（并在每次提供的参数验证失败时增加错误计数）。如果没有错误计数，则继续执行 7 次插入。在此之后，它有commit tran;

最近我在列表中添加了第 8 个插入。隐式类型转换意味着如果插入某些插入的数据将被截断。这向 SSMS 屏幕抛出了一个错误，但我发现前 7 个插入已提交，而第 8 个显然没有完成。

我很感激我可以包含一个try ... catch块来处理错误，但是如果一个显式begin tran;不能使整个工作块自治到commit，那么有什么意义呢？我错过了什么？

我知道我也许可以将我的程序调用包含在该级别的事务中 - 但是有人可以解释发生了什么以及为什么begin tran当包含在程序主体中时似乎不受尊重？如果调用该过程开始一个隐式事务，那么 proc 中的错误步骤是否不应该回滚由 proc 影响的所有更改 - 即使没有明确包括begin tran在 proc 主体中？

sql-server stored-procedures

2 个回答

  1. Best Answer

    事务不会在错误时自动回滚——这不是它们的设计目的。它们旨在使您能够回滚。但是，您仍然需要做一些事情来实现这一点。

    正如您所提到的，您可以通过 实现这一点TRY...CATCH，这使您可以最大程度地控制是否以及如何回滚。

    听起来您期待 的行为SET XACT_ABORT ON，您可以在存储过程中设置它，但这不是默认行为。文档中设置XACT_ABORTon vs off的描述是：

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

    当 SET XACT_ABORT 为 OFF 时，在某些情况下，只有引发错误的 Transact-SQL 语句被回滚并且事务继续处理。根据错误的严重程度，即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。OFF 是 T-SQL 语句中的默认设置，而 ON 是触发器中的默认设置。

    SET XACT_ABORT什么时候ON，调用故障过程会在 SSMS 中返回消息，例如：

    Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.

Completion time: 2021-04-26T10:51:00.8420902+10:00

    SSMS 中的第一行是红色的。

    当SET XACT_ABORTis时OFF，调用故障过程包括一条附加消息：

    Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-04-26T10:53:52.5951780+10:00

    特别是，当您看到以下消息时，这意味着过程中错误语句之前的语句已被提交：

    The statement has been terminated.
    • 16

  2. 如果调用该过程开始一个隐式事务，那么 proc 中的错误步骤不应该回滚由 proc 影响的所有更改

    不。即使您设置了SET IMPLICIT_TRANSACTIONS ON（很可能您没有，也不应该），执行/调用存储过程也不会开始事务。如果我没记错的话，这就是 PostgreSQL 的工作方式（函数内的任何错误都会自动回滚整个函数），而不是 SQL Server。

    添加到AMtwo 的答案中，您还可以在块中使用TRY...CATCHwith a来控制此行为。这是我更喜欢而不是使用的方法（除非通过链接服务器到其他 SQL Server 实例进行分布式事务，在这种情况下需要）。有关详细信息，请参阅我对以下问题的回答（也在 DBA.SE 上）：ROLLBACK TRAN;CATCHSET XACT_ABORT ON;SET XACT_ABORT ON;

    从一个存储过程启动3个存储过程时如何回滚

    • 3

相关问题