DBMS_XA and Four Eyes Principle

I am simply amazed by the new DBMS_XA package available in Oracle 11g. It offers the possibility to define global transactions and to manage them from within different sessions using a 2PC (Two Phase Commit) approach. One of the great benefit I see now is for those systems which require the implementation of a "four eyes principle", that is somebody puts a transaction into the system but somebody else checks it and finally approves it. Without this approval that transaction is not visible which, from the business point of view, it's not committed.

Let's walk through a very simple and basic example.
First of all we'll need an Oracle user:

grant connect, resource, force any transaction to fep identified by xxx;

Pay attention to the special right "foce any transaction". It is needed in order to be able to manage transactions created by other users.
Now, lets create a simple table which is supposed to store invoices.

SQL> connect fep/xxx@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as fep

SQL>
SQL> create table invoices (
  2   inv_number integer,
  3   inv_text   varchar2(100),
  4   inv_date   date,
  5   inv_value  number(20, 6)
  6  );

Table created

Now, assume that somebody books in a new invoice. This means that a new record has to be added to the "INVOICES" table but, being subject to "four eyes principle" it must not be committed till somebody else will check it. In the DBMS_XA package light this should be done like this:

SQL> DECLARE
  2    l_rc  PLS_INTEGER;
  3    l_oer PLS_INTEGER;
  4    e_xae EXCEPTION;
  5  BEGIN
  6    l_rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMNOFLAGS);
  7 
  8    IF l_rc != DBMS_XA.XA_OK THEN
  9      RAISE e_xae;
 10    END IF;
 11 
 12    insert into invoices values (1, 'buy a TV', sysdate, 1000);
 13 
 14    l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
 15 
 16    IF l_rc != DBMS_XA.XA_OK THEN
 17      RAISE e_xae;
 18    END IF;
 19 
 20  EXCEPTION
 21    WHEN OTHERS THEN
 22      l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 23      l_rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
 24   
 25      IF l_rc != DBMS_XA.XA_OK THEN
 26        l_oer := DBMS_XA.XA_GETLASTOER();
 27        raise_application_error(-20001,
 28                                'ORA-' || l_oer ||
 29                                ' error in rolling back a failed transaction');
 30      END IF;
 31   
 32      raise_application_error(-20002,
 33                              'error in transaction processing, transaction rolled back');
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options

Lets have a look at the above piece of code. At the 6th line a new global transaction is started. The 123 is the global transaction identifier and should be provided by the application and it is required to be unique. If the transaction has been successfully started then returning value must be DBMS_XA.XA_OK,otherwise an error has occurred. Then, the insert into our "INVOICES" table is made and the global transaction is simply suspended. Pay attention that no COMMIT is issued. Of course, it is important to note how the status of each DBMS_XA operation is checked and how the exception handler is written. Finally, the session is ended by effectively disconnecting.

Now, suppose that the second person must take over the previous transaction, to check it and to approve it. This can be done like shown below:

SQL> connect fep/xxx@testdb
Connected.
SQL> select * from invoices;

no rows selected

SQL> commit;

Commit complete.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    l_rc  PLS_INTEGER;
  3    l_oer PLS_INTEGER;
  4    e_xae EXCEPTION;
  5    l_inv_no  invoices.inv_number%type;
  6    l_inv_dat invoices.inv_date%type;
  7    l_inv_txt invoices.inv_text%type;
  8    l_inv_val invoices.inv_value%type;
  9  BEGIN
 10    -- resume global transaction
 11    l_rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMRESUME);
 12    IF l_rc != DBMS_XA.XA_OK THEN
 13      RAISE e_xae;
 14    END IF;
 15 
 16    select *
 17      into l_inv_no, l_inv_txt, l_inv_dat, l_inv_val
 18      from invoices i
 19     where i.inv_number = 1;
 20 
 21    dbms_output.put_line('invoice number = ' || l_inv_no);
 22    dbms_output.put_line('invoice text = ' || l_inv_txt);
 23    dbms_output.put_line('invoice date = ' || l_inv_dat);
 24    dbms_output.put_line('invoice value = ' || l_inv_val);
 25 
 26    -- end global transaction
 27    l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 28    IF l_rc != DBMS_XA.XA_OK THEN
 29      RAISE e_xae;
 30    END IF;
 31 
 32    -- commit global transaction
 33    l_rc := DBMS_XA.XA_COMMIT(DBMS_XA_XID(123), TRUE);
 34    IF l_rc != DBMS_XA.XA_OK THEN
 35      RAISE e_xae;
 36    END IF;
 37 
 38  EXCEPTION
 39    WHEN OTHERS THEN
 40      l_rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
 41      l_rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
 42   
 43      IF l_rc != DBMS_XA.XA_OK THEN
 44        l_oer := DBMS_XA.XA_GETLASTOER();
 45        raise_application_error(-20001,
 46                                'ORA-' || l_oer ||
 47                                ' error in rolling back a failed transaction');
 48      END IF;
 49   
 50      raise_application_error(-20002,
 51                              'error in transaction processing, transaction rolled back');
 52  END;
 53  /
invoice number = 1
invoice text = buy a TV
invoice date = 04-NOV-07
invoice value = 1000

PL/SQL procedure successfully completed.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options

Pay attention to the COMMIT statement just above the anonymous block. That's needed in order the anonymous block to start within a new fresh transaction, otherwise an error may occur.

So, using DBMS_XA package we were able to resume an uncommitted transaction made from another session, to see the data and to commit the changes. Of course, we can connect once again to see if the data is really committed.

SQL> connect fep/xxx@testdb
Connected.
SQL> set linesize 120
SQL> column inv_text format a10
SQL> select * from invoices;

INV_NUMBER INV_TEXT   INV_DATE   INV_VALUE
---------- ---------- --------- ----------
         1 buy a TV   04-NOV-07       1000

Lovely! The data is there!

However, there are some additional areas which I still have to investigate in order to validate this model for a MAYBE "four eyes principle" system:
1) where I can see all suspended XA transactions? it seems that DBA_PENDING_TRANSACTIONS, V$GLOBAL_TRANSACTION, DBA_2PC_PENDING or DBA_2PC_NEIGHBORS doesn't show any suspended transaction.
2) are the suspended global transactions persistent across database restarts? Apparently, as far as I could test, it's really not the case.
3) what about the global transaction timeout for a session? If a global transaction should never expire than we have a problem as I don't see any special value for the timeout parameter of the DBMS_XA.XA_SETTIMOUT function (e.g. zero means forever). So the maximum timeout we can set is the upper bound of the PLS_INTEGER type which is 2147483647. That means approximately 24855 days. However, without persistence across database restarts it's, anyway, useless.
4) no idea how this component behaves in a RAC configuration. For example starting a transaction on one instance and resuming it on another one.