I would like to have one transaction, one commit and one database connection
for an Apache Camel route that only uses Oracle. In my current setup it appears
that the Oracle AQ JMS transaction and the Oracle INSERT transaction are
separate.
Why do I want it
With separate transactions there is the possibility of duplicate row
INSERTs.
The current setup borrows two database connections from the pool. One for
JMS and one for INSERT. Every route uses twice the number of database
connections of which there are a limited supply.
How do I know
When the routes are running under load I can kill -9 the camel process. The
result is that the in-flight message rolls back, but the INSERTed row remains.
I have some apache camel routes that operate on the Oracle database. The route
starts from an Oracle AQ, inserts some rows into a few database tables and
finally sends the message to another Oracle AQ.
Example Route java code
from(getInputAQ())
.routeId("AQ_ROUTE")
.autoStartup(true)
.transacted("PROPAGATION_REQUIRED")
.process("OracleInsertProcessor")
.to(getOutputAQ())
Example INSERT java code
@Autowired
private JdbcTemplate jdbcTemplate;
public int save(List<T> list)
{
int[] insertCountArray = getJdbcTemplate().batchUpdate(getInsertQuery(),
new BatchPreparedStatementSetter()
{
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException
{
buildInsertParameters(ps, list.get(i));
}
@Override
public int getBatchSize()
{
return list.size();
}
});
return getTotalCount(insertCountArray);
}
Context file
<!-- oracle aq set up -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory"
factory-method="getPoolDataSource">
<property name="URL" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.pwd}')}" />
<property name="connectionFactoryClassName"
value="oracle.jdbc.pool.OracleDataSource" />
<property name="connectionPoolName" value="ORACLE_POOL" />
<property name="minPoolSize" value="${minPoolSize}" />
<property name="maxPoolSize" value="${maxPoolSize}" />
<property name="initialPoolSize" value="${initialPoolSize}" />
<property name="queryTimeout" value="${queryTimeout}" />
<property name="inactiveConnectionTimeout"
value="${inactiveConnectionTimeout}" />
<property name="validateConnectionOnBorrow" value="true" />
<property name="secondsToTrustIdleConnection"
value="${secondsToTrustIdleConnection}" />
<property name="timeToLiveConnectionTimeout"
value="${timeToLiveConnectionTimeout}" />
<property name="maxStatements" value="10" />
</bean>
<bean id="dbTransactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
<property name="defaultTimeout" value = "60"/>
</bean>
<bean id="PROPAGATION_REQUIRED"
class="org.apache.camel.spring.spi.SpringTransactionPolicy">
<property name="transactionManager" ref="dbTransactionManager" />
<property name="propagationBehaviorName" value="PROPAGATION_REQUIRED" />
</bean>
<bean id="PROPAGATION_REQUIRES_NEW"
class="org.apache.camel.spring.spi.SpringTransactionPolicy">
<property name="transactionManager" ref="dbTransactionManager" />
<property name="propagationBehaviorName" value="PROPAGATION_REQUIRES_NEW" />
</bean>
<bean id="PROPAGATION_SUPPORTS"
class="org.apache.camel.spring.spi.SpringTransactionPolicy">
<property name="transactionManager" ref="dbTransactionManager" />
<property name="propagationBehaviorName" value="PROPAGATION_SUPPORTS" />
</bean>
<bean id="aqJmsConnectionFactory" class="oracle.jms.AQjmsConnectionFactory">
<property name="datasource" ref="dataSource"/>
</bean>
<bean id="aqjms" class="org.apache.camel.component.jms.JmsComponent">
<property name="connectionFactory" ref="aqJmsConnectionFactory" />
<property name="transacted" value="true" />
<property name="transactionManager" ref="dbTransactionManager" />
</bean>
When thinking about solutions for this problem, it seems that reusing the
database connection used by the Apache Camel JMS component could be a solution.
I suppose there is a problem somewhere in my setup.
--
I have also posted this question to stackoverflow.
https://stackoverflow.com/questions/67493188/apache-camel-single-transaction-with-oracle-aq-and-row-inserts
--
Alex Mattern
*************************** IMPORTANT NOTE*****************************
The opinions expressed in this message and/or any attachments are those of the
author and not necessarily those of Brown Brothers Harriman & Co., its
subsidiaries and affiliates ("BBH"). There is no guarantee that this message is
either private or confidential, and it may have been altered by unauthorized
sources without your or our knowledge. Nothing in the message is capable or
intended to create any legally binding obligations on either party and it is
not intended to provide legal advice. BBH accepts no responsibility for loss or
damage from its use, including damage from virus.
******************************************************************************
