…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. <\/p>\n
The documentation states the following…<\/p>\n
“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.”<\/p>\n
…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.<\/p>\n
The support analyst also referenced a documentation bug, shown below…<\/p>\n
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.<\/p>\n 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).<\/p>\n What works:<\/p>\n * Branches in a tightly coupled transaction group can see (via a SELECT) uncommitted changes in each others branches on different nodes 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.<\/p>\n 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.<\/p>\n Below are the results of the test when run against separate instances.<\/p>\n 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.<\/p>\n On node 1...<\/p>\n ...and on node 2...<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" …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…<\/p>\n
\n“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
\nThe suggested solution to go to singleton services DTP and will not cause issues.
\nNote that an Enhancement request 9728136 was raised to correct documentation on future releases.”
\n<\/font><\/p>\n
\n* 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
\n* 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<\/p>\n\r\n\r\nimport java.sql.*;\r\nimport javax.sql.*;\r\nimport java.util.*;\r\nimport javax.transaction.xa.*;\r\nimport oracle.jdbc.xa.OracleXid;\r\nimport oracle.jdbc.xa.client.*;\r\n\r\npublic class testXA implements Runnable {\r\n\r\n Thread t;\r\n static String SAME_INSTANCE;\r\n String DO;\r\n\r\n public static void main(String args[]) {\r\n try {\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n SAME_INSTANCE = args[0];\r\n System.out.println(new java.util.Date());\r\n testXA r1 = new testXA(\"run\");\r\n testXA r2 = new testXA(\"check locks\");\r\n }\r\n catch(Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n\r\n testXA(String what) {\r\n this.DO = what;\r\n try {\r\n t = new Thread(this);\r\n t.start();\r\n }\r\n catch (Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n\r\n public void run() {\r\n\r\n\r\n\r\n try {\r\n\r\n\t if (this.DO.equals(\"run\")) {\r\n\r\n \/\/get value to insert\r\n\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n Connection conn = DriverManager.getConnection(\"jdbc:oracle:thin:cmh\/cmh@192.168.1.50:1521\/online.home\");\r\n Statement stm = conn.createStatement();\r\n ResultSet rst = stm.executeQuery(\"select max(c) + 1 from parent\");\r\n int i = 0;\r\n while (rst.next()) {\r\n i = rst.getInt(1);\r\n }\r\n\r\n \/\/end get value to insert\r\n\r\n \/\/data source for each db is the root of all XA activity\r\n\r\n OracleXADataSource OXADSLocal = new OracleXADataSource();\r\n OracleXADataSource OXADSRemote = new OracleXADataSource();\r\n\r\n \/\/configure each data source so it can connect\r\n\r\n OXADSLocal.setURL(\"jdbc:oracle:thin:@192.168.1.50:1521\/online.home\");\r\n OXADSLocal.setUser(\"cmh\");\r\n OXADSLocal.setPassword(\"cmh\");\r\n\r\n \/\/if we connect to the same instance for each, it won't block.\r\n \/\/if we connect to different instances of the same database, it will block, as the child can't \"see\" the parent insert\r\n\r\n if (testXA.SAME_INSTANCE.equals(\"same\"))\r\n OXADSRemote.setURL(\"jdbc:oracle:thin:@192.168.1.50:1521\/online.home\");\r\n else\r\n OXADSRemote.setURL(\"jdbc:oracle:thin:@192.168.1.51:1521\/online.home\");\r\n OXADSRemote.setUser(\"cmh\");\r\n OXADSRemote.setPassword(\"cmh\");\r\n\r\n \/\/get an XA connection from each data source\r\n\r\n XAConnection XACLocal = OXADSLocal.getXAConnection();\r\n XAConnection XACRemote = OXADSRemote.getXAConnection();\r\n\r\n \/\/then get a \"regular\" connection from the XA connection\r\n\r\n Connection ConnLocal = XACLocal.getConnection();\r\n Connection ConnRemote = XACRemote.getConnection();\r\n\r\n \/\/now, we create an XA resource on our \"regular\" connection\r\n\r\n XAResource XARLocal = XACLocal.getXAResource();\r\n XAResource XARRemote = XACRemote.getXAResource();\r\n\r\n XARLocal.setTransactionTimeout(20);\r\n XARRemote.setTransactionTimeout(30);\r\n\r\n System.out.println(\"Driver Version \" + ConnLocal.getMetaData().getDriverVersion());\r\n System.out.println(\"Local (parent) URL = \" + OXADSLocal.getURL());\r\n System.out.println(\"Local (parent) XAResource timeout is \" + XARLocal.getTransactionTimeout() + \" seconds\");\r\n System.out.println(\"Remote (parent) URL = \" + OXADSRemote.getURL());\r\n System.out.println(\"Remote (child) XAResource timeout is \" + XARRemote.getTransactionTimeout() + \" seconds\");\r\n\r\n if (XARLocal.isSameRM(XARRemote))\r\n System.out.println(\"Same resource manager for both branches of transaction\");\r\n else\r\n System.out.println(\"Different resource manager for each branch of transaction\");\r\n\r\n \/\/use the same byte array group id\r\n\r\n byte [] gid = new byte[64];\r\n gid[0] = (byte) 1;\r\n\r\n \/\/create unique byte array branch id for each connection\r\n\r\n byte [] bqidLocal = new byte[64];\r\n bqidLocal[0] = (byte) 1;\r\n byte[] bqidRemote = new byte[64];\r\n bqidRemote[0] = (byte) 2;\r\n\r\n Xid XidLocal = new OracleXid(0x1234, gid, bqidLocal);\r\n Xid XidRemote = new OracleXid(0x1234, gid, bqidRemote);\r\n\r\n XARLocal.start(XidLocal, XAResource.TMNOFLAGS);\r\n XARRemote.start(XidRemote, XAResource.TMNOFLAGS);\r\n\r\n Statement local = ConnLocal.createStatement();\r\n PreparedStatement remote = ConnRemote.prepareStatement(\"INSERT INTO child values(\" + i + \",\" + i + \")\");\r\n local.executeUpdate(\"INSERT INTO parent values(\" + i + \")\");\r\n XARLocal.end(XidLocal, XAResource.TMSUCCESS);\r\n System.out.println(\"pk inserted\");\r\n remote.executeUpdate();\r\n System.out.println(\"child inserted\");\r\n\r\n XARRemote.end(XidRemote, XAResource.TMSUCCESS);\r\n\r\n int resultLocal = XARLocal.prepare(XidLocal);\r\n int resultRemote = XARRemote.prepare(XidRemote);\r\n\r\n boolean doCommit = true;\r\n if (!((resultLocal == XAResource.XA_OK) || (resultLocal == XAResource.XA_RDONLY))) {\r\n doCommit = false;\r\n }\r\n if (!((resultRemote == XAResource.XA_OK) || (resultRemote == XAResource.XA_RDONLY))) {\r\n doCommit = false;\r\n }\r\n\r\n if (resultLocal == XAResource.XA_OK) {\r\n if (doCommit) {\r\n System.out.println(\"Performing local commit\");\r\n XARLocal.commit(XidLocal, false);\r\n }\r\n else {\r\n System.out.println(\"Performing local rollback\");\r\n XARLocal.rollback(XidLocal);\r\n }\r\n }\r\n if (resultRemote == XAResource.XA_OK) {\r\n if (doCommit) {\r\n System.out.println(\"Performing remote commit\");\r\n XARRemote.commit(XidRemote, false);\r\n }\r\n else {\r\n System.out.println(\"Performing remote rollback\");\r\n XARRemote.rollback(XidRemote);\r\n }\r\n }\r\n\r\n ConnLocal.close();\r\n ConnRemote.close();\r\n XACLocal.close();\r\n XACRemote.close();\r\n local.close();\r\n remote.close();\r\n\t }\r\n\t else {\r\n\t\tThread.sleep(10000);\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n Connection conn = DriverManager.getConnection(\"jdbc:oracle:thin:cmh\/cmh@192.168.1.50:1521\/online.home\");\r\n Statement stm = conn.createStatement();\r\n\t ResultSet rst = stm.executeQuery(\"select distinct type from gv$lock order by 1\");\r\n\t System.out.println(\"Current list of enqueues in all instances of database (should be at least one DX \" +\r\n\t \"unless the child has been successfully inserted)\");\r\n\t while (rst.next()) {\r\n\t\t System.out.println(rst.getString(1));\r\n\t\t}\r\n\t\trst = stm.executeQuery(\"select * from gv$global_transaction\");\r\n\t System.out.println(\"Current list of global transactions in the cluster\");\r\n\t ResultSetMetaData rstMeta = rst.getMetaData();\r\n\t while (rst.next()) {\r\n\t\t for (int i = 1; i <= rstMeta.getColumnCount(); i++) {\r\n \t\t System.out.println(String.format(\"%1$-30s\", rstMeta.getColumnName(i)) + rst.getString(i));\r\n\t\t }\r\n\t\t System.out.println(\"-------------------------------------------------------------\");\r\n\t\t}\r\n\t }\r\n }\r\n catch(Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n}\r\n<\/pre>\n\r\nc:\\java>java testXA diff\r\nFri Jul 13 20:19:25 EDT 2012\r\nDriver Version 11.2.0.1.0\r\nLocal (parent) URL = jdbc:oracle:thin:@192.168.1.50:1521\/online.home\r\nLocal (parent) XAResource timeout is 60 seconds\r\nRemote (parent) URL = jdbc:oracle:thin:@192.168.1.51:1521\/online.home\r\nRemote (child) XAResource timeout is 60 seconds\r\nDifferent resource manager for each branch of transaction\r\npk inserted\r\nCurrent list of enqueues in all instances of database (should be at least one DX\r\n unless the child has been successfully inserted)\r\nAE\r\nCF\r\nCO\r\nDM\r\nKD\r\nKT\r\nMN\r\nMR\r\nPS\r\nRD\r\nRS\r\nRT\r\nSR\r\nTM\r\nTS\r\nTX\r\nWP\r\nXR\r\nCurrent list of global transactions in the cluster\r\nINST_ID 1\r\nFORMATID 4660\r\nGLOBALID 01000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000000000000000000000000000\r\nBRANCHID 01000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000000000000000000000000000\r\nBRANCHES 1\r\nREFCOUNT 1\r\nPREPARECOUNT 0\r\nSTATE ACTIVE\r\nFLAGS 0\r\nCOUPLING TIGHTLY COUPLED\r\n-------------------------------------------------------------\r\nINST_ID 2\r\nFORMATID 4660\r\nGLOBALID 01000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000000000000000000000000000\r\nBRANCHID 02000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000000000000000000000000000\r\nBRANCHES 1\r\nREFCOUNT 1\r\nPREPARECOUNT 0\r\nSTATE [MULTINODE]ACTIVE\r\nFLAGS 1024\r\nCOUPLING TIGHTLY COUPLED\r\n-------------------------------------------------------------\r\njava.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction wa\r\niting for lock\r\n\r\n at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)\r\n at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)\r\n at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)\r\n at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)\r\n at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)\r\n at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)\r\n at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.\r\njava:205)\r\n at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta\r\ntement.java:1008)\r\n at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme\r\nnt.java:1307)\r\n at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep\r\naredStatement.java:3449)\r\n at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar\r\nedStatement.java:3530)\r\n at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(Oracl\r\nePreparedStatementWrapper.java:1350)\r\n at testXA.run(testXA.java:132)\r\n at java.lang.Thread.run(Thread.java:662)\r\n\r\nc:\\java>\r\n<\/pre>\n
\r\nSQL> declare\r\n 2 l number;\r\n 3 begin\r\n 4 l := dbms_xa.xa_start(dbms_xa_xid(123,hextoraw('1'),hextoraw('1')), dbms_xa.tmnoflags);\r\n 5 insert into parent values(10000);\r\n 6 l := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);\r\n 7 end;\r\n 8 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\n<\/pre>\n\r\nSQL> declare\r\n 2 l number;\r\n 3 begin\r\n 4 l := dbms_xa.xa_start(dbms_xa_xid(123,hextoraw('1'),hextoraw('2')), dbms_xa.tmnoflags);\r\n 5 insert into child values(10000,10000);\r\n 6 l := dbms_xa.xa_end(dbms_xa_xid(123), dbms_xa.tmsuccess);\r\n 7 l := dbms_xa.xa_commit(dbms_xa_xid(123), true);\r\n 8 end;\r\n 9 \/\r\ndeclare\r\n*\r\nERROR at line 1:\r\nORA-02049: timeout: distributed transaction waiting for lock\r\nORA-06512: at line 5\r\n\r\n\r\nSQL>\r\n<\/pre>\n