Spring JPA deleteInBatch causes StackOverflow

Question

I have a question to deleting Items from db using deleteInBatch. I have an object A having a list of Objects B like:

class A {
private List <B>;
}

The list contains more than 7k elements. So now I have to remove A and all its elements. I tried via deleteInBatch but I get

org.springframework.web.util.NestedServletException: Handler processing failed;
nested exception is java.lang.StackOverflowError

Deleting the items with a sipmle delete method works but it takes more than 5 minutes. My delete code is:

public void delete(Long id) {
A a = repository.findOne(id);
deleteElements(a);
repository.delete(a);
}

private void deleteElements(A a) {
repository.deleteInBatch(a.getListOfB);
}

Is there a good solution to speed up the deleting process or how to change so that deleteinbatch does not take all hibernate stack - without increasing it?

The complete Stacktrace:

org.springframework.web.util.NestedServletException: Handler processing failed; nested exception is java.lang.StackOverflowError
org.springframework.web.servlet.DispatcherServlet.triggerAfterCompletionWithError(DispatcherServlet.java:1259)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:163)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:206)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:179)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)

And so on...

root cause

java.lang.StackOverflowError
org.hibernate.hql.internal.ast.tree.SqlNode.<init>(SqlNode.java:34)
sun.reflect.GeneratedConstructorAccessor36.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:526)
java.lang.Class.newInstance(Class.java:379)
org.hibernate.hql.internal.ast.SqlASTFactory.create(SqlASTFactory.java:256)
antlr.ASTFactory.create(ASTFactory.java:153)
antlr.ASTFactory.create(ASTFactory.java:186)
org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2018)
org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2026)
org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2026)

And so on....

could you post the complete stacktrace? – kucing_terbang Apr 15 '15 at 08:23 — kucing_terbang, Apr 15 '15 at 08:23

score 13 · Accepted Answer · answered Apr 15 '15 at 09:37

13

in your case, the delete query will be translated by JpaRepository to be looks like this.

delete from [table_name] where [criteria] = id or [criteria] = id (and so on...)

the jvm throws a stack overflow error because the HqlSqlBaseWalker is trying to search all the or (or basically the where criteria) statement

I guess, in your case, you could try to generate your own delete query and then execute it or you could try to split the data into few lists.

answered Apr 15 '15 at 09:37

kucing_terbang

4,991
2
22
28

So, you mean that "batch" doesn't really create a JDBC batch statement internally? That's rather bad news for any DBA who's tuning cursor caches... – Lukas Eder Apr 16 '15 at 15:57
1

@LukasEder well, I think the problem lies on the "batch" term itself in here. I mean, what we usually expect on this case is that hibernate will split the data and try to insert those data like once query for every 100 of data. but, if you read at the api doc, it does mention that the function will indeed create a single `query`. so, i think the lesson in here is we should think twice to name our public api ;) – kucing_terbang Apr 17 '15 at 03:58
Yes, I created my own delete query. The process took 3 min, but I created an index on the key and it works good. Thx :) – kamil.rak Apr 20 '15 at 06:26

score 1 · Answer 2 · answered Oct 17 '19 at 07:22

I ended up combining deleteInBatch with an answer to another question:

import com.google.common.collect.Lists;
...
Lists.partition(messages, 500)
        .forEach(fragmentMessageDao::deleteInBatch);

For me, this gave a proper and elegant solution because I already needed the data for another calculation

score 0 · Answer 3 · answered Jun 10 '15 at 17:22

0

I got the same issue (java.lang.StackOverflowError) and created a method with my own delete query, e.g. deleteByIdAndYear(...), to avoid it.

answered Jun 10 '15 at 17:22

user3325137William

13
3

score 0 · Answer 4 · answered Oct 29 '19 at 15:19

0

In my case deleting entities in smaller chunks helped. Chunks of 100 items worked just fine.

answered Oct 29 '19 at 15:19

ITisha

902
2
12
14

Spring JPA deleteInBatch causes StackOverflow

4 Answers4