JDBC dead-letter claimable sequence paging appears inconsistent with JPA implementation

[doubleflygofight]

Summary

The JDBC dead-letter queue implementation appears to page claimable sequence heads differently from the JPA implementation, in a way that may skip or distort candidate coverage beyond the first page.

JDBC behavior observed

JdbcSequencedDeadLetterQueue.process(predicate, processingTask) calls findClaimableSequences(10).

PagingJdbcIterable advances pages using page * pageSize, and DefaultDeadLetterStatementFactory.claimableSequencesStatement(...) uses that value as:

sequenceIndex >= ?

while also ordering by:

ORDER BY lastTouched ASC

The resulting SQL shape is effectively:

SELECT *
FROM DeadLetterEntry dl
WHERE dl.processingGroup = ?
  AND dl.sequenceIndex >= ?
  AND dl.sequenceIndex = (
      SELECT MIN(dl2.sequenceIndex)
      FROM DeadLetterEntry dl2
      WHERE dl2.processingGroup = dl.processingGroup
        AND dl2.sequenceIdentifier = dl.sequenceIdentifier
  )
  AND (
      dl.processingStarted IS NULL
      OR dl.processingStarted < ?
  )
ORDER BY dl.lastTouched ASC
LIMIT ?

Why this looks problematic

sequenceIndex is sequence-local, not globally monotonic across a processing group.

That means paging with sequenceIndex >= offset does not appear consistent with a result set ordered by lastTouched ASC.

For example:

• page 1 uses sequenceIndex >= 0
• page 2 uses sequenceIndex >= 10

But page 2 is then not "the next 10 oldest sequence heads by lastTouched".
Instead, it filters out any sequence head whose minimum sequenceIndex is below 10, even if that head was never part of page 1.

Comparison with JPA

The JPA implementation appears to page the ordered result set directly:

select dl
from DeadLetterEntry dl
where dl.processingGroup = :processingGroup
  and dl.sequenceIndex = (
      select min(dl2.sequenceIndex)
      from DeadLetterEntry dl2
      where dl2.processingGroup = dl.processingGroup
        and dl2.sequenceIdentifier = dl.sequenceIdentifier
  )
  and (dl.processingStarted is null or dl.processingStarted < :processingStartedLimit)
order by dl.lastTouched asc

This matches the expected semantics much more closely.

Potential risk

This may:

• distort coverage beyond the first page,
• skip valid sequence heads in later pages,
• or make predicate-based processing fairness/throughput worse than expected.

This becomes especially visible when a custom predicate is used and the first page contains many non-processable sequence heads.

Question

Is this intentional in the JDBC implementation, or should JDBC paging be aligned with the JPA behavior for claimable sequence selection?

[smcvb]

First and foremost, thanks for filing this concern with us, @doubleflygofight! Contributions are always much appreciated, in any form they may come.
Now, to your exact question:

Is this intentional in the JDBC implementation, or should JDBC paging be aligned with the JPA behavior for claimable sequence selection?

No, definitely intentional. Purely coincidence.

Just for my clarity, though, you're most likely talking about Axon Framework 4 at the moment, right?
Knowing the exact version you're on when filing this concern is helpful to deduce where a chance, if any, should be targeted.
Thanks! 🙏

Lastly, on the gravity of this concern. It is indeed inconsistent, but I wouldn't expect anybody's production environment to break because of this.
Does that mean we do not adjust this? No, of course we should do something about this.
Knowing the gravity of a concern is just helpful to clearly prioritize this effort among other tasks the team has.

On that note: would you be up to provide a pull request to fix this concern for us, @doubleflygofight?
Assuming you're dealing with an AF4 concern, of course!

As it stands, the sequenced DLQ support moved to Axoniq Framework, which follows a different licensing.
Hence my request for the version you spotted this in, by the way! 🙏

[doubleflygofight]

Yes, this is on Axon Framework 4.10.0.

We observed it in the JDBC dead-letter queue path, where PagingJdbcIterable advances by page offset, but claimableSequencesStatement(...) applies that offset as sequenceIndex >= ? while still ordering by lastTouched ASC.

From our side, this is not just a theoretical inconsistency. We believe this has already contributed to a real production retry-delay / starvation issue.

The practical impact is not data corruption or a full production outage, but degraded retry fairness / coverage:

• later pages are not guaranteed to be the next N sequence heads by lastTouched
• eligible sequence heads can be skipped from later pages if their minimum sequenceIndex is below the page offset
• this becomes more visible when a custom predicate filters out many heads from the first page
• in practice, that can delay or starve retries for DLQ entries that are otherwise eligible to run sooner

Given your reply, I understand the JDBC/JPA difference is not intentional. From our side, the remaining question is whether the AF 4.10.0 JDBC implementation should be corrected to align with the JPA paging semantics, given the production retry-delay impact we’ve observed. If that direction is confirmed, then yes, I can look into providing a PR for the AF4 path.

[doubleflygofight]

Opened PR #4551 to address this on the AF 4.10.x line:
#4551

[smcvb]

Thanks for the clarity and effort you've put into this, @doubleflygofight. Problematic to hear it caused issues in your environment; that was definitely unintended from Axon Framework's side.

Now on to the pull request you've constructed.
The chances look fine, but I need to point out that we will not do any patch releases for Axon Framework 4.10, 4.11, and 4.12 anymore.
As a matter of fact, we'll stop doing public fixes for Axon Framework 4 soon (as mentioned in our 15 years blog).

Given you're still filing this before our EOL, we will make an exception for the fix you've given.
However, I do need to ask you to make a PR for axon-4.13.x.
Feel free to either adjust #4551 accordingly, or close #4551 and open a new PR branched from axon-4.13.x.