XA transactions and Oracle RAC…

…don’t work in 11.2.0. Perhaps more specifically, I (and everyone I have found blogging about it, as well as on oracle forums) can’t get it to work.

The documentation states the following…

“Tightly coupled XA transactions no longer require the special type of singleton services (that is, Oracle Distributed Transaction Processing (DTP) services) to be deployed on Oracle RAC database. XA transactions are transparently supported on Oracle RAC databases with any type of services configuration.”

…I opened an SR on this, and after several back and forth conversations and test cases uploaded, the analyst finally stated it doesn’t work.

The support analyst also referenced a documentation bug, shown below…


“As per Bug 9694442 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9694442] : ORA-24798 XA ROM WITHIN A SERVICE TO CALL ANOTHER SERVICE, which was raised for a similar issue, this seems to be a documentation Bug which claims DTP to be not required for XA transactions in 11.1
The suggested solution to go to singleton services DTP and will not cause issues.
Note that an Enhancement request 9728136 was raised to correct documentation on future releases.”

The DOC ID 1106793.1 shows the original issue, which was related to joining or resuming a transaction; however, none of it seems to work.

According to the documentation, to enable it you must have the compatible parameter set to at least 11.1.0.7 (thanks to Steve, author of the first comment below), and the global_txn_processes set to at least 1 (the default).

What works:

* Branches in a tightly coupled transaction group can see (via a SELECT) uncommitted changes in each others branches on different nodes
* Branches that perform DML in a tightly coupled transaction can be on different nodes, provided there are no transactions that rely on a change on another node to which they have referential integrity constraints defined
* Branches on the same node can see (via SELECT) and act (UPDATE and INSERT) on each others row locks, even those that have a referential integrity constraint in place that would normally block

What doesn’t work is a primary key insert on one node, and then a child record insert on another node that depends on that first insert. It simply can’t act on the insert (but can see it), or at least not as part of its global transaction. It’s no different than if you have a normal transaction in one session that inserts a primary key, but doesn’t commit. Any change in another session that relies on that insert in the first session will wait on a transaction lock until the first session commits.

Before running the example below, create a table called parent with a single column number primary key, and a child table with two columns, the first of which references that primary key.


import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.transaction.xa.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.client.*;

public class testXA implements Runnable {

  Thread t;
  static String SAME_INSTANCE;
  String DO;

  public static void main(String args[]) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      SAME_INSTANCE = args[0];
      System.out.println(new java.util.Date());
      testXA r1 = new testXA("run");
      testXA r2 = new testXA("check locks");
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  testXA(String what) {
    this.DO = what;
    try {
      t = new Thread(this);
      t.start();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  public void run() {



    try {

	  if (this.DO.equals("run")) {

        //get value to insert

        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:cmh/[email protected]:1521/online.home");
        Statement stm = conn.createStatement();
        ResultSet rst = stm.executeQuery("select max(c) + 1 from parent");
        int i = 0;
        while (rst.next()) {
          i = rst.getInt(1);
        }

        //end get value to insert

        //data source for each db is the root of all XA activity

        OracleXADataSource OXADSLocal = new OracleXADataSource();
        OracleXADataSource OXADSRemote = new OracleXADataSource();

        //configure each data source so it can connect

        OXADSLocal.setURL("jdbc:oracle:thin:@192.168.1.50:1521/online.home");
        OXADSLocal.setUser("cmh");
        OXADSLocal.setPassword("cmh");

        //if we connect to the same instance for each, it won't block.
        //if we connect to different instances of the same database, it will block, as the child can't "see" the parent insert

        if (testXA.SAME_INSTANCE.equals("same"))
          OXADSRemote.setURL("jdbc:oracle:thin:@192.168.1.50:1521/online.home");
        else
          OXADSRemote.setURL("jdbc:oracle:thin:@192.168.1.51:1521/online.home");
        OXADSRemote.setUser("cmh");
        OXADSRemote.setPassword("cmh");

        //get an XA connection from each data source

        XAConnection XACLocal = OXADSLocal.getXAConnection();
        XAConnection XACRemote = OXADSRemote.getXAConnection();

        //then get a "regular" connection from the XA connection

        Connection ConnLocal = XACLocal.getConnection();
        Connection ConnRemote = XACRemote.getConnection();

        //now, we create an XA resource on our "regular" connection

        XAResource XARLocal = XACLocal.getXAResource();
        XAResource XARRemote = XACRemote.getXAResource();

        XARLocal.setTransactionTimeout(20);
        XARRemote.setTransactionTimeout(30);

        System.out.println("Driver Version " + ConnLocal.getMetaData().getDriverVersion());
        System.out.println("Local (parent) URL = " + OXADSLocal.getURL());
        System.out.println("Local (parent) XAResource timeout is " + XARLocal.getTransactionTimeout() + " seconds");
        System.out.println("Remote (parent) URL = " + OXADSRemote.getURL());
        System.out.println("Remote (child) XAResource timeout is " + XARRemote.getTransactionTimeout() + " seconds");

        if (XARLocal.isSameRM(XARRemote))
          System.out.println("Same resource manager for both branches of transaction");
        else
          System.out.println("Different resource manager for each branch of transaction");

        //use the same byte array group id

        byte [] gid = new byte[64];
        gid[0] = (byte) 1;

        //create unique byte array branch id for each connection

        byte [] bqidLocal = new byte[64];
        bqidLocal[0] = (byte) 1;
        byte[] bqidRemote = new byte[64];
        bqidRemote[0] = (byte) 2;

        Xid XidLocal = new OracleXid(0x1234, gid, bqidLocal);
        Xid XidRemote = new OracleXid(0x1234, gid, bqidRemote);

        XARLocal.start(XidLocal, XAResource.TMNOFLAGS);
        XARRemote.start(XidRemote, XAResource.TMNOFLAGS);

        Statement local = ConnLocal.createStatement();
        PreparedStatement remote = ConnRemote.prepareStatement("INSERT INTO child values(" + i + "," + i + ")");
        local.executeUpdate("INSERT INTO parent values(" + i + ")");
        XARLocal.end(XidLocal, XAResource.TMSUCCESS);
        System.out.println("pk inserted");
        remote.executeUpdate();
        System.out.println("child inserted");

        XARRemote.end(XidRemote, XAResource.TMSUCCESS);

        int resultLocal = XARLocal.prepare(XidLocal);
        int resultRemote = XARRemote.prepare(XidRemote);

        boolean doCommit = true;
        if (!((resultLocal == XAResource.XA_OK) || (resultLocal == XAResource.XA_RDONLY))) {
          doCommit = false;
        }
        if (!((resultRemote == XAResource.XA_OK) || (resultRemote == XAResource.XA_RDONLY))) {
          doCommit = false;
        }

        if (resultLocal == XAResource.XA_OK) {
          if (doCommit) {
            System.out.println("Performing local commit");
            XARLocal.commit(XidLocal, false);
          }
          else {
            System.out.println("Performing local rollback");
            XARLocal.rollback(XidLocal);
          }
        }
        if (resultRemote == XAResource.XA_OK) {
          if (doCommit) {
            System.out.println("Performing remote commit");
            XARRemote.commit(XidRemote, false);
          }
          else {
            System.out.println("Performing remote rollback");
            XARRemote.rollback(XidRemote);
          }
        }

        ConnLocal.close();
        ConnRemote.close();
        XACLocal.close();
        XACRemote.close();
        local.close();
        remote.close();
	  }
	  else {
		Thread.sleep(10000);
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:cmh/[email protected]:1521/online.home");
        Statement stm = conn.createStatement();
	    ResultSet rst = stm.executeQuery("select distinct type from gv$lock order by 1");
	    System.out.println("Current list of enqueues in all instances of database (should be at least one DX " +
	                       "unless the child has been successfully inserted)");
	    while (rst.next()) {
		  System.out.println(rst.getString(1));
		}
		rst = stm.executeQuery("select * from gv$global_transaction");
	    System.out.println("Current list of global transactions in the cluster");
	    ResultSetMetaData rstMeta = rst.getMetaData();
	    while (rst.next()) {
		  for (int i = 1; i <= rstMeta.getColumnCount(); i++) {
  		    System.out.println(String.format("%1$-30s", rstMeta.getColumnName(i)) + rst.getString(i));
		  }
		  System.out.println("-------------------------------------------------------------");
		}
	  }
    }
    catch(Exception e) {
          e.printStackTrace();
        }
  }
}

Below are the results of the test when run against separate instances.

c:\java>java testXA diff
Fri Jul 13 20:19:25 EDT 2012
Driver Version 11.2.0.1.0
Local (parent) URL = jdbc:oracle:thin:@192.168.1.50:1521/online.home
Local (parent) XAResource timeout is 60 seconds
Remote (parent) URL = jdbc:oracle:thin:@192.168.1.51:1521/online.home
Remote (child) XAResource timeout is 60 seconds
Different resource manager for each branch of transaction
pk inserted
Current list of enqueues in all instances of database (should be at least one DX
 unless the child has been successfully inserted)
AE
CF
CO
DM
KD
KT
MN
MR
PS
RD
RS
RT
SR
TM
TS
TX
WP
XR
Current list of global transactions in the cluster
INST_ID                       1
FORMATID                      4660
GLOBALID                      01000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000
BRANCHID                      01000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000
BRANCHES                      1
REFCOUNT                      1
PREPARECOUNT                  0
STATE                         ACTIVE
FLAGS                         0
COUPLING                      TIGHTLY COUPLED
-------------------------------------------------------------
INST_ID                       2
FORMATID                      4660
GLOBALID                      01000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000
BRANCHID                      02000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000
BRANCHES                      1
REFCOUNT                      1
PREPARECOUNT                  0
STATE                         [MULTINODE]ACTIVE
FLAGS                         1024
COUPLING                      TIGHTLY COUPLED
-------------------------------------------------------------
java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction wa
iting for lock

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta
tement.java:1008)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1307)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
edStatement.java:3530)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(Oracl
ePreparedStatementWrapper.java:1350)
        at testXA.run(testXA.java:132)
        at java.lang.Thread.run(Thread.java:662)

c:\java>

I also ran the PL/SQL code below using the DBMS_XA PL/SQL package, just to ensure I wasn't missing a switch in java.

On node 1...

SQL> declare
  2    l number;
  3  begin
  4    l := dbms_xa.xa_start(dbms_xa_xid(123,hextoraw('1'),hextoraw('1')), dbms_xa.tmnoflags);
  5    insert into parent values(10000);
  6    l := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

...and on node 2...

SQL> declare
  2    l number;
  3  begin
  4    l := dbms_xa.xa_start(dbms_xa_xid(123,hextoraw('1'),hextoraw('2')), dbms_xa.tmnoflags);
  5    insert into child values(10000,10000);
  6    l := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);
  7    l := dbms_xa.xa_commit(dbms_xa_xid(123), true);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at line 5


SQL>

As noted, the interesting thing is that I can *see* the uncommitted insert on the first node from the second node if I use tight coupling (the default). If I start the XAResource with ORATRANSLOOSE flag, I can't see the uncommitted insert on the first node. This leads me to believe that *some* of it works. However, foreign key references will time out waiting on a transaction lock, which is the exact opposite of Oracle's current official documentation for releases at or above 11.1.0.7 in a RAC.

4 comments for “XA transactions and Oracle RAC…

  1. Steve
    July 13, 2012 at 1:49 AM

    What is the value of the compatible parameter, it needs to be at least 11.0.0.0.

  2. July 13, 2012 at 10:32 AM

    Hi Steve,

    Yep, it’s actually set to 11.2.0.3. The odd thing is, when I run the code, I never see a DX enqueue anywhere in either instance, which it should have. However, the transaction shows as tightly coupled, with the same XID and different branch id. This is what the documentation says should be the case. I also have the GTX parameter set to the default of 1, so Oracle will automatically manage the number of global transaction processes.

    The documentation itself isn’t clear. Right below the documented note I had in the original post is the following:

    “An external transaction manager, such as Oracle Services for Microsoft Transaction Server (OraMTS), coordinates DTP/XA transactions. However, an internal Oracle transaction manager coordinates distributed SQL transactions. Both DTP/XA and distributed SQL transactions must use the DTP service in Oracle RAC.

    Thanks,

    Steve

  3. Sergio
    June 22, 2018 at 2:05 PM

    Hi Steve,
    We are facing the same issue in a 11.2.04 Oracle RAC database, we can’t work with distributed transactions. We followed all the steps described in “Best Practices for Using XA with RAC”, but is not working.
    Do you know if this issue is fixed on 12c version?

  4. June 29, 2018 at 1:52 PM

    Hi Sergio – No, unfortunately I don’t know if this is resolved in 12c or not.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.