Jakarta Persistence Query Timeout does not work for Spring Data JPA findBy and findAll methods on MySQL and Oracle DBs

[limkl-psa]

Hi Spring Data JPA Team,

Problem Statement I am trying to set spring.jpa.properties.jakarta.persistence.query.timeout in application.properties, so that JPA Repository findBy Field/All [findByName] methods would throw a QueryTimeoutException when the queries take too long. However, the query timeout does not work for them. As a result, the application thread would hang indefinitely until the long query goes through.

Setup I am using:

1. SpringBoot 3.2.4
2. Hibernate 6.4.4 (pulled in by spring-boot-starter-data-jpa:3.2.4)
3. Java 17.0.11
4. MySQL 8 DB
5. Oracle 19.23 DB

Non-Working Scenario

application.properties

# Query Timeout (must be set in multiples of 1000ms as JDBC specs require it in secs)
spring.jpa.properties.jakarta.persistence.query.timeout=2000

EmployeeService.java

@Service
public class EmployeeService {

    private final EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public Employee[] getAllEmployees()
    {
        Employee[] employees = employeeRepository.findAll().toArray(new Employee[0]);

        return employees;
    }

The long query was simulated by locking the table.

LOCK TABLES EMPLOYEE WRITE;

Working Scenarios

These scenarios are working though, throwing a jakarta.persistence.QueryTimeoutException:

1. [Hibernate ORM on MySQL] Calling Hibernate's CriteriaQuery with an query hint for jakarta.persistence.query.timeout=2000 (must be units of 1000ms)

    // This method is used to test the query timeout exception for MySQL. 
    // Before running this test, please first issue a lock table command as follows:
    // mysql> LOCK TABLES employee WRITE;
    @Test
    public void criteriaQueryTimeoutShouldThrowQueryTimeoutException() throws Exception {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
        Root<Employee> root = cq.from(Employee.class);
        cq.select(root);
        TypedQuery<Employee> query = em.createQuery(cq);
        query.setHint("jakarta.persistence.query.timeout", 2000); // Very low timeout to force the exception
        assertThrows(QueryTimeoutException.class, query::getResultList);
    }

2. [Hibernate ORM on Oracle] Calling Hibernate's NativeQuery with an query hint for jakarta.persistence.query.timeout=2000 (must be units of 1000ms)

    // This method is used to test the query timeout exception for Oracle DB
    // by issuing a sleep command as follows:
    // BEGIN DBMS_LOCK.SLEEP(30); END;
    @Test
    public void nativeQueryTimeoutShouldThrowQueryTimeoutException() throws Exception {
        Query query = em.createNativeQuery("BEGIN DBMS_LOCK.SLEEP(30); END;");
        query.setHint("jakarta.persistence.query.timeout", 1000); // Very low timeout to force the exception
        assertThrows(QueryTimeoutException.class, query::getResultList);
    }

3. [Spring Data JPA Custom Queries on MySQL] Calling an explicitly defined @query method without @QueryHint.

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query(value = "select e from Employee e")
    List<Employee> findAllEmployees();
}

4. [Spring Data JPA Custom Queries on Oracle] Calling an explicitly defined native query method without @QueryHint.

package test;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import jakarta.persistence.QueryTimeoutException;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class CustomRepository
{
    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public void simulateQueryTimeout() {
        Query query = entityManager.createNativeQuery("BEGIN DBMS_LOCK.SLEEP(30); END;");

        try {
            query.getResultList();
        } catch (QueryTimeoutException e) {
            // Handle the timeout exception
            System.out.println("Query timeout occurred: " + e.getMessage());
        }
    }
}

For Working Scenarios #1 and #2, it is clear that Hibernate ORM, for both Criteria and Native Queries, is able to pass the value of "jakarta.persistence.query.timeout" to MySQL and Oracle JDBC Drivers to enforce the query timeout.

For Working Scenarios #3 and #4, it is clear that Spring Data JPA, for custom queries, is able to pass the value of "spring.data.jpa.jakarta.persistence.query.timeout=2000" to Hibernate ORM and then to MySQL and Oracle JDBC Drivers to enforce the query timeout.

It seems that Spring Data JPA is only unable to do so for the inferred findByField or findAll methods.

Would greatly appreciate any help/advice on this.

Thanks!

[limkl-psa]

Posted the test files here src.zip

[christophstrobl]

From a first look this pretty much sounds like a reincarnation of HHH-16062. Similar to the original report, HHH-18336 has been opened recently.

[limkl-psa]

Hi Christoph,

Both HHH issues were opened by me.

HHH-16062 was due to me setting query timeout to 1ms which is too low for JDBC drivers using secs. Specifying at least 1000ms resolved the issue.

HHH-18336 explores the differences in Hibernate ORM API behavior between DBMS_LOCK and long queries with large datasets.

For #3532, perhaps we could focus on whether Spring Data JPA is passing down spring.jpa.properties.jakarta.persistence.query.timeout=2000 to Hibernate and DB Drivers for the findBy/findAll methods. Custom JPA Repository methods are working, based on verification using LOCK TABLES (MySQL) and DBMS_LOCK (Oracle).

On the other hand, if this configuration is not supported for findBy/findAll and requires custom JPA Repository methods, just let me know! Thanks!

[christophstrobl]

I see - thanks for opening those!

data-jpa does recognize jakarta.persistence.QueryHints and passes them on. The property however is part of JpaProperties used by Spring Boot, and is passed on to the EntityManagerFactory via LocalContainerEntityManagerFactoryBean.

So, I do not see data-jpa involvement here right away - will check the sample you provided. Maybe we're missing something here.

[odrotbohm]

I think Christoph is right. Query timeouts need to be declared on each query individually, which means that you would have to explicitly declare them on the (re)declared repository methods via @QueryHint.

[limkl-psa]

Hi Oliver, thanks for your advice. I've asked my engineering teams to rely on explicitly declared queries for now.

Interestingly, I'm able to replicate that both Hibernate 6.4.4 and EclipseLink 4.0.0 for both MySQL/Oracle DBs, do not throw QueryTimeoutException/PersistenceException for long queries exceeding the query timeout value (unlike for locked table queries).

HHH-18336 EclipseLink-2204

This could explain why there is no query timeout for the Spring Data JPA findBy and findAll methods.

[limkl-psa]

Update: There is no bug in Spring Data JPA / Hibernate / JDBC Drivers.

In the failing test, the JDBC statement was executed within the JDBC query timeout. The rest of the time was spent by Hibernate marshalling the ResultSet into JPA Entities, thus giving the false impression that the JDBC query timeout did not kick in.

[christophstrobl]

@limkl-psa thank you for the update!