Query timeout with JPA and Hibernate

(Last Updated On: July 10, 2019)
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see what is the best way to set up the query timeout interval with JPA and Hibernate.

Setting the query timeout allows you to cancel slow-running queries that would, otherwise, put pressure on database resources.

The “javax.persistence.query.timeout” JPA query hint

As I explained in this article, the Java Persistence API defines the notion of query hint, which unlike what its name might suggest, it has nothing to do with database query hints. The JPA query hint is a Java Persistence provider customization option.

JPA provides support for setting a timeout interval on a given entity or SQL query via the javax.persistence.query.timeout query hint:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("javax.persistence.query.timeout", 50)
.getResultList();

The timeout value is defined in milliseconds, so the JPQL query above will time out after 50 milliseconds unless the result set is being fetched prior to the timeout threshold.

The “org.hibernate.timeout” Hibernate query hint

Hibernate also provides the org.hibernate.timeout query hint, which unlike its JPA counterpart, takes the timeout interval in seconds:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("org.hibernate.timeout", 1)
.getResultList();

The Hibernate Query timeout property

If you unwrap the JPA javax.persistence.Query to the Hibernate-specific org.hibernate.query.Query interface that extends the JPA query specification, you can get access to the Hibernate query extension methods which allow you to set a SQL-level comment, hint or provide a timeout threshold.

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.unwrap(org.hibernate.query.Query.class)
.setTimeout(1)
.getResultList();

Just like it was the case with the org.hibernate.timeout query hint, the setTimeout method takes the timeout interval in seconds, so the JPQL query above will time out after one second unless the query finishes faster.

Testing time

To see how the query timeout works, consider the following example:

List<Tuple> result = entityManager
.createNativeQuery(
    "SELECT 1 " +
    "FROM pg_sleep(2) ", Tuple.class)
.setHint(
    "javax.persistence.query.timeout", 
    (int) TimeUnit.SECONDS.toMillis(1)
)
.getResultList();

When running the PostgreSQL query above, the database is going to throw a query_canceled exception:

SELECT 1 
FROM pg_sleep(2)

-- SQL Error: 0, SQLState: 57014
-- ERROR: canceling statement due to user request

Auto-applying the timeout interval to Hibernate queries

If you want to apply the query timeout automatically to all Hibernate queries, then you should pass the JPA javax.persistence.query.timeout query hint as a property:

<property
    name="javax.persistence.query.timeout"
    value="1000"
/>

Note that the global query hints are only applied to entity queries (e.g. JPQL and Criteria API) only. They are not applied for native SQL queries, at least until HHH-13493 gets fixed.

And, then you execute the following JPQL query:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where function('1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(2) ) --',) is ''", Post.class)
.getResultList();

Then Hibernate is going to throw the query timeout exception even if we didn’t explicitly specify the timeout interval on the JPQL query:

SELECT p.id AS id1_0_,
       p.title AS title2_0_
FROM post p
WHERE 1 >= ALL (
    SELECT 1
    FROM pg_locks, pg_sleep(2)
) --()=''

-- SQL Error: 0, SQLState: 57014
-- ERROR: canceling statement due to user request

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Setting the query timeout interval is very useful, as otherwise, slow running queries will keep the database connection acquired over long period of times, therefore, putting pressure on concurrency and scalability.

Download free ebook sample

Newsletter logo
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence,
  • a 10% discount coupon for my book.

4 Comments on “Query timeout with JPA and Hibernate

  1. For use of programmatically setting the default timeout for all queries, is the string “javax.persistence.query.timeout” available anywhere in the Hibernate JAR, or can it be added to avoid users having to have the string hard-coded?
    A lot of property strings are already available in org.hibernate.cfg.AvailableSettings
    For example with and without hard-coding using the AvailableSettings interface:

    var properties = Map.ofEntries(
    entry(SHOW_SQL, false),
    entry(“javax.persistence.query.timeout”, 5000)
    );

    Happy to raise a HHH ticket to get it added or create a pull request adding it to the AvailableSettings interface unless there’s a good reason it’s not already in there?

    p.s. anyway to get this Envers ticket bumped up in priority? HHH-13473
    https://hibernate.atlassian.net/browse/HHH-13473

    p.p.s. good job on the book.

    • The query hints are already available as constants in the QueryHints class. So, you don’t have to hard code the value. The AvailableSettings class is for exclusive global configuration, hence the distinction.

  2. Hi Vlad,

    For queries the way to set query timeouts is quite straightforward. More challenging IMHO is how to set query timeouts for persist, merge, delete and update operations without any explicit query – so all operations which, in the end, will send statements to the database.

    Is there an easy solution for that as well?

    Cheers,
    Wolfgang

    • You have a statement timeout setting in the DB for that as well as a lock timeout which is typical for modifying statements.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want to run your data access layer at warp speed?