Rollback after commit using flashback

Normally when someone asks if it is possible to rollback a single, complete transaction after a commit I have to say no. But this time I was confronted myself with this requirement. We are migrating an Oracle Forms client-server application to Oracle Apex and in one of the forms the transaction-handling was as follows:

  1. Execute some complex pl/sql that updates/inserts into several tables
  2. After completion the user can switch between several screens within the same session to look at several parameters.
  3. If necessary the user can rollback the complete transaction.

Well, in C/S the third step is just a simple ROLLBACK command, but in Apex life is not that simple.

In Apex a commit is issued after every pl/sql block – in our case a Dynamic Action- so step 3 is not that trivial any more. As we saw it we had 2 options:

  • Write some complex pl/sql that reverses the complex logic of the first step
  • Something else.

I went for “Something else”. The database version is an Oracle 10g EE, so I didn’t have all the new flashback features at hand, but it was enough to make something that works.

I created the following package like this:

create or replace package asl_transaction
as
  procedure capture_transaction
    ( p_session_id number
    );
  procedure reverse_transaction
    ( p_session_id number
    );
  procedure confirm_transaction
    ( p_session_id number
    );    
  procedure purge_old_transactions ;
end;
/
create or replace package body asl_transaction
as

type t_cache 
is
  table of rowid
  index by varchar2(2000)
;
g_cache t_cache;

procedure capture_transaction
  ( p_session_id number
  )
is
begin
  insert into apx_transaction
    ( session_id
    , xid
    , creation_date
    )
  select p_session_id
  ,      xid
  ,      sysdate
  from  v$transaction
  where addr = ( select taddr
                 from v$session
                 where sid = ( select sid
                               from v$mystat
                               where rownum = 1  ));
end;

procedure push_rowid
  ( p_rowid rowid
  )
is
begin
  g_cache(p_rowid) := p_rowid;
end;

function check_rowid
  ( p_rowid in rowid
  ) return rowid
is
begin
  if g_cache.exists(p_rowid)
  then
    return g_cache(p_rowid);
  else
    return p_rowid;
  end if;
end;

procedure reverse_transaction
  ( p_session_id number
  )
is
  l_new_rowid rowid;
begin
  savepoint inlineTransaction;
  for r_transactie in ( select xid 
                        from   apx_transaction 
                        where  session_id = p_session_id
                      )
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid         = r_transactie.xid
                   and    qry.table_name != 'ASL_APX_TRANSACTION'
                 )
    loop
      if upper(r_dml.undo_sql) like '%INSERT INTO%'
      then
        push_rowid(r_dml.row_id);
        l_new_rowid := null;
        execute immediate r_dml.undo_sql
        ||' returning rowid into :b1' returning into l_new_rowid;        
        g_cache(r_dml.row_id) := l_new_rowid;
      else
        l_new_rowid := check_rowid(r_dml.row_id);
        if ( l_new_rowid != r_dml.row_id)
        then
          r_dml.undo_sql := replace(r_dml.undo_sql,r_dml.row_id, l_new_rowid);
        end if;
        execute immediate r_dml.undo_sql;        
      end if;
    end loop;
  end loop;
  confirm_transaction(p_session_id);
end;

procedure confirm_transaction
  ( p_session_id number
  )
is
begin
  delete from apx_transaction where session_id = p_session_id;  
end;

procedure purge_old_transactions
is
begin
  delete from apx_transaction where creation_date < sysdate - 1;  
end;

end;
/

I did not include the DDL for the table APX_TRANSATION. I think you will be able to reverse engineer the statement.
Before you commit your transaction, so in Apex before the end of your PL/SQL block, you call

ASL_TRANSACTION.CAPTURE_TRANSACTION(:SESSION_ID).

The code that is executed by the former Rollback-button now has to be

ASL_TRANSACTION.REVERSE_TRANSACTION(:SESSION_ID).

In my opinion there also has to be some Confirm button that has to call

ASL_TRANSACTION.CONFIRM_TRANSACTION(:SESSION_ID).

That’s all. I included a full  test script at the end of this post.

For this package to work you’ll have to have give grant select any transaction to the package owner. If you don’t you can’t see the view sys.flashback_transaction_query.

The logic behind the package is this:

  • Before the commit, capture all the XID’s from v$transaction of all the DML that are executed in the current transaction
  • Save the XID’s with some identifying number, e.g. the session_id
  • By reversal, get the undo_sql from flashback_transaction_query and play back the transaction.
  • If original transaction deleted the row he first updated c.q. inserted, the reversal has to catch the new rowid of the insert to play back the latter playback c.q. delete.

Some words of caution:

Don’t, really don’t try to access flashback_transaction_query with anything else than a XID.

One of the features you get with an ordinary rollback is locking. The above solution does not involve a locking mechanism, yet. Ghost updates are therefore a possibility: when transaction A updates a table to X, session B updates to Y and A reverses, the update to Y is lost. In the client-server situation session B was blocked for update until A commits or rollbacks. It’s simple Oracle 1:1

However, when we look back at our options we also would have had the same problem with option 1.

Test, test and test if the given solution is appropriate in your case.

drop table emp_2
/

drop table dept_2
/

delete from apx_transaction
where session_id = -1
/

CREATE TABLE emp_2 
   (empno   NUMBER PRIMARY KEY, 
    empname VARCHAR2(16), 
    salary  NUMBER
    )
/
CREATE TABLE dept_2 
   (deptno   NUMBER, 
    deptname VARCHAR2(32)
   )
/

Prompt Insert in EMP_2 111,mike,555
INSERT INTO emp_2 VALUES (111, 'Mike', 555);
COMMIT;

prompt insert in dept_2 10,accounting
INSERT INTO dept_2 VALUES (10, 'Accounting');
COMMIT;

Prompt
Prompt Start an erroneous transaction
Prompt

UPDATE emp_2 SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept_2 VALUES (20, 'Finance'); 
DELETE FROM emp_2 WHERE empno = 111;

Prompt
Prompt Capture the transactiondata and commit
Prompt
execute asl_transaction.capture_transaction(-1);
COMMIT;

Prompt New data in the tables
Prompt EMP_2
select * from emp_2
/
Prompt DEPT_2
select * from dept_2
/
prompt
Prompt The undo-statements
begin
  for r_transactie in ( select xid from apx_transaction)
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid  = r_transactie.xid
                   and    qry.table_name != 'ASL_GTT_TRANSACTION'
                 )
    loop               
      dbms_output.put_line( r_transactie.xid||'...'||r_dml.undo_sql);
    end loop;
  end loop;
end;  
/
Prompt
Prompt Reverse the transaction
exec asl_transaction.reverse_transaction(-1)
Prompt
Prompt Check 
Prompt Has to be: 111,mike,555
select * from emp_2
/

prompt Has to be:  10,accounting
select * from dept_2
/
Advertisements

One thought on “Rollback after commit using flashback

  1. The coding ‘bypass’ reminds me of the mutating table error ‘bypass’: store some ID, and be able to deal with it later, at commit time. Same here. But much more exotic, nice post 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s