如何使用ADO.NET实现嵌套SQL事务？

Question

我需要使用ADO.NET在.NET中实现嵌套事务。

情况如下：

--> Start Process (Begin Transaction)       

   --> Do DB things       

   --> Begin Transaction for step 1
   --> Step 1
   --> Commit transaction for step 1

   --> Begin transaction for step 2
   --> Step 2
   --> Rollback transaction for step 2

   --> etc ...

   --> Do DB things       


--> End Process(Commit or Rollback ALL commited steps --> a.k.a the process)

这可以用事务范围来完成吗？有人能提供一个例子吗？

此外，我需要这个过程适用于 SQL Server 2005 和 Oracle 10g 数据库...事务范围能在这两种数据库引擎中工作吗？

编辑：请注意，以下情况可能会发生：

步骤1已提交， 步骤2已回滚 步骤3已提交。

该过程已提交

（步骤1和步骤3确实将数据存储到数据库中，步骤2则没有）

另一方面...

步骤1已提交， 步骤2已回滚 步骤3已提交。

该过程已回滚。

没有数据提交到数据库中。

注意：没有可用的数据库架构或域值。

- lestival

---

如果所有事务在失败时都会回滚，为什么要提交中间事务？因此，如果第n个事务失败，则从n-1到1的所有事务都将回滚，无论它们是否已提交。 - A G

但是他正在回滚一些内部事务，以便实际上可以提交一半的步骤并在最后回滚其余部分，或者回滚所有步骤。 - Jorge Córdoba

@Jorge：在结束进程时，他提到了回滚所有已提交的步骤...因此这是完全回滚还是提交而不是部分回滚。 - A G

我需要实现嵌套事务 - 为什么？在一个事务中，要么所有步骤都成功并完成事务，要么一步失败并回滚所有步骤。 - Paul Turner

Answer 1

您可以通过TSQL的保存点（在SQL Server上是SAVE TRAN）来实现这一点，但老实说我不建议这样做。您无法通过TransactionScope来实现，因为任何中止都是终端操作（只要树中的任何事务指示失败，整个事务就会回滚）。

个人建议：先检查数据，然后只执行有效操作。如果失败了，那就是终端-回滚它。可能将工作分成原子单位，可以在隔离中真正提交（或回滚）。

- Marc Gravell

---

可能出现的错误是由于违反外键约束而不是数据值之前可以检查，无论步骤2是否失败，步骤3仍然可以提交。感谢有关TSQL的信息，但我需要在ADO.NET上找到解决方案（我还必须使用Oracle）。 - lestival

@manza_jurjur - 抱歉，我不认同外键无法提前验证的说法。虽然SqlTransaction有一个Save()方法，但据我所知Oracle并没有这样的方法。你不能期望不存在的东西。 - Marc Gravell

当然可以，但在这种情况下，我不知道数据库模式和域值。 - lestival

Answer 2

关于 Oracle：

BEGIN    
  SAVEPOINT STEP1;
  -- do some things
  IF your_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP1;
  END IF;

  SAVEPOINT STEP2;
  -- do some other things
  IF your_other_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP2;
  END IF;

  -- SOME NUMBER OF OTHER STEPS

  IF your_criteria_for_all_step_commit_is_needed THEN
    COMMIT; -- commit all changes to DB
  ELSE
    ROLLBACK; -- rollback all changes
  END IF;
END;
/

- jva

---

我认为在事务中使用保存点的整个想法都有些可疑：当事务的某一部分失败时，整个事务应该被回滚。如果Step2成功或失败并不重要，那么为什么还要去管Step2呢？但是，如果您的工作单元是以一种奇怪的方式指定的，那么这绝对是实现它的方法。 - APC

非常适合于PL-SQL，但我能在ADO.NET上做到吗？ - lestival

Answer 3

本文讨论保存点和嵌套事务。请参考http://msdn.microsoft.com/en-us/library/ms971557.aspx。

- A G

---

谢谢！Jet提供程序是唯一支持嵌套事务的Microsoft OLE DB提供程序。 :-( - lestival

Answer 4

如果你想要所有步骤都被提交或者全部不提交，那么一个事务会更加合适。你可以将现有的事务对象传递给 ADO.Net Command 对象的 构造函数，从而在单个事务范围内执行多个更新操作。

- Simon