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

["Ben D'Herville (JIRA)" <ji...@apache.org>]

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

Ben D'Herville updated CAMEL-6144:
----------------------------------
    Comment: was deleted

(was: This is a bit of a nasty one.  I assumed, as it wasn't written anywhere, that enabling optimistic locking would prevent any race conditions from occurring.  If we hadn't tested extensively we wouldn't have noticed it wasn't catching race conditions when doing updates which caused messages to be lost and aggregations to fail which would have cost us and potentially our customers financially.  

It would be worth explaining the limits of optimistic locking in the current implementations of JdbcAggregationRepository and MemoryAggregationRepository in the documentation at least.  Both implementations prevent multiple inserts and won't update if the key doesn't exist but neither prevent race conditions in concurrent updates.  Neither are suitable for use where multiple threads (on a single server or distributed across servers) can process exchanges from the same aggregation group in parallel and message loss is a concern.

I have modified JdbcAggregationRepository to use a version in the DB for our project.  I think this change is safe for general use so I'll submit it in a pull request soon.)

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