[jira] [Commented] (CAMEL-6144) Optimistic Locking Required for JdbcAggregationRepository in order for Camel Aggregation to work in a Clustered environment

["K Smith (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CAMEL-6144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14129153#comment-14129153 ] 

K Smith commented on CAMEL-6144:
--------------------------------

I don't understand how the current implementation (2.13.x) solves problem #2 from the description:

bq. A similar Race condition happens when more than one Camel Aggregator(s) tries to update a row in the AGGREGATION table. This problem does not lead into any exceptions. But it leads into missing exchanges. Because both the Aggregator's are trying to update the same row in the AGGREGATION table, But one update is overwritten by other update, thus losing an exchange.

Simple example: 
* I'm awaiting completion based on counting 5 messages.  
* The current data in the aggregation table indicates 3 have been aggregated so far.  
* If I have either two threads or two process with a camel route that listens to a queue and then aggregates:
** thread one gets the existing message from the aggregation table (#3)
** thread two gets the existing message from the aggregation table (#3)
** thread one aggregates and adds its message to the aggregation table (#4)
** thread two does the same then I've lost the message from thread one  

But there is no exception in this case -- the optimistic lock is only on the message key, there's no versioning going on.

What is wrong with my understanding of the AggregateProcessor and AggregateRepository that prevents the current implementation from  missing exchanges?

> Optimistic Locking Required for JdbcAggregationRepository in order for Camel Aggregation to work in a Clustered environment
> ---------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CAMEL-6144
>                 URL: https://issues.apache.org/jira/browse/CAMEL-6144
>             Project: Camel
>          Issue Type: Improvement
>          Components: camel-sql
>    Affects Versions: 2.9.2
>         Environment: Camel Aggregation in more than one server each of them using JDBCAggregationRepository and using a common DB table to store aggregated exchanges.
>            Reporter: Shivakumar
>            Assignee: Claus Ibsen
>             Fix For: 2.12.0
>
>
> Listing two problems here. And a solution that is needed to fix these problems.
> 1) A Race condition leading to below ConstraintViolationException when two Camel Aggregator's trying to insert into the AGGREGATION DB table for same correlationkey(ID). 
> "org.hibernate.exception.ConstraintViolationException: ORA-00001: unique constraint (USLDB_UAT2.AGGREGATION_PK) violated
> at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74)
> at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
> at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
> at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
> at $Proxy171.executeUpdate(Unknown Source)
> at org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback.doInPreparedStatement(AbstractLobCreatingPreparedStatementCallback.java:73)
> at org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback.doInPreparedStatement(AbstractLobCreatingPreparedStatementCallback.java:1)
> at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
> at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
> at org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository$1.doInTransaction(JdbcAggregationRepository.java:137)
> at org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository$1.doInTransaction(JdbcAggregationRepository.java:113)
> at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
> at org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository.add(JdbcAggregationRepository.java:113)
> at org.apache.camel.processor.aggregate.AggregateProcessor.doAggregation(AggregateProcessor.java:260)
> at org.apache.camel.processor.aggregate.AggregateProcessor.process(AggregateProcessor.java:197)
> at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(As..."
> 2) A similar Race condition happens when more than one Camel Aggregator(s) tries to update a row in the AGGREGATION table. This problem does not lead into any exceptions. But it leads into missing exchanges. Because both the Aggregator's are trying to update the same row in the AGGREGATION table, But one update is overwritten by other update, thus losing an exchange.
> SOLUTION:
> ==========
> Optimistic locking should be enabled / applied  for JdbcAggregationRepository to handle this race condition.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)