Spring + Hibernate+ HikariCP: how to handle DB connection while doing long running REST call?

Question

I have a project running on Spring Boot 1.3.8, Hikari CP 2.6.1 and Hibernate (Spring ORM 4.2.8). The code on service layer looks like this:

public void doStuff() {
    A a = dao.findByWhatever();
    if (a.hasProperty()) {
        B b = restService.doRemoteRequestWithRetries(); // May take long time
    }
    a.setProp(b.getSomethig());
    dao.save(b);
}

Hikari configuration has this: spring.datasource.leakDetectionThreshold=2000. The problem is that external REST service is quite slow and often takes 2+ seconds to respond, as a result we see a lot of java.lang.Exception: Apparent connection leak detected which are nothing else but false negatives, though the problem can be clearly seen: we hold DB connection for the time we executing rest request.

The question would be: how to properly decouple DB and REST stuff? Or how to tell hibernate to release connection in between? So that we return DB connection to pool while waiting for REST response.

I have tried setting hibernate.connection.release_mode=AFTER_TRANSACTION and it kind of helps, at least we do not have connection leak exceptions. The only problem is that our tests started showing this:

2018-04-17 15:48:03.438  WARN 94029 --- [           main] o.s.orm.jpa.vendor.HibernateJpaDialect   : JDBC Connection to reset not identical to originally prepared Connection - please make sure to use connection release mode ON_CLOSE (the default) and to run against Hibernate 4.2+ (or switch HibernateJpaDialect's prepareConnection flag to false`

The tests are using injected DAO to insert records in DB and later check them via application API. They are not annotated with @Transactional and the list of listeners looks like this:

@TestExecutionListeners({
    DependencyInjectionTestExecutionListener.class,
    TransactionalTestExecutionListener.class,
    TransactionDbUnitTestExecutionListener.class
})

Any ideas what could be the problem with tests?

Answer 1

In the code

public void doStuff() {
    A a = dao.findByWhatever();
    if (a.hasProperty()) {
        B b = restService.doRemoteRequestWithRetries(); // May take long time
    }
    a.setProp(b.getSomethig());
    dao.save(b);
}

I see three tasks here - fetching entity A, connecting to remote service and updating entity A. And all these are in same transaction, so the underlying connection will be held till the method is complete.

So the idea is to split the tasks one and three into separate transactions, there by allowing the connection to be releases before making the call to remote service.

Basically, with spring boot you need to add spring.jpa.open-in-view=false. This will not register OpenEntityManagerInViewInterceptor and thus entityManager (in-turn connection) is not bound to the current thread/request.

Subsequently, split the three tasks into separate methods with @Transactional. This helps us bind the entityManager to the transaction scope and releasing connection at end of transaction method.

NOTE: And do ensure that there isn't any transaction started/in progress before (i.e., caller - like Controller etc) calling these methods. Else the purpose is defeated and these new @Transactional methods will run in the same transaction as before.

---

So the high-level approach could look like below:

• In spring boot application.properties add property spring.jpa.open-in-view=false.
• Next you need to split doStuff method into three methods in new service class. Intent is to ensure they use different transactions.
  • First method with @Transactionalwill call A a = dao.findByWhatever();`.
• Second method makes remote call.
• Third method with @Transactionalwill call rest of the code with JPA merge or hibernate saveOrUpdate on objecta`.
• Now Autowired this new service in your current code and call the 3 methods.