VB OracleTransaction Not Rolling back

Question

Hi,

I'm calling a third party stored procedure in Oracle from VB that is not rolling back.

Code first (I'm simplifying):

Connection string:

String sqlstr = "SERVER=x.x.x.x;Database=db;uid=sa;pwd=admin;Connect Timeout=60; Min Pool Size=5; Max Pool Size=100;";

The call (I've just forced a rollback immediately after the execute to test it):

Dim Oraclecon As New OracleConnection(_OracleConnection)
Dim sqlCon As New SqlConnection(_SQLConnection)
Dim oTrans As OracleTransaction = Nothing

 Oraclecon.Open()
 oTrans = Oraclecon.BeginTransaction()
 Dim myCMD As New OracleCommand()
 myCMD.Connection = Oraclecon
 myCMD.Transaction = oTrans
 myCMD.CommandText = "CREATE_USER"
 myCMD.CommandType = CommandType.StoredProcedure
 myCMD.Parameters.Add(New OracleParameter("username", OracleType.VarChar)).Value = UserName
 myCMD.Parameters.Add(New OracleParameter("passwd", OracleType.VarChar)).Value = Password
 myCMD.Parameters.Add(New OracleParameter("speed", OracleType.VarChar)).Value = Speed
 myCMD.Parameters.Add(New OracleParameter("monthly_quota", OracleType.VarChar)).Value = Quota
 myCMD.Parameters.Add(New OracleParameter("type", OracleType.VarChar)).Value = "H"
 Dim oparam As OracleParameter
 oparam = New OracleParameter("success_flag", OracleType.VarChar)
 oparam.Size = 1
 oparam.Direction = ParameterDirection.Output

 Dim oparam2 As OracleParameter
 oparam2 = New OracleParameter("err_msg", OracleType.VarChar)
 oparam2.Direction = ParameterDirection.Output
 oparam2.Size = 100

 myCMD.Parameters.Add(oparam)
 myCMD.Parameters.Add(oparam2)

 Dim RowId As OracleString

 myCMD.ExecuteOracleNonQuery(RowId)
 oTrans.Rollback()

I can't give the details of the stored procedure but it does a commit and rollback inside it.

Either way, it is doing an insert, and that immediate rollback does not rollback the insert.

Any ideas?

Answer 1

Without the source of the package this one could be tricky.

For starters, Oracle normally raises an exception if you do a COMMIT or ROLLBACK inside a package that is not marked with the AUTONOMOUS TRANSACTION pragma, regardless of the calling language.

So I presume that if the package contains a COMMIT and ROLLBACK then the package MUST be autonomous. This would mean that your client side rollback wouldn't have any impact.

If a package is autonomous then an exception is raised if you exit the package without having done either a ROLLBACK or COMMIT - i.e. there should be no route where the package could exit, leaving an uncommitted row on the database.

The only situation that could explain what you are seeing is if the rollback FAILED, or was not executed at all, and the code then carried on to COMMIT.

(Another possible option - the Insert has ALSO been done as an autonomous transaction, with a self-contained commit, so that the data was committed before the rollback?).

Autonomous transactions are notorious for (re)-introducing the problems that atomic transactions are there to avoid (race conditions, data integrity, deadlocks) - they have their uses, but must be handled with care.

Answer 2

The Commit/Rollback logic in the PL/SQL (regardless of AUTONOMOUS TRANSACTION clause --> DO NOT USE THIS unless you are error logging: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2212445691154)

So if you issue a COMMIT in your package your data is commited, PERIOD. Rollback the same way.

Create table for the following examples:

create  table SHOW_TRANSACTION (MISC varchar2(15)) ;
/

Looking at this example shows just that:

declare

   procedure DOTHEINSERT(DATAM in SHOW_TRANSACTION.MISC%type ,
                        doCommit IN boolean

   ) 
   as
   begin    
            insert into SHOW_TRANSACTION (MISC) 
            values (DATAM);

            if DOCOMMIT   then
               commit ;
            else
               ROLLBACK ;
            end if;
   end ;
begin
    DOTHEINSERT('commit1' , true);
    DOTHEINSERT('NOcommit1' , false);
    DOTHEINSERT('commit2' , true);
    DOTHEINSERT('NOcommit2' , false);
    DOTHEINSERT('commit3' , true);
    ROLLBACK;


end ;
/

select * from SHOW_TRANSACTION ;
/
MISC            
--------------- 
commit1         
commit2         
commit3   

Notice the final ROLLBACK does nothing? this is because the COMMIT/ROLLBACKS in the procedure are effecting the entire scope, look at this example:

truncate table SHOW_TRANSACTION; --start with clean slate

declare

   procedure DOTHEINSERT(DATAM in SHOW_TRANSACTION.MISC%type ,
                        doCommit IN boolean

   ) 
   as
   begin    
            insert into SHOW_TRANSACTION (MISC) 
            values (DATAM);

            if DOCOMMIT   then
               commit ;
            --else (HERE I AM GETTING RID OF THE PROCEDURES ROLLBACK, SO EVERYTHING IS BEING ROLLBACK'ED
            --   ROLLBACK ;

            end if;
   end ;
begin
    DOTHEINSERT('commit1' , true);
    DOTHEINSERT('NOcommit1' , false);
    DOTHEINSERT('commit2' , true);
    DOTHEINSERT('NOcommit2' , false);
    DOTHEINSERT('commit3' , true);
    ROLLBACK;


end ;
/

select * from SHOW_TRANSACTION ;
/

MISC            
--------------- 
commit1         
NOcommit1       
commit2         
NOCOMMIT2       
commit3     

Here the package ROLLBACK is removed, so when that COMMIT happens, the contents of all the INSERTS prior to that are inserted.

If you want the VB application to handle the transaction, you must remove the commit/rollback from the PL/SQL.

Also, it does not matter where the commit/rollback are, they are indicative to the ENTIRE SCOPE of all items in the transaction:

truncate table SHOW_TRANSACTION ; 

declare

   procedure DOTHEINSERT(DATAM in SHOW_TRANSACTION.MISC%type ,
                        doCommit IN boolean

   ) 
   as
          procedure DOTHETRANSACTION(doCommit IN boolean) as 
          begin
            if DOCOMMIT   then
               commit ;
            else -- (HERE I AM GETTING RID OF THE PROCEDURES ROLLBACK, SO EVERYTHING IS BEING ROLLBACK'ED
               ROLLBACK ;
            end if;             
          END DOTHETRANSACTION;
   begin    
            insert into SHOW_TRANSACTION (MISC) 
            values (DATAM);
            DOTHETRANSACTION(doCommit);
   end DOTHEINSERT;
begin
    DOTHEINSERT('commit1' , true);
    DOTHEINSERT('NOcommit1' , false);
    DOTHEINSERT('commit2' , true);
    DOTHEINSERT('NOcommit2' , false);
    DOTHEINSERT('commit3' , true);
    ROLLBACK;


end ;
/

select * from SHOW_TRANSACTION ;
/

MISC            
--------------- 
commit1         
COMMIT2         
commit3 

/* now the transaction is in a sub-sub procedure */