Spring Integration stored-proc-inbound-channel-adapter

Question

I have the below requirement.

1.One Spring based application runs on one Unix box which creates entries on a table.1 column called created_on which hold time when it got created.

2.Another spring integration based application which has 30 instances are running on 3 different Unix box with 10 instances on each box(10 different PID). All these application has configured with stored-proc-inbound-channel-adapter which pulls data for 1 sec interval from above table.

The table has one status column which marks to Y once the record has been picked by the store procedure.

Store procedure picks record based on created on and not marked Y.

I don't want used external queues to communicate between the 2 applications.

Doubts:- 1.Is there any chance of getting same record has picked by multiple nodes?

2. Is there going to be any performance issue when 30 nodes try to access same table records threw a same store procedure?

Answer 1

1.Is there any chance of getting same record has picked by multiple nodes?

I don't know how to help you from here, but you shouldn't have any problems if all the logic is encapsulated in that stored proc: no one else is going to pick up the same records as long as the update and select logic there is right.

2.Is it possible to return the result based on a specific no?

Fully unclear what is no in your question, but stored procedures indeed can return results - raw sets or plain OUT params. That's already fully out of scope this thread and Spring Integration responsibility.

3.How to pass the node name to the spring context(stored-proc-inbound-channel-adapter)?

Please, study what is Environment in Spring Framework and its related @Value and properties placeholders: https://docs.spring.io/spring/docs/current/spring-framework-reference/core.html#beans-environment. So, you indeed can propagate some property on the matter into the stored proc param.

4. Is there going to be any performance issue when 30 nodes try to access same table records threw a same store procedure?

No, that's not. As far as your RDBMS vendor support concurrent access to the data base.

5.How to deal stored-proc-inbound-channel-adapter when no data has been return by the store procedure?

You can apply something like AbstractMessageSourceAdvice into the poller of the stored-proc-inbound-channel-adapter and check the result in the implementation of:

/**
 * Subclasses can take actions based on the result of the poll; e.g.
 * adjust the {@code trigger}. The message can also be replaced with a new one.
 * @param result the received message.
 * @param source the message source.
 * @return a message to continue to process the result, null to discard whatever the poll returned.
 */
Message<?> afterReceive(Message<?> result, MessageSource<?> source);