Ours is a JEE application deployed on JBoss 5.1.0 GA and Oracle 11g. We use XA transactions and use JTS transactions for co-ordination across different app servers.
We also use Oracle Global Temporary tables (GTT)
We are facing issues with random occurrences of
- java.sql.SQLException: ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "CORE_API.PKG_OS_WF", line 5757
ORA-06512: at line 1
This happens when DB session has uncommitted changes to our Global temporary table (abs_lookup_sql) table in a given transaction and somehow another transaction tries to query same table using the same DB session.
XA datasource property <track-connection-by-tx> was changed to true on app server to tie a given connection to a transaction, but we have still seen errors after that.The error is not recreatable and happens randomly.
This error does not happen when running same codebase in Websphere 7.0/8.5. We suspect error is due to some transaction and connection management behavior specific to JBoss.
Can you please advice if there is any known issue around usage of Oracle GTT within XA transactions or how we can troubleshoot this further. Please find below XA datasource configuration.
This is a major issue impacting our clients and any help will be greatly appreciated.
<xa-datasource-property name="Password">CORE1_API</xa-datasource-property> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
<!-- The maximum connections in a pool/sub-pool -->
<!-- The time before an unused connection is destroyed -->
<!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
<!-- Whether to check all statements are closed when the connection is returned to the pool,
this is a debugging feature that should be turned off in production -->