如何确保嵌套事务彼此独立地提交？ [英] How can I ensure that nested transactions are committed independently of each other?

问题描述

如果我有一个存储过程可以使用不同的参数多次执行另一个存储过程，那么是否有可能使每个调用独立于其他调用提交？

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)?

我在SQL Server 2000中定义了一个存储过程，如下所示：

I have a stored procedure defined something like this in 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

推荐答案

首先，在SQL Server中没有嵌套事务之类的东西

但是，您可以将 SAVEPOINTs 用作根据此示例（太长，无法在此重现，抱歉），来自SO同行用户 Remus Rusanu

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

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

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.

这篇关于如何确保嵌套事务彼此独立地提交？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！