When XA connection pools are used for the datasources which will involve transactions with the database and other resources, these are the most common issues faced:
1) Exception: : javax.ejb.EJBException: nested exception is: java.sql.SQLException: ORA-02049: timeout: distributed transaction waiting for lock
2) EJB Exception: : javax.ejb.EJBException: nested exception is: java.sql.SQLException: Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAER_NOTA : The XID is not valid start() failed on resource ‘CONNECTION_NAME’: XAER_NOTA : The XID is not valid
The XAER_NOTA error raises due to, using two different JDBCConnection Pools for the same Database (Oracle) or two DataSources for the same pool.
XAER_NOTA errors are also seen during recovery.
These errors are thrown for transactions that have been committed before the server restart but still exist in the transaction log at the time the server was booted.
During recovery processing, for each transaction record in the transaction log, the transaction manager will inform the participating resources of the commit decision.
If the resource commit directive succeeded before the restart, the resource manager will respond to a subsequent commit with XAER_NOTA because it no longer has knowledge of the Xid.
The transaction manager ignores this error assuming that the commit succeeded before the crash.
The reason why there are transaction log records that exist for transactions that have already completed is because the transaction manager only removes entries during checkpoint operations.
A checkpoint occurs every five minutes by default and deletes transaction log files for which all records have been released.
The checkpoint interval can be configured via the JTAMBean.CheckpointIntervalSeconds attribute. You can set the Checkpoint Interval in the administration console on the Domain —> Configuration —> JTA tab.
To get more information about the issue, enable the below debug flags on the server:
The javax.transaction.xa.XAResource interface provides a method, setTransactionTimeout, which in some driver implementations sets the resource’s internal timeout interval.
The WebLogic transaction manager can be instructed to invoke this method with a value equal to the global transaction timeout prior to each resource enlistment.
For JDBC connection pool configurations, set the attribute “XASetTransactionTimeout” to “true” to enable this feature.
Note that this JDBCConnectionPool attribute is only applicable for XA-compliant drivers.
Also note that setting this attribute has no affect on XA drivers that do not implement the XAResource.setTransactionTimeout method.
The Oracle thin driver supports XAResource.setTransactionTimeout. The WebLogic jDriver for Oracle driver does not implement this method.
Below are the tunings that should help to resolve the issue:
– Use one connection pool for one datasource.
– Merge the 2 XA pools for one single database.
– Try to set autoCommit to true or call connection.commit() before returning the connection to the connection pool.
Transaction related tunings:
– XATransactionTimeout=”300″ (This value must be greater than the JTA timeoutseconds value)
– DISTRIBUTED_LOCK_TIMEOUT on the database should be greater than XATransactionTimeout.
– Database Pool XATransactionTimeout should be greater than Global transaction timeout.
If these tunings do not solve the issue, do revert with the debugs enabled for the server so that the logs give more information about the transaction internals.