TransactionScope and Stored Procedure?

Question

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?

Answer 1

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

Answer 2

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.