[jira] [Resolved] (CAMEL-17050) Two transactions for Oracle AQ JMS and Oracle inserts

["Claus Ibsen (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CAMEL-17050?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Claus Ibsen resolved CAMEL-17050.
---------------------------------
    Resolution: Not A Bug

The auto wired jdbc template is not a Camel component or Camel doing this, its your own custom code.

To make transactions work, then you need to setup spring transaction so it autowrie that jdbc template as a transactional resource and whatnot.

Get help first on the mailing list / chat room.

> Two transactions for Oracle AQ JMS and Oracle inserts
> -----------------------------------------------------
>
>                 Key: CAMEL-17050
>                 URL: https://issues.apache.org/jira/browse/CAMEL-17050
>             Project: Camel
>          Issue Type: Bug
>          Components: camel-jms
>    Affects Versions: 2.4.0, 3.4.6, 3.7.2, 3.11.0
>            Reporter: Alex Mattern
>            Priority: Major
>
> What: Oracle AQ JMS transaction and the Oracle INSERT transaction are separate. These should be committed in the same transaction.
> Why: possible duplicate row insert. Each consumer thread is borrowing two database connections from the pool instead of one.
> Recreate: 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.
> {code:java}
> // Example Route Java code
> from(getInputAQ())
> .routeId("AQ_ROUTE")
> .autoStartup(true)
> .transacted("PROPAGATION_REQUIRED")
> .process("OracleInsertProcessor")
> .to(getOutputAQ())
> {code}
> {code:java}
> // Example Database 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);
> }
> {code}
> {code:java}
>  // Example 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>
> {code}
> Acceptance Criteria:
>  # Single consumer thread using camel-jms with Oracle AQ only uses one database connection for JMS and CRUD operations.
>  # JMS and CRUD operations on a single Oracle database operate within the same transaction to prevent duplicate messages.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)