Concurrncy of JDBC inbound adaptors

[mjafari]

My problem was delaying some messages according to a header value that determine the time message should be sent.
My solution was:

1. a router that send delayed messages to D-ch channel and others to I-ch
2. a jdbc-outbound-adapter that persists messages from D-ch
3. a jdbc-inbound-adapter that by a query catches persisted messages that it is time to be sent and send them to I-ch

I run concurrently multiple instance of program for scalablity affairs but there is no guaranty that same messages don't be catch by multiple instances.

Questions:

1. How can I solve this concurrency problem about jdbc-inbound-adapter?
2. What if I use a delayer with a jdbc message store as an alternative solution?

[xuqingyang]

The jdbc message store can't handle the concurrency problem too. I have to rewrite some classes to solve the problem. Basicly I think spring integration is not ready for cocurrency in database stuff.

[oleg.zhurakousky]

Could you please explain your concurrency problem in detail? Are you saying that multiple instances of the same message are being generated by the jdbc:inbound-channel-adapter?
Could you also provide configuration sample from which it will be clear as to what you are doing?

[Mark Fisher]

I'd recommend just adding an update statement on the inbound-channel-adapter to set a status field if possible (e.g. status=1), then in the query include status=0 (or whatever you decide to use) in the select statement. Other than that *concurrency* is then at the level of DB transactions. I'd be interested to know more about either set of use-cases though to understand what needed to be extended and why a delay is needed.

Thanks,
Mark

[mjafari]

Could you please explain your concurrency problem in detail? Are you saying that multiple instances of the same message are being generated by the jdbc:inbound-channel-adapter?
Could you also provide configuration sample from which it will be clear as to what you are doing?

I run multiple instance of single SI program that has an inbound jdbc adapter.
Concurrency problem is that when an instance do select and before it process all records in Resultset and then run update query, another instance do select, so there will be common records in Resultset s of two instances. That is repeated messages.

[mjafari]

I'd recommend just adding an update statement on the inbound-channel-adapter to set a status field if possible (e.g. status=1), then in the query include status=0 (or whatever you decide to use) in the select statement.

If you refer to jdbc:update section of jdbc inbound adapter, this exists now.

Other than that *concurrency* is then at the level of DB transactions.

Is in this DB transaction include update phase? In other words, does this start before execution of select query and end after execution of update for all selected records?

[mjafari]

I'd be interested to know more about either set of use-cases though to understand what needed to be extended and why a delay is needed.

Thanks,
Mark

Use-case description:
We send sms notifications on different events to customer.This notification should not be sent in unsuitable times (e.g. Midnight is not suitable time for sending Happy Birthday message). So every notification is send instantly when event occurs but in unsuitable times notification messages should be delayed until first suitable time (e.g. 8:00 a.m).

Non-functional requirement:

• Number of delayed notification that should be sent in a same first suitable time, can be in scale of 5 million or more.
• Multiple instance of program run concurrently for increasing performance and throughput.
• Delay mechanism should work properly when program process restarts.

[mjafari]

Finally I used such a solution:

    <jdbc:inbound-channel-adapter channel="batchDueNotificationsChannel"
            data-source="dataSource"                                 
            select-sql-parameter-source="currentTimeMillisSqlParameterSource">
        <jdbc:query>
            select id, ... from Notification
            where delayUntil &lt; :CURRENT_TIME_MILLIS 
        </jdbc:query>
        <jdbc:update>
            delete Notification where id in (:id)
        </jdbc:update>
        <integration:poller fixed-delay="1000">
            <integration:transactional isolation="SERIALIZABLE" 
                            transaction-manager="delayTransactionManager"/>
        </integration:poller>
    </jdbc:inbound-channel-adapter>

Every time that multiple program instance retrived same rows from DB, all of them except one will have an errorMesssage with payload type SQLException (for oracle ORA-8177) that should be filtered from real errorMessages logging.

Haven't every body a better solution according to described requirements?