Hi all, Following up from Adam's review on PR #6078 — moving the design discussion here as suggested.
PR #6078 (FINERACT-2672) isolates standing-instruction execution so a single failing instruction can no longer mark the whole run's transaction rollback-only and revert successful transfers. It does this by executing each instruction in its own REQUIRES_NEW transaction and recording failures in separate committed transactions. - JIRA: https://issues.apache.org/jira/browse/FINERACT-2672 - PR: https://github.com/apache/fineract/pull/6078 On the review, Adam raised a broader set of scalability improvements. I'd suggest the isolation fix in #6078 can land on its own correctness merits, with the scalability work tracked as a separate ticket and designed here — but I'm happy to keep them together if folks prefer. Current state: the EXECUTE_STANDING_INSTRUCTIONS job is a single Spring Batch tasklet that loads all due instructions into memory in one SELECT, then loops. Due-ness is computed per row at runtime from last_run_date + recurrence; there is no persisted next-run date. Redesign direction (roughly in dependency order): - Persisted next_run_date on the instruction, so the fetch becomes an indexed WHERE next_run_date <=:businessDate. This is the enabler for everything below. - Paginated/keyset fetch in chunks (~100) instead of loading the full due set. - Chunk-oriented, fault-tolerant step: attempt a chunk in one transaction; on failure, fall back to per-item transactions (Spring Batch skip/scan semantics). Open question: for SIs, insufficient-funds failures are common, so the per-item fallback may fire frequently — worth validating the happy-path saving isn't eaten by fallback re-execution. - Partitioned parallelism grouped by account (from/to) so same-account instructions run sequentially within a partition, avoiding lock contention/deadlocks. - Scoped retry (Retry4j or Spring Batch retry): only for transient failures (deadlock, lock timeout, service-unavailable) — explicitly not for business failures like insufficient balance or validation errors. - Longer term: whether to move to Spring Batch remote-partitioning/worker steps for the parallelization and retry rather than hand-rolling it. Questions for the list: 1. Is a persisted next_run_date an acceptable schema addition, and how should it interact with backdated / valid_from changes? 2. For parallelism, is account-based partitioning sufficient, or are there SI relationships (e.g., chained transfers) that need stricter ordering? 3. Appetite for the Spring Batch remote-worker model vs. a simpler local partitioned step as the first iteration? Happy to write this up as a design doc/wiki page if that's the preferred format. Kind Regards, Farooq
