TransactionScope 和存储过程？
我有两个 PL/SQL 存储过程，每个过程处理自己的事务（开始/提交和发生错误时回滚）。在.Net代码中，我调用这两个SP，如下所示。
using (TransactionScope ts = new TransactionScope())
{
CallSP1();
CallSP2().
ts.SetComplete();
}
如果我对 SP2 的调用失败，它会回滚 CallSP1() 所做的更改吗？如果它不回滚，那么这是否意味着从 .Net 应用程序而不是在存储过程中处理事务更好？
I have two PL/SQL Stored procedure each handling its own Transaction (Begin/Commit and Rollback in case of error). From .Net code I Call these two SP as shown below.
using (TransactionScope ts = new TransactionScope())
{
CallSP1();
CallSP2().
ts.SetComplete();
}
If my Call to SP2 fails will it roll back the changes made by CallSP1()? If it does not roll back then does that mean that its better to handle the Transaction from the .Net application instead of inside Stored Procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果第一个存储过程 SP1 发出提交，那么它已经做出的任何更改都将是永久性的。在这种情况下，如果 SP2 失败，SP1 所做的更改将不会回滚。
IMO 调用应用程序应该是处理事务逻辑的应用程序，即不要在 PL/SQL 过程中发出提交或回滚。让错误传播到调用应用程序，这样 PL/SQL 引擎将仅回滚失败过程完成的工作，而不是整个事务。
让调用应用程序决定发生错误时要做什么（重试、提交一半工作？或回滚）。
If the first stored procedure SP1 issues a commit then any changes it has already made will be permanent. In that case if SP2 fails, the changes made by SP1 won't be rolled back.
IMO the calling application should be the one handling the transaction logic, i.e. don't issue commit or rollback in your PL/SQL procedures. Let the errors propagate to the calling application, that way the PL/SQL engine will roll back only the work done by the failing procedure and not the entire transaction.
Let the calling application decide what to do in case of error (retry, commit half-work? or rollback).
如果 SP1 执行提交，则 SP2 中发生的情况并不重要。 SP1 中所做的更改不会回滚 - 它们已被提交。
If SP1 performs a commit, it doesn't matter what happens in SP2. The changes made in SP1 will not be rolled back - they've been committed.
如果您的第一个存储过程始终发出提交或回滚，那么第二个存储过程所做的任何操作都不会影响该事务。
如果您希望调用应用程序控制整个事务，但存储过程在发生错误时处理回滚自己的更改，则一种方法是在存储过程的开头定义一个保存点。然后，存储过程的异常块可以回滚到保存点，而不是事务的开始（在这种情况下，提交不应包含在存储过程中）。当然，在这种情况下，存储过程通知应用程序发生了错误，并让应用程序适当地处理该情况，这一点很重要。
If your first stored procedure always issues either a commit or a rollback, then nothing the second stored procedure does can affect that transaction.
If you want the calling application to control the overall transaction, but the stored procedures to handle rolling back their own changes in case of an error, one way to do this is to define a savepoint at the beginning of the stored procedure. Then the exception block of the stored procedure can rollback to the savepoint, rather than the beginning of the transaction (commits, in this case, should not be included in the stored procedures). Of course, in that scenario, it would be important for the stored procedure to notify the application that the error occurred and for the application to handle that case appropriately.