Spring Data "TimeoutException" when calling delete documents (with pagination) in Couchbase

Question

Our Spring Boot app is using a Couchbase DB and accessing it using Spring-Data

To delete records from the bucket we created the following method in the repository:

Slice<Dog> deleteAllByOwnerIdAndName(String ownerId, String name, Pageable pageable);

We also have relevant index on the bucket:

CREATE INDEX `dogs_by_ownerId_and_name_idx` ON `dogs`(`ownerId`,`name`) WHERE (`_class` = "com.example.Dog")

Our code is using the pagination when trying to delete the elements:

 Slice<Dog> dogsSlice = null;
 Pageable pageable = PageRequest.of(0, 1000, Sort.by("id"));
 int pageCounter = 0;
 do {
   log.debug("Deleting page No. {} of dogs", pageCounter++);
   dogsSlice = dogsRepository.deleteAllByOwnerIdAndName("3243242", "Max", pageable);
 } while (dogsSlice.hasNext());

But, too many times we get a Timeoutexceptioin:

Deleting page No. 0 of dogs

o.s.s.s.TaskUtils$LoggingErrorHandler : Unexpected error occurred in scheduled task.

org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Unable to execute query due to the following n1ql errors: {"msg":"Timeout 7.5s exceeded","code":1080} at org.springframework.data.couchbase.core.CouchbaseTemplate.findByN1QL(CouchbaseTemplate.java:458) ~[classes!/:5.1.40] at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executeSliced(AbstractN1qlBasedQuery.java:189) ~[classes!/:5.1.40] at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executeDependingOnType(AbstractN1qlBasedQuery.java:129) ~[classes!/:5.1.40] at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.execute(AbstractN1qlBasedQuery.java:106) ~[classes!/:5.1.40] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) ~[classes!/:5.1.40] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.data.couchbase.repository.support.ViewPostProcessor$ViewInterceptor.invoke(ViewPostProcessor.java:87) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[classes!/:5.1.40] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[classes!/:5.1.40] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[classes!/:5.1.40] at com.sun.proxy.$Proxy130.deleteAllByOwnerIdAndName(Unknown Source) ~[na:na]

Is there anything else we need to do?

deniswsrosa deniswsrosa · Accepted Answer · 2018-11-19T08:24:02

You can improve a few things:

1) Change your index to sort by ownerId, then while deleting, sort also by ownerId

CREATE INDEX `dogs_by_ownerId_and_name_idx` ON `dogs`(`ownerId` ASC,`name`) WHERE (`_class` = "com.example.Dog")

As your index is already sorted, couchbase won't spend extra time sorting it during the delete.

2) Do you really need to return all deleted objects? Couchbase has to bring all attributes which are not in your index before sending the documents back to you, and this operation will take some extra time. The best approach is to return only the ids.

@Override
public void updateFamilyName(String familyName, String familyId) {

    String queryString = "Delete from "+getBucketName()+" WHERE "+getClassFilter()+" " +
            " and familyId = '"+familyId+"' RETURNING meta().id";

    N1qlParams params = N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS).adhoc(true);
    ParameterizedN1qlQuery query = N1qlQuery.parameterized(queryString, JsonObject.create(), params);
    checklistRepository.getCouchbaseOperations().getCouchbaseBucket().query(query);
}

private String getBucketName(){
    return checklistRepository.getCouchbaseOperations().getCouchbaseBucket().bucketManager().info().name();
}

private String getClassFilter(){
    return "_class = '" + Checklist.class.getName() + "' ";
}

3) You could also improve on your pagination, but I don't think it will be necessary in your case.

https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

Spring Data "TimeoutException" when calling delete documents (with pagination) in Couchbase

3 Answers