Transaction and Query timeout

Performance is one of the non-functional requirements of an application. It defines the acceptable maximum response time for activities such as page load. It can also include the acceptable performance criteria for other activities which are application specific.

Performance requirements should be handled upfront while developing an application rather than optimizing application after the user starts complaining about slowness.

While designing an application the following two things should be considered to meet the acceptable performance criteria:

1. The maximum time taken by a query to fetch database records during peak load
2. The maximum time taken to execute a transaction during peak load

Often a series of queries or transactions are called to perform an operation such as a page load. For such an operation, the collective maximum response time should be considered.

Once the maximum time permitted for a query or transaction is defined, we can always take measures to ensure that minimum number of timeouts elapse during peak load by increasing the server memory or heap size or code optimization.

In this blog, I’m writing about defining transaction and query timeout for Spring-Hibernate applications.

Transaction timeout

Transactions are handled in the Service layer in a Spring application. The timeout attribute of @Transactional can be used to define the timeout value in seconds. If this value is not specified it defaults to the default value of the underlying transaction mechanism.

The default timeout can be specified for the application by setting the defaultTimeout property as shown below:

<bean id="transactionManager"
class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory"
		ref="entityManagerFactory" />
<property name="defaultTimeout" value="30" />
</bean>

Query timeout

JPA provides javax.persistence.query.timeout hint to specify query timeout in milliseconds. This hint can be specified in @NamedQuery or @NamedNativeQuery. It can also be specified on the Query or TypedQuery by calling the setHint method. The query timeout defined by the hint can be overridden by the setHint method.

The default query timeout can be specified for an application in persistence.xml as shown below:

<persistence-unit name="PU" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
    	...
<property name="javax.persistence.query.timeout"
		value="30" />
     </properties>
 </persistence-unit>

Some persistence providers or databases may not support this hint.