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

["Alex Mattern (Jira)" <ji...@apache.org>]

Alex Mattern created CAMEL-17050:
------------------------------------

             Summary: 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: 3.11.0, 3.7.2, 3.4.6, 2.4.0
            Reporter: Alex Mattern


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)