JPA query timeout parameters ignored but @Transaction annotation works

Question

I want JPA queries made by my Spring Boot application to a Postgres database to timeout after 5 seconds.

I have created this 20 seconds query to test timeouts:

@Query(value = "select count(*) from pg_sleep(20)", nativeQuery = true)
int slowQuery();

I've set the following properties in application.config:

spring.jpa.properties.javax.persistence.query.timeout=3000
javax.persistence.query.timeout=5000

But the query does not timeout after 3s or 5s (it still takes 20s to execute).

Strangely, if I annotate slowQuery with @Transactional(timeout = 10), it times out after 10s or so.

I would prefer not to annotate every query. I'm using JPA 2.0 and the Tomcat connection pool.

What magic is required to make the timeout work just by setting them in application properties file?

Answer 1

To make the timeout generic, in your JpaConfiguration, when you declare the PlatformTransactionManager Bean, you can set the default timeout of the transactions:

@Bean
public PlatformTransactionManager transactionManager() throws Exception {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    txManager.setDataSource(this.dataSource);
    txManager.setDefaultTimeout(10); //Put 10 seconds timeout
    return txManager;
}

PlatformTransactionManager inherits AbstractPlatformTransactionManager which contains that method:

    /**
     * Specify the default timeout that this transaction manager should apply
     * if there is no timeout specified at the transaction level, in seconds.
     * <p>Default is the underlying transaction infrastructure's default timeout,
     * e.g. typically 30 seconds in case of a JTA provider, indicated by the
     * {@code TransactionDefinition.TIMEOUT_DEFAULT} value.
     * @see org.springframework.transaction.TransactionDefinition#TIMEOUT_DEFAULT
     */
    public final void setDefaultTimeout(int defaultTimeout) {
        if (defaultTimeout < TransactionDefinition.TIMEOUT_DEFAULT) {
            throw new InvalidTimeoutException("Invalid default timeout", defaultTimeout);
        }
        this.defaultTimeout = defaultTimeout;
    }