Hibernate Query Timeout Configuration for Low-Traffic Workloads

Answer

For low‑traffic workloads the reliable way to enforce a query timeout is to set Hibernate’s statement‑level timeout property (hibernate.query.timeout in 5.x, jakarta.persistence.query.timeout in 6.x) to the desired number of seconds. This value is applied to every JDBC Statement created by the Session, independent of the connection pool.

Why this works

• Hibernate calls Statement.setQueryTimeout(seconds) on each Statement, so the timeout travels with the query regardless of how the connection was obtained.
• Most pools (HikariCP, Tomcat JDBC, Apache DBCP2) leave the statement timeout at the driver default unless they expose an explicit setting (e.g., Tomcat JDBC’s defaultQueryTimeout). In that case Hibernate’s value wins when the pool does not override it.
• If the pool does configure its own statement timeout, set it to the same value or to 0 (disabled) to let Hibernate control the limit, preventing conflicting limits.
• Pool‑level settings like maxLifetime or idleTimeout may recycle connections, but Hibernate re‑applies the timeout on each new Statement, so no extra reset is needed.

Steps to configure

1. Add the timeout property to your Hibernate configuration:

   # Hibernate 5.x
   hibernate.query.timeout=10
   # Hibernate 6.x (preferred)
   jakarta.persistence.query.timeout=10
   # or keep the legacy name for backward compatibility
   hibernate.query.timeout=10
   

2. Check your connection pool configuration:
   • If using HikariCP, ensure connectionInitSql does not call setQueryTimeout and that there is no custom connectionTimeout that interferes.
   • If using Tomcat JDBC, either leave defaultQueryTimeout at its default (0) or set it to the same value as Hibernate’s property.
   • For Apache DBCP2, verify defaultQueryTimeout is not set unless you want it to match Hibernate.
3. Validate the behavior:
   • Enable Hibernate SQL logging or statistics to see Statement.setQueryTimeout(10) calls.
   • Run a query that exceeds the limit (e.g., SELECT pg_sleep(15) on PostgreSQL) and confirm a QueryTimeoutException is thrown after roughly the configured seconds.

If you observe timeouts occurring earlier than expected, verify whether a JTA transaction manager timeout is active; coordinate or disable it for low‑traffic queries.