jdbc inbound channel adapter concurrent problem in cluster.

[m.ugues]

Hallo all.
I have a spring integration applistence cation deployed on Weblogic 10.3 in cluster mode; Oracle 10g ad RDBMS.

I have this configuration:

<int-jdbc:inbound-channel-adapter id="FirstBatchPoller"
		query="SELECT SC.* FROM S_CONT_BATCH_STAGING SC WHERE SC.COD_STATOELABORAZIONE = 100 AND FND_CONT_LOCK_BATCH_STAGING ( SC.ROWID ) = 1 AND ROWNUM &lt;= 301 FOR UPDATE SKIP LOCKED"  
		channel="preProcessingSplitterBatchMessage" 
		data-source="cont0Data" update="UPDATE S_CONT_BATCH_STAGING SET COD_STATOELABORAZIONE = '300' WHERE MESSAGE_ID IN (:MESSAGE_ID)">
		<int:poller>
			<int:cron-trigger expression="0 0 0/1 * * *" />
			<int:transactional propagation="REQUIRES_NEW" isolation="DEFAULT" transaction-manager="txManager"/>
		</int:poller>
</int-jdbc:inbound-channel-adapter>

In this way we have handle the concurrent condition with a "SELECT FOR UPDATE SKIP LOCKED", but I would like to know if there is a best way to handle this kind of situation?

Kind regards.
Massimo

[Amol Nayak]

Is your downstream idempotent? That is would it be a problem if the same record selected by multiple processes is presented multiple times to it?

[m.ugues]

>>Is your downstream idempotent?
No. I need to handle messages (record on db) once.

>>That is would it be a problem if the same record selected by multiple processes is presented multiple times to it?
It's not what we expect from our application: the goal would be process the records once.

Kind regards.

Massimo

[Amol Nayak]

In that case I feel it has to happen at the database level as you have done it. Because similar to multiple inbound channel adapters (one on each node of the cluster) if there are multiple downstream components for processing them (like say service activators which is local to the inbound adapter, one on each node) then it would not be possible to ensure only one of them processes a given record from database.
Alternatively, these channels of the jdbc inbound adapter needs to feed some outbound adapter (say a JMS outbound adapter) of some common process who is responsible for filtering out the duplicates and pass the selected records downstream where they would be processed. But this unnecessarily complicates i guess.

[Gary Russell]

You could consider having a single instance of the JDBC adapter running and have it distribute the work to other instances over JMS.

If you need automated failover to another instance in the event the current instance dies, there's a demonstration that does that here...

https://github.com/garyrussell/sprin...ration-ha-demo

[m.ugues]

In that case I feel it has to happen at the database level as you have done it. Because similar to multiple inbound channel adapters (one on each node of the cluster) if there are multiple downstream components for processing them (like say service activators which is local to the inbound adapter, one on each node) then it would not be possible to ensure only one of them processes a given record from database.

I was wondering the same thing. The problem is that using Oracle as RDBMS the select for update statement works in a different way from mySql, in such a way that parallel queries does not work as expected.

Alternatively, these channels of the jdbc inbound adapter needs to feed some outbound adapter (say a JMS outbound adapter) of some common process who is responsible for filtering out the duplicates and pass the selected records downstream where they would be processed. But this unnecessarily complicates i guess.

Too complicate. I'm driven by the KISS principle. The only way is to work on the messages producer.

Kind regards
Massimo

[m.ugues]

Thank's. I will thing about it

Kind regards
Massimo