2

I have a very specific scenario that, during the execution of a query, specifically during the fetching rows from db to my resultset, I get an OutOfMemoryError.

The code is simple as it:

public interface MyRepository extends Repository<MyEntity, Long> {

    @EntityGraph(value = "MyBigEntityGraphToFetchAllCollections", type = EntityGraphType.FETCH)
    @QueryHints({@QueryHint(name = "org.hibernate.readOnly", value = "true")})
    MyEntity getOneById(Long id);

}

public class MyService {

    ...

    public void someMethodCalledInLoop(Long id) {

       try{
           return repository.getOneById(id);
       } catch (OutOfMemoryError error) {
          // Here the connection is closed. How to reset Hikaricp?
          System.gc();
          return null;
       }

    }

}

Seems weird a getOne consumes all the memory, but due to eager fetching about 80 collections and due to multiplication of rows, some cases are insupportable.

I know I have the option to lazely load the collections, but I don't want to. Hit database 1+N times on every load consumes more time and my application dont have it. Its a batch processing of milions of records and less than 0,001% has this impact in memory. So my strategy is just discard this few records and process the next ones.

Just after catch the OutOfMemoryError the memory is freed, the trouble entity turns garbage. But due to this Error, HikariCP closes (or is forced to) the connection.

In the next call of the method, hikaricp still gives me a closed connection. Seems due to memory lack hikaricp doesn't finished correctly the previous transaction and sticks in this state forever.

My intention, now, is to reset or recovery hikaricp. I don't need to care about other threads using the pool.

So, after all, my simple question is, how to programatically restart or recover hikarycp to its primary state, without reboot the application.

Thanks, a lot, for who read this.

smaudi
  • 83
  • 7

2 Answers2

0

Try adding this to your Hibernate configuration:

<property name="hibernate.hikari.connectionTestQuery">select 1</property>

This way HikariCP will test that the connection is still alive before giving it to Hibernate.

Guillaume
  • 14,306
  • 3
  • 43
  • 40
  • I'll try. But this will invoke a test on every transaction, most times unnecessary. You know if is possible to tell hikari when to test? Inside a try/catch block eg? – smaudi Aug 27 '20 at 17:12
  • Catching an OOM is going to be hit and miss: depending on where it happened, various parts of the application might be in an invalid state. There are good points here: https://stackoverflow.com/questions/2679330/catching-java-lang-outofmemoryerror In my opinion you should pay the very small cost of that `select 1` query to ensure that the connection is alive. – Guillaume Aug 27 '20 at 17:53
0

Nothing has worked so far.

I minimized the problem by adding a 'query hint' to the method:

@QueryHints({@QueryHint(name = "org.hibernate.timeout", value = "10")})
MyEntity getOneById(Long id);

99% of the resultsets are fetched in 1 or less second, but sometimes the resultset is so big that takes longer. This way the JDBC stops the result fetching before the memory gets compromised.

smaudi
  • 83
  • 7