Hibernate scroll() vs offset pagination: inconsistent row visibility during zero‑downtime migration

Migrating a small Java application with zero downtime requires a pagination method that preserves data consistency while keeping latency low.

Fetch‑size based pagination reduces memory overhead but demands careful transaction boundary management; offset pagination via maxResults/setFirstResult degrades on large tables and can miss or duplicate rows if concurrent inserts occur, and some JDBC drivers ignore the fetch‑size hint. Hibernate 5 improves scroll() for large result sets, while Hibernate 6 may alter cache interaction semantics.

The unresolved decision is whether iterator‑based scroll() or limit‑offset queries give a better trade‑off for this migration scenario.

Which strategy guarantees no missed or duplicate rows under concurrent writes? How does fetch‑size interact with the chosen transaction isolation level? What benchmark metrics should be collected to decide?