You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by "Mattern, Alex" <al...@bbh.com.INVALID> on 2021/09/28 13:07:40 UTC
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://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.
******************************************************************************
Re: Apache Camel single transaction with oracle aq and row inserts
Posted by Zheng Feng <zf...@redhat.com>.
OK, I see. It looks like that DataSourceTransactionManger could associate
the Connection with a thread scope. I'm not sure that the Oracle AQ and DB
can obtain the same connection from the pool. So can you try to set the
DataSource pool minPoolSize and maxPoolSize with "1" to see what happens?
On Sat, Oct 16, 2021 at 1:41 AM Mattern, Alex <al...@bbh.com.invalid>
wrote:
> 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.mattern@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
> <al...@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 <al...@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.
>
> ******************************************************************************
>
RE: Apache Camel single transaction with oracle aq and row inserts
Posted by "Mattern, Alex" <al...@bbh.com.INVALID>.
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.mattern@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 <al...@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 <al...@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.
******************************************************************************
Re: Apache Camel single transaction with oracle aq and row inserts
Posted by Zheng Feng <zf...@redhat.com>.
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 <al...@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.mattern@bbh.com
> www.bbh.com
>
> -----Original Message-----
> From: Mattern, Alex <al...@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.
>
> ******************************************************************************
>
>
RE: Apache Camel single transaction with oracle aq and row inserts
Posted by "Mattern, Alex" <al...@bbh.com.INVALID>.
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.mattern@bbh.com
www.bbh.com
-----Original Message-----
From: Mattern, Alex <al...@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.
******************************************************************************