This blog has moved here.

Sunday, November 04, 2007

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.

2 comments:

iudith said...

Hello Alexandru,

With a big delay, but today just Google-ing after DBMS_XA I found your post, extremely nice and clear for somebody new to this issue until today ...

And I'm happy it comes from somebody from Romania, which is my birth country as well !

Thanks & Best Regards,
Iudith Mentzel
Oracle developer & ADBA
ZIM Integrated Shipping Services Ltd.
Haifa, Israel

Alexandru Tica said...

Hi Iudith,

Glad I could help!
It's a small world, isn't it?

Greetings from Romania! :)