Hi Camel riders
I'd like to consume records from a database and pass them to a queue.
Sounds very simple as well as the route would be very simple:
from("sql:select ...?consumer.onConsume=update or delete
...&otherOptions...") .to("activemq:queue:myQueue");
respectively:
from("sql:select ...?consumer.onConsume=update or delete
...&otherOptions...")
.transacted()
.to("activemq:queue:myQueue");
As far as I see, for my simple case, there is no difference between the
transacted and non-transacted route, since the record is anyway "marked" as
processed or failed and there are no things to roll back on failure.
The rather tricky problem is the potential of multiple reads of the same
record. This can happen when I have multiple nodes running the same route
(availability) or in a edge case when the message can be delivered to the
queue but the marking of the record fails.
Therefore my question: how can I deal with this "imprecision"?
Possibilities I see:
1. I could add an idempotent consumer to the route, but this would require
a shared repository when I have multiple nodes. Complex to setup/deploy?
2. Another approach would be to block the database record (select for
update) for other consumers between reading and marking the record. This
would be very simple in terms of infrastructure, but is it possible with
the SQL consumer?
Are there other possibilities?
Thanks
Stephan
