How to solve Spring Data Error : org.springframework.dao.QueryTimeoutException

Image Credit

org.springframework.dao.QueryTimeoutException

ERROR c.a.m.i.api.rest.EventController - Unexpected Error

org.springframework.dao.QueryTimeoutException: java.util.concurrent.TimeoutException; nested exception is java.lang.RuntimeException: java.util.concurrent.TimeoutException

        at org.springframework.data.couchbase.core.CouchbaseExceptionTranslator.translateExceptionIfPossible(CouchbaseExceptionTranslator.java:122)

        at org.springframework.data.couchbase.core.CouchbaseTemplate.execute(CouchbaseTemplate.java:539)

        at org.springframework.data.couchbase.core.CouchbaseTemplate.findById(CouchbaseTemplate.java:295)

        at org.springframework.data.couchbase.repository.support.SimpleCouchbaseRepository.findOne(SimpleCouchbaseRepository.java:105)

As per the Spring Reference Document,

"Exception to be thrown on a query timeout. This could have different causes depending on the database API in use but most likely thrown after the database interrupts or stops the processing of a query before it has completed. This exception can be thrown by user code trapping the native database exception or by exception translation."

Root Cause - Spring Data Aspect

This happens when the query execution time exceeds the query timeout value. The relevant Spring Data client application (example: Spring Data Couchbase) will correctly map this type of exception when occurred on database querying. 

Note : Increasing query timeout configuration of the underlying spring data client is not the solution for this unless it is set to really low value. It is always better to use the default value if there is not a specific reason.

Solution

1. Go through the Spring Data client application client references and make  sure they are set to a proper default value. As above example, Couchbase setting is for 75000ms which is more that required, but better keep it as it is.

2. Investigate & isolate the associated query which is causing the query timeout. In the above example it should be a "findOne" query.

3. Create a similar query manually and run in a query runner environment to check whether the query actually consumes an unexpected time period for completion.

4. If the above step shows delays, the issue is in the database side and may be you have to add an index to speed up the query execution.

5. If the above step 3 is executing fast enough, you may have used the above function incorrectly in your application. Example you may executing is in a multiple times in a loop.

Database side optimizations like adding indexes are database specific and should be handled properly as per the documentation.

References

1. https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/dao/QueryTimeoutException.html
2. https://developer.couchbase.com/guides-and-references