Question
Hibernate pagination and query timeout behavior during large offset retrieval
Ari Cedar
0 reputation · 05 Feb 2020, 05:11 UTC
148.6K views1
Loading…
0 reputation · 05 Feb 2020, 05:11 UTC
When implementing pagination for large datasets in Hibernate ORM, the combination of setFirstResult() and setMaxResults() is used to manage result windows. However, as the offset increases, the underlying database may experience significant latency due to the scanning of skipped rows.
There is a need to ensure that long-running pagination queries are handled gracefully to prevent resource exhaustion. While query timeouts can be configured via setHints() or global properties, the interaction between the JDBC driver's Statement.cancel() mechanism and Hibernate's pagination execution is not always uniform across different database dialects.
What is the expected behavior of a Hibernate query timeout when a high offset causes a database-level scan? Does the QueryTimeoutException trigger consistently across different JDBC drivers during the offset phase of the execution?
34775 reputation · 05 Feb 2020, 09:11 UTC
When the timeout expires, Hibernate relies on the JDBC driver’s Statement.cancel() to abort the server‑side execution. If the driver honors the cancel while the database is still scanning the OFFSET rows, a QueryTimeoutException is thrown promptly. If the driver only checks for cancellation after it starts returning rows, the expensive OFFSET scan may finish before the exception is raised, delaying the timeout.
Older driver versions or certain proprietary drivers may only poll for cancellation after the result set has been materialized on the server. In that case the server continues to skip the OFFSET rows, consuming CPU and I/O, and the client only receives the timeout exception after the scan finishes, which can be much later than the configured timeout.
hibernate
scalingospring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.generate_statistics=true
If your driver shows delayed cancellation, consider switching to keyset pagination (seek method) or enable driver‑specific network timeouts (socketTimeout, queryTimeout) as a safety net.
Please provide the exact JDBC driver versions you are using so we can confirm whether an upgrade would resolve the delay.
Use comments to ask for clarification. Post a solution as an answer.
5,940 reputation · 05 Feb 2020, 11:39 UTC
Hibernate propagates the javax.persistence.query.timeout hint as a plain java.sql.Statement.setQueryTimeout() call; the timeout measures the total elapsed time of the statement, not just the fetch phase. When the limit expires, the driver issues a cancel request, but many databases only honor that request after they have finished evaluating the OFFSET clause (whether rendered as LIMIT/OFFSET, FETCH … OFFSET, or a window‑function subquery). Consequently, a high‑offset query can continue consuming CPU and I/O until the skip operation completes, after which the QueryTimeoutException is thrown. This behavior is independent of the Hibernate API used (Criteria, HQL, or native SQL) and is dictated solely by the JDBC driver and database engine.