DSRA0304E with an XA error of XAER_PROTO an Oracle error code 24776 when LTC is used on "SELECT ... FOR UPDATE

Cause

The cause of the problem is that the Oracle JDBC driver does not commit the LTC even though the auto commit is set to true. The WebSphere Application Server assumes the auto commit did in fact commit the LTC. So, it does its normal processing after a commit has been done and the connection has been closed. It returns the connection to the connection pool for reuse. When the subsequent global transaction is started and it gets this same connection from the connection pool, the following errors are logged:

[6/16/14 16:53:53:119 CDT] 0000005a WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are:

The XA Error is : -6
The XA Error message is : Routine was invoked in an inproper context.
The Oracle Error code is : 24776
The Oracle Error message is: Internal XA Error
The cause is : null.

[6/16/14 16:53:53:123 CDT] 0000005a WSRdbXaResour E DSRA0302E: XAException occurred. Error code is: XAER_PROTO (-6). Exception is: <null>

[6/16/14 16:53:53:269 CDT] 0000005a XATransaction E J2CA0027E: An exception occurred while invoking start on an XA Resource Adapter from dataSource jdbc/WowOraXa, within transaction ID {XidImpl: formatId(57415344), gtrid_length(36), bqual_length(54), data(00000113368720a60000000100000004f19fea203e512a61832608ccd614dcd70415c55b00000113368720a60000000100000004f19fea203e512a61832608ccd614dcd70415c55b000000010000000000000000000000000001)}: oracle.jdbc.xa.OracleXAException
at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:938)
at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:244)
at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1322)
at com.ibm.ejs.j2c.XATransactionWrapper.start(XATransactionWrapper.java:1289)

The LTC that uses the same connection could be on a different thread. What causes the problem is the use of the same connection pool connection. Since the Oracle JDBC driver did not commit the LTC, it throws the ORA-24776 to indicate that the previous LTC has not been committed. This appears to be a change in the behavior of the Oracle JDBC driver.

Sample of trace events showing the events that lead to this problem

This is the trace event that shows what Oracle connection maps to the WebSphere AppServer connection pool connection

[6/16/14 16:47:26:212 CDT] 0000005a WSJdbcConnect > <init> Entry
WSRdbManagedConnectionImpl@1743eecf
oracle.jdbc.driver.LogicalConnection@3486aecf

[6/16/14 16:47:26:214 CDT] 0000005a WSJdbcConnect > prepareStatement Entry
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@3beee90
SELECT IOM_XML_LOG_SEQ.NEXTVAL FROM DUAL
TYPE FORWARD ONLY (1003)
CONCUR READ ONLY (1007)

This shows that the transaction is an LTC

[6/16/14 16:47:26:214 CDT] 0000005a LocalTranCurr > getLocalTranCoord Entry
com.ibm.ws.LocalTransaction.LocalTranCurrentImpl@4731aee5

This shows that AutoCommit is set for the connection
[6/16/14 16:47:26:214 CDT] 0000005a WSRdbManagedC > enforceAutoCommit Entry
true

This shows the connection being returned to the connection pool once the connection is closed after the SELECT for update

[6/16/14 16:47:26:255 CDT] 0000005a PoolManager 3 released managed connection WSRdbManagedConnectionImpl@1743eecf

This shows the connection being used by the global TX

[6/16/14 16:53:58:629 CDT] 00000057 WSJdbcConnect > <init> Entry
WSRdbManagedConnectionImpl@1743eecf
oracle.jdbc.driver.LogicalConnection@3486aecf

This shows the begin called on the global TX

[6/16/14 16:53:58:413 CDT] 00000057 TransactionIm 1 (SPI) Transaction BEGIN occurred for TX: 5

This first of the errors that is logged when the transaction manager attempts to start the global tx on the Oracle driver's resource manager.

[6/16/14 16:53:58:637 CDT] 00000057 WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are:
The XA Error is : -6
The XA Error message is : Routine was invoked in an inproper context.
The Oracle Error code is : 24776