Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

DBMS_XA

The Oracle database normally manages its own transactions, but the XA/Open interface allows an external transaction manager to take control of transaction management. When would you need this? If you had an application that interacted with multiple databases, even from different vendors, it would be good to manage transactions from a single location.

Oracle has been XA compliant since Oracle 7, but Oracle 11g introduced the DBMS_XA package to allow calls to the XA interface from PL/SQL. This article shows a brief example of how it can be used.

Setup

We will run some tests against the following table.

CREATE TABLE tab1 (
  id  NUMBER
);

All the functions in the DBMS_XA package return a status indicating the success or reason for failure of an operation. The package contains constants representing the possible outcomes. To make life easier the following procedure checks the response and in the event of an unsuccessful output calls the XA_ROLLBACK function and raises an exception with a suitable description.

CREATE OR REPLACE PROCEDURE check_result (p_xid     IN DBMS_XA_XID,
                                          p_return  IN PLS_INTEGER,
                                          p_command IN VARCHAR2) AS
  l_output  VARCHAR2(23767);
  l_return  PLS_INTEGER;
BEGIN
  IF p_return != SYS.DBMS_XA.XA_OK THEN
    CASE p_return
      WHEN SYS.DBMS_XA.XA_RBBASE THEN l_output := 'XA_RBBASE: Inclusive lower bound of the rollback codes';
      WHEN SYS.DBMS_XA.XA_RBROLLBACK THEN l_output := 'XA_RBROLLBACK: Rollback was caused by an unspecified reason';
      WHEN SYS.DBMS_XA.XA_RBCOMMFAIL THEN l_output := 'XA_RBCOMMFAIL: Rollback was caused by a communication failure';
      WHEN SYS.DBMS_XA.XA_RBDEADLOCK THEN l_output := 'XA_RBDEADLOCK: Deadlock was detected';
      WHEN SYS.DBMS_XA.XA_RBINTEGRITY THEN l_output := 'XA_RBINTEGRITY: Condition that violates the integrity of the resources was detected';
      WHEN SYS.DBMS_XA.XA_RBOTHER THEN l_output := 'XA_RBOTHER: Resource manager rolled back the transaction for an unlisted reason';
      WHEN SYS.DBMS_XA.XA_RBPROTO THEN l_output := 'XA_RBPROTO: Protocol error occurred in the resource manager';
      WHEN SYS.DBMS_XA.XA_RBTIMEOUT THEN l_output := 'XA_RBTIMEOUT: Transaction branch took long';
      WHEN SYS.DBMS_XA.XA_RBTRANSIENT THEN l_output := 'XA_RBTRANSIENT: May retry the transaction branch';
      WHEN SYS.DBMS_XA.XA_RBEND THEN l_output := 'XA_RBEND: Inclusive upper bound of the rollback codes';
      WHEN SYS.DBMS_XA.XA_NOMIGRATE THEN l_output := 'XA_NOMIGRATE: Transaction branch may have been heuristically completed';
      WHEN SYS.DBMS_XA.XA_HEURHAZ THEN l_output := 'XA_HEURHAZ: Transaction branch may have been heuristically completed';
      WHEN SYS.DBMS_XA.XA_HEURCOM THEN l_output := 'XA_HEURCOM: Transaction branch has been heuristically committed';
      WHEN SYS.DBMS_XA.XA_HEURRB THEN l_output := 'XA_HEURRB: Transaction branch has been heuristically rolled back';
      WHEN SYS.DBMS_XA.XA_HEURMIX THEN l_output := 'XA_HEURMIX: Some of the transaction branches have been heuristically committed, others rolled back';
      WHEN SYS.DBMS_XA.XA_RETRY THEN l_output := 'XA_RETRY: Routine returned with no effect and may be re-issued';
      WHEN SYS.DBMS_XA.XA_RDONLY THEN l_output := 'XA_RDONLY: Transaction was read-only and has been committed';
      WHEN SYS.DBMS_XA.XA_OK THEN l_output := 'XA_OK: Normal execution';
      WHEN SYS.DBMS_XA.XAER_ASYNC THEN l_output := 'XAER_ASYNC: Asynchronous operation already outstanding';
      WHEN SYS.DBMS_XA.XAER_RMERR THEN l_output := 'XAER_RMERR: Resource manager error occurred in the transaction branch';
      WHEN SYS.DBMS_XA.XAER_NOTA THEN l_output := 'XAER_NOTA: XID is not valid';
      WHEN SYS.DBMS_XA.XAER_INVAL THEN l_output := 'XAER_INVAL: Invalid arguments were given';
      WHEN SYS.DBMS_XA.XAER_PROTO THEN l_output := 'XAER_PROTO: Routine invoked in an improper context';
      WHEN SYS.DBMS_XA.XAER_RMFAIL THEN l_output := 'XAER_RMFAIL: Resource manager unavailable';
      WHEN SYS.DBMS_XA.XAER_DUPID THEN l_output := 'XAER_DUPID: XID already exists';
      WHEN SYS.DBMS_XA.XAER_OUTSIDE THEN l_output := 'XAER_OUTSIDE: Resource manager doing work outside global transaction';
    END CASE;

    l_return := SYS.DBMS_XA.xa_rollback(p_xid);
    RAISE_APPLICATION_ERROR(-20000, p_command || '=' || l_output);
  END IF;
END;
/

By default the server aborts a transaction branch if no session has been associated with it for more than 60 seconds. If you need more time between running the sample scripts, alter this timeout using the XA_SETTIMEOUT function and a suitable number of seconds.

DECLARE
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_settimeout(500);
END;
/

Session 1

The XA_START function attaches the current session to a transaction branch. The TMNOFLAGS flag indicates a new transaction branch should be created, while the TMJOIN and TMRESUME flags indicate the session should be attached to an existing transaction branch. Once attached, the work you perform will be done as part of that transaction branch, and as such will be independent of any transactions associated with the current session. The XA_END function detaches the session from the transaction branch.

The following code attaches the session to a new transaction branch, inserts a row, then detaches from the transaction branch. We have to define a transaction ID, in this case "999", so we can identify the transaction in subsequent sessions.

-- Connect to a new session
CONN test/test

TRUNCATE TABLE tab1;

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_start(xid  => l_xid,
                                   flag => DBMS_XA.TMNOFLAGS);

  check_result(l_xid, l_return, 'xa_start');


  INSERT INTO tab1 (id) VALUES (1);


  l_return := SYS.DBMS_XA.xa_end(xid  => l_xid,
                                 flag => DBMS_XA.TMSUSPEND);

  check_result(l_xid, l_return, 'xa_end');
END;
/

PL/SQL procedure successfully completed.

SQL>

We can see the transaction in the V$GLOBAL_TRANSACTION view.

CONN / AS SYSDBA

SELECT state
FROM   v$global_transaction
WHERE  globalid = DBMS_XA_XID(999).gtrid;

STATE
--------------------------------------
ACTIVE

SQL>

Session 2

If we create a new session by connecting to the database we can see that the insert was not committed by closing the previous session.

-- Connect to a new session
CONN test/test

SELECT * FROM tab1; 

no rows selected

SQL>

Next, we insert a second row into the table in a similar manner. Notice this time the use of the TMRESUME flag in the call to the XA_START function. This is because we are attaching the session to the previous transaction branch, rather than creating a new one.

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_start(xid  => l_xid,
                                   flag => DBMS_XA.TMRESUME);

  check_result(l_xid, l_return, 'xa_start');

  INSERT INTO tab1 (id) VALUES (2);

  l_return := SYS.DBMS_XA.xa_end(xid  => l_xid,
                                 flag => DBMS_XA.TMSUCCESS);

  check_result(l_xid, l_return, 'xa_end');
END;
/

PL/SQL procedure successfully completed.

SQL>

Session 3

Once again we create a new session by connecting to the database. Notice again that no rows are present in the test table.

-- Connect to a new session
CONN test/test

SELECT * FROM tab1; 

no rows selected

SQL>

Now we call the XA_COMMIT function to commit the changes associated with the transaction branch.

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_commit(xid      => l_xid,
                                    onePhase => TRUE);

  check_result(l_xid, l_return, 'xa_commit');
END;
/

PL/SQL procedure successfully completed.

SQL>

The rows inserted in the previous two sessions are now visible in the test table.

SELECT * FROM tab1; 

        ID
----------
         1
         2

SQL>

Note. I can't see a difference between the TMJOIN and TMRESUME flags when used with the XA_START function. Likewise, the TMSUSPEND and TMSUCCESS flags used with the XA_END function seem to give the same result. In both cases the documentation suggests the flags perform different functions, but from my tests I can't see a difference in the effect they have.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.