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

Direct answer

Only scroll() — used with a forward-only cursor inside a single transaction at REPEATABLE_READ (or stricter) isolation — can guarantee no missed or duplicate rows under concurrent writes. Offset pagination (setFirstResult/setMaxResults) cannot provide that guarantee at any isolation level, because each page is a separate query against a table whose row positions shift as other transactions insert or delete rows. For a zero-downtime migration where correctness of the copied dataset matters, scroll-based streaming is the right default; offset pagination is acceptable only if you can tolerate re-processing or deduplication downstream.

Why the two behave differently

With offset pagination, page N is computed as "skip N rows, take M." If a concurrent insert lands before the current offset between two page queries, one row shifts into the already-read range (missed) or a row is read twice (duplicate). Isolation level doesn't save you here unless the entire pagination run lives in one snapshot transaction — which offset pagination typically doesn't, since each page is usually its own query/transaction.

With ScrollableResults in ScrollMode.FORWARD_ONLY, the database cursor reads from a single transaction snapshot. Under REPEATABLE_READ (the default on PostgreSQL and MySQL InnoDB for this purpose), rows inserted or deleted by other transactions after your transaction began are simply not visible to the cursor, and rows you have already read are stable. That snapshot property is what delivers the guarantee — not Hibernate itself.

How fetch size fits in

setFetchSize() is purely a performance hint to the JDBC driver controlling how many rows are fetched per round-trip. It has no effect on consistency. Two practical caveats:

• Some drivers ignore the hint or default to buffering the entire result set in memory (notably older MySQL Connector/J behavior, which needs useCursorFetch=true or Integer.MIN_VALUE fetch size for streaming). Consistency is unaffected, but memory behavior changes drastically — verify with a heap profile on a large table.
• A large fetch size reduces round-trips but holds the cursor transaction open longer between fetches; a small one does the opposite. Tune it for latency, not correctness.

Isolation-level interaction

The cursor's consistency comes from the transaction snapshot, so the isolation level is the real decision:

• READ_COMMITTED: each statement may see a new snapshot. A long cursor can observe inserts committed mid-scroll — the guarantee is weakened. Avoid for migration reads.
• REPEATABLE_READ: one snapshot for the whole transaction. This is the practical sweet spot — consistent reads without blocking writers on InnoDB/PostgreSQL MVCC.
• SERIALIZABLE: strongest guarantee, but higher abort/contention rates under write load. Only needed if your migration logic also writes based on what it read.

Operational cautions

Holding a cursor open for a long migration increases transaction duration, can bloat undo/WAL retention, and risks idle-in-transaction timeouts. Mitigate by chunking the migration into keyset-bounded sub-ranges (e.g., scroll within WHERE id > :last windows) so each transaction stays short while still being snapshot-consistent per chunk, with the chunk boundary chosen on an indexed, immutable key.

Benchmarks to collect before deciding

1. Correctness: run the scroll under concurrent insert/delete load and diff the emitted row IDs against a pre-transaction snapshot — expect zero misses/duplicates. Repeat with offset pagination and count anomalies.
2. Latency per page/batch and total migration wall-clock time.
3. Throughput (rows/sec) under a steady concurrent write workload.
4. Heap usage of the migration JVM (validates whether the driver honors fetch size).
5. Database-side: lock wait time, idle-in-transaction duration, cursor count, and undo/WAL growth.

One detail worth confirming before finalizing: your database and Hibernate version. The snapshot behavior above is well established for PostgreSQL and MySQL InnoDB with Hibernate 5/6, but driver-specific streaming behavior (especially MySQL) should be verified on your exact stack with the heap test described above.