It looks like you need an external JTA TransactionManager to coordinate
these two resources ( oneis database and the other is Oracle AQ).
On Fri, Oct 15, 2021 at 10:20 PM Mattern, Alex <alex.matt...@bbh.com.invalid>
wrote:
> Any advice on having one Oracle connection reused for both insert and jms
> within the same transaction? We have been attempting find the solution to
> this problem since camel version 2.4.
>
> Alex Mattern | AVP | Infomediary Architect | Investor Services
>
> BROWN BROTHERS HARRIMAN
> 50 Post Office Square, Boston, MA 02110
> T 617-772-0096 | M 857-283-3724 | alex.matt...@bbh.com
> www.bbh.com
>
> -----Original Message-----
> From: Mattern, Alex <alex.matt...@bbh.com.INVALID>
> Sent: Tuesday, September 28, 2021 9:08 AM
> To: users@camel.apache.org
> Subject: [EXTERNAL SENDER:] Apache Camel single transaction with oracle aq
> and row inserts
>
> 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://urldefense.com/v3/__https://stackoverflow.com/questions/67493188/apache-camel-single-transaction-with-oracle-aq-and-row-inserts__;!!KV6Wb-o!rHWrcae-klU9LhF2s3pTwkUkwHPpLsQoNMxKT7ZtRRL-zr45iWe2s584Iubrbhas$
> --
> 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.
>
> ******************************************************************************
>
>
