In this case the Oracle AQ and Oracle DB are actually the same resource. Oracle
AQ's implementation of JMS is just an Oracle DB table behind the scenes. I can
connect with one credential and select joining the AQ table and another table.
So I don't believe that an external JTA TransactionManager is required since
this is not a distributed transaction. This should be a single transaction on a
single resource, namely Oracle DB.
--
Alex Mattern
-----Original Message-----
From: Zheng Feng <zf...@redhat.com>
Sent: Friday, October 15, 2021 11:38 AM
To: alex.matt...@bbh.com.invalid
Cc: users@camel.apache.org
Subject: [EXTERNAL SENDER:] Re: Apache Camel single transaction with oracle aq
and row inserts
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
>
> -----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/674931
> 88/apache-camel-single-transaction-with-oracle-aq-and-row-inserts__;!!
> KV6Wb-o!rHWrcae-klU9LhF2s3pTwkUkwHPpLsQoNMxKT7ZtRRL-zr45iWe2s584Iubrbh
> as$
> --
> 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.
>
> **********************************************************************
> ********
>
>
*************************** 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.
******************************************************************************
