Hi,
We have a requirement of polling DB table for certain criteria and then send mail using polled records. Here's how my poller jca looks like
<property name="DescriptorName" value="PollTimerTable_DB.NotifyTimer"/>
<property name="QueryName" value="PollTimerTable_DBSelect"/>
<property name="MappingsMetaDataURL" value="PollTimerTable_DB-or-mappings.xml"/>
<property name="PollingStrategy" value="DeletePollingStrategy"/>
<property name="PollingInterval" value="5"/>
<property name="MaxRaiseSize" value="5"/>
<property name="MaxTransactionSize" value="10"/>
<property name="RowsPerPollingInterval" value="10"/>
<property name="NumberOfThreads" value="1"/>
<property name="ReturnSingleResultSet" value="false"/>
Datasource used - XA datasource
Scenario:
The table has 150 records. As MaxRaiseSize is given 5. My OSB pipeline received 5 messages at a time and sends mail. But after polling around 140 records. Last 10 records are staying in DB, although it is being read and mail is being sent. Those records are being read again and again and the following error is seen in DB.
Fast classification of SQLState: null errorCode: 0 message: Unexpected exception while enlisting XAConnection java.sql.SQLException: Transaction rolled back: Transaction timed out after 279 seconds [[
BEA1-6BA138AF7EB8AFADE99B
at weblogic.jdbc.jta.DataSource.enlist(DataSource.java:1831)
at weblogic.jdbc.jta.DataSource.refreshXAConnAndEnlist(DataSource.java:1716)
at weblogic.jdbc.wrapper.JTAConnection.getXAConn(JTAConnection.java:229)
at weblogic.jdbc.wrapper.JTAConnection.checkConnection(JTAConnection.java:91)
at weblogic.jdbc.wrapper.JTAConnection.checkConnection(JTAConnection.java:74)
at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:124)
at weblogic.jdbc.wrapper.Connection.prepareStatement(Connection.java:648)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1565)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1514)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:785)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:621)
at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatch(ParameterizedSQLBatchWritingMechanism.java:149)
at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:134)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.writesCompleted(DatabaseAccessor.java:1845)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.commitTransaction(DatabaseAccessor.java:428)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicCommitTransaction(AbstractSession.java:820)
at org.eclipse.persistence.sessions.server.ClientSession.basicCommitTransaction(ClientSession.java:185)
at org.eclipse.persistence.internal.sessions.AbstractSession.commitTransaction(AbstractSession.java:1024)
at oracle.tip.adapter.db.inbound.PollingStrategy.commitTransaction(PollingStrategy.java:527)
at oracle.tip.adapter.db.InboundWork.transactionalUnitDirectly(InboundWork.java:1828)
at oracle.tip.adapter.db.InboundWork.transactionalUnit(InboundWork.java:1671)
at oracle.tip.adapter.db.InboundWork.runOnce(InboundWork.java:853)
at oracle.tip.adapter.db.InboundWork.run(InboundWork.java:635)
at oracle.tip.adapter.db.inbound.InboundWorkWrapper.run(InboundWorkWrapper.java:43)
at oracle.tip.adapter.sa.impl.fw.jca.work.WorkerJob.go(WorkerJob.java:53)
at oracle.tip.adapter.sa.impl.fw.common.ThreadPool.run(ThreadPool.java:281)
at java.lang.Thread.run(Thread.java:748)
Caused by: weblogic.transaction.TimedOutException: Transaction timed out after 279 seconds
BEA1-6BA138AF7EB8AFADE99B
at weblogic.jdbc.jta.DataSource.enlist(DataSource.java:1829)