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

Answer 2

Hi Amitabh,

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 3

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.