[CAMEL-17050] Two transactions for Oracle AQ JMS and Oracle inserts

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Bug
• Affects Version/s: 2.4.0, 3.4.6, 3.7.2, 3.11.0
• Component/s: camel-jms
• Estimated Complexity: Unknown
• Assignee: Unassigned
• Reporter: Alex Mattern
• Created: 07/Oct/21 12:50
• Updated: 07/Oct/21 12:57
• Resolved: 07/Oct/21 12:57

Description

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.

// Example Route Java code
from(getInputAQ())
.routeId("AQ_ROUTE")
.autoStartup(true)
.transacted("PROPAGATION_REQUIRED")
.process("OracleInsertProcessor")
.to(getOutputAQ())

// 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);
}

 // 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>

Acceptance Criteria:

1. Single consumer thread using camel-jms with Oracle AQ only uses one database connection for JMS and CRUD operations.
2. JMS and CRUD operations on a single Oracle database operate within the same transaction to prevent duplicate messages.