如何确保嵌套事务彼此独立提交？

If I have a stored procedure that executes another stored procedure several times with different arguments, is it possible to have each of these calls commit independently of the others?

如果我有一个存储过程使用不同的参数多次执行另一个存储过程，是否可以让这些调用中的每一个独立于其他调用？

In other words, if the first two executions of the nested procedure succeed, but the third one fails, is it possible to preserve the results of the first two executions (and not roll them back)?

换句话说，如果嵌套过程的前两次执行成功，但第三次执行失败，是否可以保留前两次执行的结果（而不是回滚）？

I have a stored procedure defined something like this in SQL Server 2000:

我在SQL Server 2000中定义了类似的存储过程：

CREATE PROCEDURE toplevel_proc ..
AS
BEGIN

         ...

         while @row_count <= @max_rows
    begin
        select @parameter ... where rownum = @row_count 
        exec nested_proc @parameter
        select @row_count = @row_count + 1
    end

END

2 个解决方案

#1

First off, there is no such thing as a nested transaction in SQL Server

首先，SQL Server中没有嵌套事务

However, you can use SAVEPOINTs as per this example (too long to reproduce here sorry) from fellow SO user Remus Rusanu

但是，你可以使用SAVEPOINTs作为这个例子（太长时间来重复这里抱歉）来自SO用户Remus Rusanu

Edit: AlexKuznetsov mentioned (he deleted his answer though) that this won't work if a transaction is doomed. This can happen with SET XACT_ABORT ON or some trigger errors.

编辑：AlexKuznetsov提到（他删除了他的答案），如果交易注定失败，这将不起作用。 SET XACT_ABORT ON或某些触发错误可能会发生这种情况。

#2

From BOL:

来自BOL：

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

没有savepoint_name或transaction_name的ROLLBACK TRANSACTION回滚到事务的开头。嵌套事务时，此同一语句将所有内部事务回滚到最外面的BEGIN TRANSACTION语句。

I also found the following from another thread here:

我还从另一个帖子中找到了以下内容：

Be aware that SQL Server transactions aren't really nested in the way you might think. Once an explict transaction is started, a subsequent BEGIN TRAN increments @@TRANCOUNT while a COMMIT decrements the value. The entire outmost transaction is committed when a COMMIT results in a zero @@TRANCOUNT. But a ROLLBACK without a savepoint rolls back all work including the outermost transaction.

请注意，SQL Server事务并不像您想象的那样真正嵌套。一旦启动了一个explict事务，后续的BEGIN TRAN增加@@ TRANCOUNT，而COMMIT递减该值。当COMMIT导致零@@ TRANCOUNT时，将提交整个最外部事务。但是没有保存点的ROLLBACK会回滚所有工作，包括最外层的事务。

If you need nested transaction behavior, you'll need to use SAVE TRANSACTION instead of BEGIN TRAN and use ROLLBACK TRAN [savepoint_name] instead of ROLLBACK TRAN.

如果您需要嵌套事务行为，则需要使用SAVE TRANSACTION而不是BEGIN TRAN并使用ROLLBACK TRAN [savepoint_name]而不是ROLLBACK TRAN。

So it would appear possible.

所以它似乎有可能。

#1