Query timeout with JPA and Hibernate

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.

Seize the deal! 50% discount. Seize the deal! 50% discount.

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.

FREE EBOOK

4 Comments on “Query timeout with JPA and Hibernate

    • Most likely a bug. You are better off fixing it and sending a Pull Request.

  1. 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.