dbms_xa Tips

IT environments are becoming more and more complex. With multiple databases and applications communicating together, it can be difficult to manage without the proper resources.

One good example is when there are transactions traveling between different applications which must be committed to different databases like BPEL, Oracle E-Business Suite, Oracle Transportation Manager and/or Oracle Retail, Oracle XA  provides an external interface used to interact with a transaction manager out of the database. It is used for committing transactions of different databases while maintaining data integrity. These database transactions are also known as distributed transaction processing (DTP).

The dbms_xa  package provides an interface for working with XA via PL/SQL language. Certain privileges are necessary for a user to execute particular XA tasks:

1.  To execute xa_recover, the user must have select privileges in the dba_pending_transactions view.

2.  To manipulate XA transactions created by other users, the force any transaction privilege is required.

In the next example, how to create a distributed transaction from one session and commit to another session by using the dbms_xa package will be shown.

conn sys@ora11g as sysdba

Connected to:

Oracle 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

--First create a test table
create table

   tab_dbms_xa (col1 number);

insert into

   tab_dbms_xa values (1);

commit;

select

   *

from

   tab_dbms_xa;

Now an XA transaction is created in the first session. This session will do an update and add two insertions on the test table, but will not commit. Note that the session timeout is increased, so there is more time to work with this sample.

--Setting timeout in session using xa_settimeout function. The value specified is in number of seconds.
declare
  v_sess_timeout  pls_integer;
begin
  v_sess_timeout := dbms_xa.xa_settimeout(seconds => 10000);
end;
/

set serveroutput on
declare
  v_my_transaction  pls_integer;
  v_xa_xid     dbms_xa_xid := dbms_xa_xid(3322);
  v_xa_exception exception;
  v_ora_error pls_integer;
begin

Notice that the xa_start function is used to associate the current session with a transaction and that the tmnoflags constant is set to inform that it is a new transaction.

  v_my_transaction  := dbms_xa.xa_start(

                          xid => v_xa_xid,

                          flag =>  dbms_xa.tmnoflags);

--Check if XA transaction is OK
  if v_my_transaction <> dbms_xa.xa_ok then
    v_ora_error := dbms_xa.xa_getlastoer();
    dbms_output.put_line(a => 'Attention! Oracle Error - ORA-' || v_ora_error || ' obtained. XA Process failed!');
    raise v_xa_exception;
  else dbms_output.put_line(a => 'XA Process ID 3322 started');
  end if;

--DML Operations
  update
     tab_dbms_xa
  set
     col1=11 ,
     col2='Value updated on Session 1.'
  where
     col1=1;
    
  insert into
     tab_dbms_xa
  values (2,'Value inserted on Session 1.');
 
  insert into
     tab_dbms_xa
  values (3,'Value inserted on Session 1.');

Suspending a transaction is done using xa_end and tmsuspend. This enables the transaction to be caught later by another session.

v_my_transaction  := dbms_xa.xa_end(

                        xid => v_xa_xid,

                        flag =>  dbms_xa.tmsuspend);

--Check if XA transaction is OK
  if v_my_transaction <> dbms_xa.xa_ok then
    v_ora_error := dbms_xa.xa_getlastoer();
   dbms_output.put_line(a => 'Attention! Oracle Error - ORA-' || v_ora_error || ' obtained. XA Process failed!');
    raise v_xa_exception;
   else dbms_output.put_line(a => 'XA Process ID 3322 is working!');
  end if;

  exception
    when others then
      dbms_output.put_line(a => 'A XA problem occur. Please check the error ('||v_my_transaction||') and try again. This transaction will be rolled back now!');
      v_my_transaction := dbms_xa.xa_end(xid => v_xa_xid,flag =>  dbms_xa.tmsuccess);
      v_my_transaction := dbms_xa.xa_rollback(xid => v_xa_xid);

      if v_my_transaction <> dbms_xa.xa_ok then
        v_ora_error := dbms_xa.xa_getlastoer();
        dbms_output.put_line(a => 'A problem occur while trying to rollback the transaction. Please try to fix error '||v_my_transaction||', ORA-' || v_ora_error || '.');
        raise_application_error(-20001, 'ORA-'||v_ora_error|| ' error when trying to rollback a transaction');
      end if;
      raise_application_error(-20002, 'ORA-'||v_ora_error||' Transaction was rolled back successfully!');
end;
/

Now there is a transaction identified by XID=3322. Another session will now be opened to make changes to this transaction before it is committed. To check that the transaction is created and active, use the query below:

select
   state,
   flags,
   coupling
from  
   gv$global_transaction
where 
   globalid = dbms_xa_xid(3322).gtrid;

STATE                                       FLAGS COUPLING

-------------------------------------- ---------- ---------------

active                                          0 tightly coupled

Here, the xa_start function is used with the tmresume constant to join an existing transaction; in this case, the transaction with XID=3322.

set serveroutput on

declare

  v_my_transaction  pls_integer;

  v_xa_xid     dbms_xa_xid := dbms_xa_xid(3322);

  v_xa_exception exception;

  v_ora_error pls_integer;

begin

  v_my_transaction  := dbms_xa.xa_start(

                          xid => v_xa_xid,

                          flag =>  dbms_xa.tmresume);

  --Check if XA transaction is OK

  if v_my_transaction <> dbms_xa.xa_ok then

     v_ora_error := dbms_xa.xa_getlastoer();

     dbms_output.put_line(a => 'Attention! Oracle Error - ORA-' ||

                          v_ora_error || ' obtained. XA Process failed!');
     raise v_xa_exception;

  else
     dbms_output.put_line(

        a => 'XA Process ID 3322 started - ####### Step 1 ##########');

  end if;

  --DML operations on test table

  dbms_output.put_line(

     a => '########## Inserting a new value in test table ##########');
  insert into tab_dbms_xa values (4,'Value inserted on Session 2.');

In order to detach from a session, the tmsuccess constant is used with the xa_end function, shown here:

  v_my_transaction  := dbms_xa.xa_end(
                       xid => v_xa_xid,
                       flag =>  dbms_xa.tmsuccess);
  --Check if XA transaction is OK
  if v_my_transaction <> dbms_xa.xa_ok then
     v_ora_error := dbms_xa.xa_getlastoer();
     dbms_output.put_line(a => 'Attention! Oracle Error - ORA-' ||
                          v_ora_error || ' obtained. XA Process failed!');
     raise v_xa_exception;
  else
     dbms_output.put_line(a => 'XA Process ID 3322 started - ####### Step 2 ##########');
  end if;

  exception
    when others then
      dbms_output.put_line(a => 'A XA problem occur. Please check the error ('||
                           v_my_transaction||') and try again. This transaction will be rolled back now!');
      v_my_transaction := dbms_xa.xa_end(
                             xid => v_xa_xid,
                             flag =>  dbms_xa.tmsuccess);
      v_my_transaction := dbms_xa.xa_rollback(
                             xid => v_xa_xid);

      if v_my_transaction != dbms_xa.xa_ok then
        v_ora_error := dbms_xa.xa_getlastoer();
        dbms_output.put_line(a => 'A problem occur while trying to rollback the transaction. Please try to fix the error '||
                             v_my_transaction||', ORA-' || v_ora_error || '.');
        raise_application_error(-20001, 'ORA-'||v_ora_error|| ' error when trying to rollback a transaction');
      end if;

      raise_application_error(-20002, 'ORA-'||v_ora_error||
       ' Transaction was rolled back successfully!');
end;
/

At this point, if the tab_dbms_xa table is checked, it will not show the lines created/updated yet. This is because no commit has been made. In the third session shown next, it will be made.

declare
 v_my_transaction  pls_integer;
  v_xa_xid     dbms_xa_xid := dbms_xa_xid(3322);
  v_xa_exception exception;
  v_ora_error pls_integer;
  
begin

Here, the xa_commit function is used and all changes are finally stored in the tab_dbms_xa table.

   dbms_output.put_line('Using xa_commit function to commit changes!');
   v_my_transaction  := dbms_xa.xa_commit(
                           xid => v_xa_xid,
                           onephase =>  true);

  --Check if XA transaction is OK
  if v_my_transaction <> dbms_xa.xa_ok then
    v_ora_error := dbms_xa.xa_getlastoer();
    dbms_output.put_line(a => 'Attention! Oracle Error - ORA-' ||
                         v_ora_error || ' obtained. XA commit Process failed!');
    raise v_xa_exception;
  else dbms_output.put_line(a => 'XA Process ID is working and was commited successfully!');
  end if;

   exception
    when others then
      dbms_output.put_line(a => 'A XA problem occur. Please check the error ('||
                           v_my_transaction||') and try again. This transaction will be rolled back now!');
      v_my_transaction := dbms_xa.xa_end(xid => v_xa_xid,flag =>  dbms_xa.tmsuccess);
      v_my_transaction := dbms_xa.xa_rollback(xid => v_xa_xid);

      if v_my_transaction <> dbms_xa.xa_ok then
        v_ora_error := dbms_xa.xa_getlastoer();
        dbms_output.put_line(a => 'A problem occur while trying to rollback the transaction. Please try to fix the error '||
                             v_my_transaction||', ORA-' || v_ora_error || '.');
        raise_application_error(-20001, 'ORA-'||v_ora_error|| ' error when trying to rollback a transaction');
      end if;

      raise_application_error(-20002, 'ORA-'||v_ora_error||
       ' Transaction was rolled back successfully!');
end;
/

Lastly, check the tab_dbms_xa table to get the results created in the first sessions and committed in the third session.

 select

    *

 from

    tab_dbms_xa;

      COL1 COL2

---------- --------------------------------------------------

        11 Value updated on Session 1.

         2 Value inserted on Session 1.

         3 Value inserted on Session 1.

         4 Value inserted on Session 2.

If an error occurs when using distributed transactions like the loss of the network connection, the transaction may become lost. They may be found in views like dba_2pc_pending, dba_2pc_neighbors, dba_pending_transactions or v$global_transactions. When these lost transactions need to  be purged, use a script like the one below:

select

   'commit force '''||local_tran_id||''';'

from

   dba_2pc_pending;

select

   'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''');'

from

   dba_2pc_pending

where

   state='forced commit';

exec dbms_transaction.purge_lost_db_entry('123.544.7');'

--Always commit after command above

commit;

This will find all pending transactions, commit and then purge them if they are still hanging. Make sure to commit after purging the transaction.