PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle DBMS_XA
Version 11.1
 
General
Note: Contains the XA/Open interface for applications to call XA interface in PL/SQL. Using this package, application developers can switch or share transactions across SQL*Plus sessions or processes using PL/SQL
Source {ORACLE_HOME}/rdbms/admin/dbmsxa.sql
First Available 11.1

One possible X/Open DTP model
Definitions
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. 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.
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;

Constants (Flag Fields for XA_START and XA_END)
Name Data Type  Value Description
TMJOIN PLS_INTEGER UTL_RAW.CAST_TO_BINARY_INTEGER ('00200000') Caller is joining existing transaction branch
TMNOFLAGS PLS_INTEGER 00000000 No value selected
TMRESUME PLS_INTEGER UTL_RAW.CAST_TO_BINARY_INTEGER ('08000000') Caller is resuming association with suspended transaction branch
TMSUCCESS PLS_INTEGER UTL_RAW.CAST_TO_BINARY_INTEGER ('04000000') Dissociate caller from transaction branch
TMSUSPEND PLS_INTEGER UTL_RAW.CAST_TO_BINARY_INTEGER ('02000000') Caller is suspending, not ending, association

Constants (Return Values)
Name Data Type  Value Description
XA_HEURCOM PLS_INTEGER 7 Transaction branch has been heuristically committed
XA_HEURHAZ PLS_INTEGER 8 Transaction branch may have been heuristically completed
XA_HEURMIX PLS_INTEGER 5 Some of the transaction branches have been heuristically committed, others rolled back
XA_HEURRB PLS_INTEGER 6 Transaction branch has been heuristically rolled back
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 was 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 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
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
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

Defined 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
Security Model Execute is granted to PUBLIC.
Files of Interest {ORACLE_HOME}/rdbms/admin/xaview.sql
 
DIST_TXN_SYNC
Used in recovery of synchronization when utilizing Oracle Real Application Clusters (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;
/
 
Demo

DBMS_XA Demo		 -- Session 1 starts a transaction and does some work.
conn hr/hr

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;
/
-- Session 2 resumes the transaction and does some work

conn hr/hr

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;
/
-- Session 3 commits the transaction

conn hr/hr

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;
/
 
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----