Oracle DBMS_XA
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Allow applications to call the XA interface in PL/SQL. Using this package, application developers can switch or share transactions across SQL*Plus sessions or processes using PL/SQL
One possible X/Open DTP model
AUTHID CURRENT_USER
Constants (Flag Fields for XA_START and XA_END)
Name Data Type Value
TMJOIN PLS_INTEGER -- Caller is joining existing transaction branch
UTL_RAW.CAST_TO_BINARY_INTEGER ('00200000')
TMNOFLAGS PLS_INTEGER -- No value selected
00000000
TMRESUME PLS_INTEGER -- Caller is resuming association with suspended transaction branch
UTL_RAW.CAST_TO_BINARY_INTEGER('08000000')
TMSUCCESS PLS_INTEGER -- Dissociate caller from transaction branch
font face="Courier">UTL_RAW.CAST_TO_BINARY_INTEGER ('04000000')
TMSUSPEND PLS_INTEGER -- Caller is suspending, not ending, association UTL_RAW.CAST_TO_BINARY_INTEGER('02000000')
Constants (Return Values)
Name Data Type Value Description
XA_HEURHAZ PLS_INTEGER 8 Transaction branch may have been heuristically completed
XA_HEURCOM PLS_INTEGER 7 Transaction branch has been heuristically committed
XA_HEURMIX PLS_INTEGER 5 Some transaction branches have been heuristically committed, others rolled back
XA_HEURRB PLS_INTEGER 6 Transaction branch has been heuristically rolled back
XAER_ASYNC PLS_INTEGER -2 Asynchronous operation already outstanding
XAER_DUPID PLS_INTEGER -8 XID already exists
XAER_INVAL PLS_INTEGER -5 Invalid arguments were given
XAER_NOTA PLS_INTEGER -4 XID is not valid
XAER_OUTSIDE PLS_INTEGER -9 Resource manager doing work outside global transaction
XAER_PROTO PLS_INTEGER -6 Routine invoked in an improper context
XAER_RMERR PLS_INTEGER -3 Resource manager error occurred in the transaction branch
XAER_RMFAIL PLS_INTEGER -7 Resource manager unavailable
XA_NOMIGRATE PLS_INTEGER 9 Transaction branch may have been heuristically completed
XA_OK PLS_INTEGER 0 Normal execution
XA_RBBASE PLS_INTEGER 100 Inclusive lower bound of the rollback codes
XA_RBCOMMFAIL PLS_INTEGER XA_RBBASE+1 Rollback was caused by a communication failure
XA_RBDEADLOCK PLS_INTEGER XA_RBBASE+2 Deadlock detected
XA_RBEND PLS_INTEGER XA_RBTRANSIENT Inclusive upper bound of the rollback codes
XA_RBINTEGRITY PLS_INTEGER XA_RBBASE+3 Resource integrity violation detected
XA_RBOTHER PLS_INTEGER XA_RBBASE+4 Unlisted resource manager transaction roll back
XA_RBPROTO PLS_INTEGER XA_RBBASE+5 Protocol error occurred in the resource manager
XA_RBROLLBACK PLS_INTEGER XA_RBBASE Rollback was caused by an unspecified reason
XA_RBTIMEOUT PLS_INTEGER XA_RBBASE+6 Transaction branch took too long
XA_RBTRANSIENT PLS_INTEGER XA_RBBASE+7 May retry the transaction branch
XA_RDONLY PLS_INTEGER 3 Transaction was read-only and has been committed
XA_RETRY PLS_INTEGER 4 Routine returned with no effect and may be re-issued
Data Types TYPE DBMS_XA_XID IS OBJECT(
 formatid NUMBER,
 gtrid    RAW(64),
 bqual    RAW(64),
constructor function DBMS_XA_XID(gtrid IN NUMBER)
RETURN SELF AS RESULT,
constructor function DBMS_XA_XID (gtrid IN RAW,
bqual IN RAW)
RETURN SELF AS RESULT,
constructor function DBMS_XA_XID(formatid IN NUMBER,
gtrid IN RAW,
bqual IN RAW DEFAULT HEXTORAW('00000000000000000000000000000001'))
RETURN SELF AS RESULT);

TYPE DBMS_XA_XID_ARRAY AS TABLE OF DBMS_XA_XID;
Dependencies
DBA_PENDING_TRANSACTIONS DBMS_XA_LIB GV$GLOBAL_TRANSACTION
DBA_2PC_NEIGHBORS DBMS_XA_XID PLITBLM
DBA_2PC_PENDING DBMS_XA_XID_ARRAY UTL_RAW
DBMS_SYSTEM    
Definitions
Key Word Description
Application Program (AP) An application program defines transaction boundaries and specifies actions that constitute a transaction. For example, an AP can be a precompiler or OCI program. The AP operates on the RM's resource through its native interface, for example, SQL.
Branch A branch is a unit of work contained within one RM. Multiple branches make up one global transaction. In the case of Oracle Database, each branch maps to a local transaction inside the database server.
Distributed Transaction A distributed transaction, also called a global transaction, is a client transaction that involves updates to multiple distributed resources and requires "all-or-none" semantics across distributed RMs.
Dynamic and Static Registration Oracle Database supports both dynamic and static registration. In dynamic registration, the RM executes an application callback before starting any work. In static registration, you must call xa_start for each RM before starting any work, even if some RMs are not involved.
Resource Manager (RM) A resource manager controls a shared, recoverable resource that can be returned to a consistent state after a failure. Examples are relational databases, transactional queues, and transactional file systems. Oracle Database is an RM and uses its online redo log and undo segments to return to a consistent state after a failure.
Tight and Loose Coupling Application threads are tightly coupled if the RM considers them as a single entity for all isolation semantic purposes. Tightly coupled branches must see changes in each other. Furthermore, an external client must either see all changes of a tightly coupled set or none of the changes. If application threads are not tightly coupled, then they are loosely coupled.
Transaction Manager (TM) A transaction manager provides an API for specifying the boundaries of the transaction and manages commit and recovery. The TM implements a two-phase commit engine to provide "all-or-none" semantics across distributed RMs.

An external TM is a middle-tier component that resides outside Oracle Database. Normally, the database is its own internal TM. Using a standards-based TM enables Oracle Database to cooperate with other heterogeneous RMs in a single transaction.
Transaction Processing Monitor (TPM) A TM is usually provided by a transaction processing monitor (TPM) vendor. A TPM coordinates the flow of transaction requests between the client processes that issue requests and the back-end servers that process them. Basically, a TPM coordinates transactions that require the services of several different types of back-end processes, such as application servers and RMs distributed over a network.

The TPM synchronizes any commits or rollbacks required to complete a distributed transaction. The TM portion of the TPM is responsible for controlling when distributed commits and rollbacks take place. Thus, if a distributed application program takes advantage of a TPM, then the TM portion of the TPM is responsible for controlling the two-phase commit protocol. The RMs enable the TMs to perform this task.

Because the TM controls distributed commits or rollbacks, it must communicate directly with Oracle Database (or any other RM) through the XA interface. It uses Oracle XA library subroutines, which are described in "Oracle XA Library Subroutines", to tell Oracle Database how to process the transaction, based on its knowledge of all RMs in the transaction.
Two-Phase Commit Protocol The Oracle XA library interface follows the two-phase commit protocol. The sequence of events is as follows:
  1. In the prepare phase, the TM asks each RM to guarantee that it can commit any part of the transaction. If this is possible, then the RM records its prepared state and replies affirmatively to the TM. If it is not possible, then the RM might roll back any work, reply negatively to the TM, and forget about the transaction. The protocol allows the application, or any RM, to roll back the transaction unilaterally until the prepare phase completes.
  2. In phase two, the TM records the commit decision and issues a commit or rollback to all RMs participating in the transaction. TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.
TX Interface An application program starts and completes all transaction control operations through the TM through an interface called TX. The AP does not directly use the XA interface. APs are not aware of branches that fork in the middle-tier: application threads do not explicitly join, leave, suspend, and resume branch work, instead the TM portion of the transaction processing monitor manages the branches of a global transaction for APs. Ultimately, APs call the TM to commit all-or-none.

Note: The naming conventions for the TX interface and associated subroutines are vendor-specific. For example, the tx_open call might be referred to as tp_open on your system. In some cases, the calls might be implicit, for example, at the entry to a transactional RPC. See the documentation supplied with the transaction processing monitor for details.
Documented Yes
Files of interest {ORACLE_HOME}/rdbms/admin/xaview.sql
First Available 11.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxa.sql
Subprograms
DIST_TXN_SYNC XA_GETLASTOER XA_SETTIMEOUT
XA_COMMIT XA_PREPARE XA_START
XA_END XA_RECOVER Related Queries
XA_FORGET XA_ROLLBACK Demo
 
DIST_TXN_SYNC
Used in recovery of synchronization with RAC dbms_xa.dist_txn_sync;
exec dbms_xa.dist_txn_sync;
 
XA_COMMIT
Commits the global transaction specified by xid dbms_xa.xa_commit(
xid      IN DBMS_XA_XID,
onePhase IN BOOLEAN)
RETURN PLS_INTEGER;
See demo below
 
XA_END
Disassociates the current session from the transaction branch specified by xid dbms_xa.xa_end(
xid  IN DBMS_XA_XID,
flag IN PLS_INTEGER)
RETURN PLS_INTEGER;
See demo below
 
XA_FORGET
Informs the resource manager to forget about a heuristically committed or rolled back transaction branch dbms_xa.xa_forget(xid IN DBMS_XA_XID) RETURN PLS_INTEGER;
TBD
 
XA_GETLASTOER
Obtains the last Oracle error code, in case of failure of previous XA calls dbms_xa.xa_getLastOER RETURN PLS_INTEGER;
See demo below
 
XA_PREPARE
Prepares the transaction branch specified in xid for committing the transaction subsequently if possible dbms_xa.xa_prepare(xid IN DBMS_XA_XID) RETURN PLS_INTEGER;
TBD
 
XA_RECOVER
Obtains a list of prepared or heuristically completed transaction branches from a resource manager dbms_xa.xa_recover RETURN DBMS_XA_XID_ARRAY;
TBD
 
XA_ROLLBACK
Informs the resource manager to roll back work done on behalf of a transaction branch dbms_xa.xa_rollback(xid IN DBMS_XA_XID)
RETURN PLS_INTEGER;
See demo below
 
XA_SETTIMEOUT
Sets the transaction timeout in seconds for the current session dbms_xa.xa_settimeout(seconds IN PLS_INTEGER)
RETURN PLS_INTEGER;
TBD
 
XA_START
Associates the current session with the transaction branch specified by xid
dbms_xa.xa_start(
xid  IN DBMS_XA_XID,
flag IN PLS_INTEGER)
RETURN PLS_INTEGER;
set serveroutput on

DECLARE
 i  SIMPLE_INTEGER := ABS(dbms_random.normal) * 10000;
 rc PLS_INTEGER;
BEGIN
  dbms_output.put_line(i);

  rc := dbms_xa.xa_start(dbms_xa_xid(i),  dbms_xa.tmnoflags);
  dbms_output.put_line(rc);
END;
/
 
Related Queries
Note: Pending, suspended, transactions can be found by SELECT xidusn, xidslot, xidsqn, start_scn, dependent_scn, xid, prv_xid, ptx_xid
FROM gv$transaction;

SELECT * FROM gv$global_transaction;
 
Demo
DBMS_XA Demo: Session 1
-- Session 1 starts a transaction and does some work.
conn hr/hr@pdbdev

set serveroutput on

DECLARE
 rc  PLS_INTEGER;
 oer PLS_INTEGER;
 xae EXCEPTION;
BEGIN
  rc := dbms_xa.xa_start(dbms_xa_xid(123), dbms_xa.tmnoflags);

  IF rc != dbms_xa.xa_ok THEN
    oer := dbms_xa.xa_getlastoer();
    dbms_output.put_line('ORA-' || oer || ' occurred, XA_START failed');
    RAISE xae;
  ELSE
    dbms_output.put_line('XA_START(new xid=123) OK');
  END IF;

  UPDATE employees
  SET salary = salary * 1.1
  WHERE employee_id = 100;

  rc := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuspend);

  IF rc != dbms_xa.xa_ok THEN
    oer := dbms_xa.xa_getlastoer();
    dbms_output.put_line('ORA-' || oer || ' occurred, XA_END failed');
    RAISE xae;
  ELSE
    dbms_output.put_line('XA_END(suspend xid=123) OK');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('XA error('||rc||') occurred, rolling back the transaction ...');
    rc := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);
    rc := dbms_xa.xa_rollback(dbms_xa_xid(123));

    IF rc != dbms_xa.xa_ok THEN
      oer := dbms_xa.xa_getlastoer();
      dbms_output.put_line('XA-' || rc || ', ORA-' || oer ||
' XA_ROLLBACK does not return XA_OK');
      raise_application_error(-20001, 'ORA-' || oer ||
' error in rolling back a failed transaction');
    END IF;
    raise_application_error(-20002, 'ORA-' || oer ||
' error in transaction processing, transaction rolled back');
END;
/
DBMS_XA Demo: Session 2
-- Session 2 resumes the transaction and does some work

conn hr/hr@pdbdev

set serveroutput on

DECLARE
 rc  PLS_INTEGER;
 oer PLS_INTEGER;
 s   NUMBER;
 xae EXCEPTION;
BEGIN
  rc := dbms_xa.xa_start(dbms_xa_xid(123), dbms_xa.tmresume);

  IF rc != dbms_xa.xa_ok THEN
    oer := dbms_xa.xa_getlastoer();
    dbms_output.put_line('ORA-' || oer || ' occurred, xa_start failed');
    RAISE xae;
  ELSE
    dbms_output.put_line('XA_START(resume xid=123) OK');
  END IF;

  SELECT salary
  INTO s
  FROM employees
  WHERE employee_id = 100;

  dbms_output.put_line('employee_id = 100, salary = ' || s);

  rc := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);

  IF rc != dbms_xa.xa_ok THEN
    oer := dbms_xa.xa_getlastoer();
    dbms_output.put_line('ORA-' || oer || ' occurred, XA_END failed');
    RAISE xae;
  ELSE
    dbms_output.put_line('XA_END(detach xid=123) OK');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('XA error('||rc||') occurred, rolling back the transaction ...');

    rc := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);
    rc := dbms_xa.xa_rollback(dbms_xa_xid(123));

    IF rc != dbms_xa.xa_ok THEN
      oer := dbms_xa.xa_getlastoer();
      DBMS_OUTPUT.PUT_LINE('XA-' || rc || ', ORA-' || oer ||
' XA_ROLLBACK does not return XA_OK');
      raise_application_error(-20001, 'ORA-' || oer ||
' error in rolling back a failed transaction');
    END IF;

    raise_application_error(-20002, 'ORA-' || oer ||
' error in transaction processing, transaction rolled back');
END;
/
DBMS_XA Demo: Session 3
-- Session 3 commits the transaction

conn hr/hr@pdbdev

set serveroutput on

DECLARE
 rc  PLS_INTEGER;
 oer PLS_INTEGER;
 xae EXCEPTION;
BEGIN
  rc := dbms_xa.xa_commit(dbms_xa_xid(123), TRUE);

  IF rc != dbms_xa.xa_ok THEN
    oer := dbms_xa.xa_getlastoer();
    dbms_output.put_line('ORA-' || oer || ' occurred, XA_COMMIT failed');
    RAISE xae;
  ELSE
    dbms_output.put_line('XA_COMMIT(commit xid = 123) OK');
  END IF;
EXCEPTION
  WHEN xae THEN
    dbms_output.put_line('XA error('||rc||') occurred, rolling back the transaction ...');

    rc := dbms_xa.xa_rollback(dbms_xa_xid(123));

    IF rc != dbms_xa.xa_ok THEN
      oer := dbms_xa.xa_getlastoer();
      dbms_output.put_line('XA-' || rc ||', ORA-' || oer || ' XA_ROLLBACK does not return XA_OK');
      raise_application_error(-20001, 'ORA-' || oer || ' error in rolling back a failed transaction');
END IF;
      raise_application_error(-20002, 'ORA-' || oer || ' error in transaction processing, transaction rolled back');
END;
/

Related Topics
Built-in Functions
Built-in Packages
JAVA_XA
RAC
Transactions
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx