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:
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.
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.
-- 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;
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;
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 ...');
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;
/