Spring Boot JPA and HikariCP maintaining active connections

Question

Brief:

Is there a way to ensure that a connection to the database is returned to the pool?

Not-brief:

Data flow:

I have some long running tasks that could be sent to the server in large volume bursts.
Each of the requests is recorded in the DB that the submission was started. Then send that request off for processing.
If failure or success the request is recorded after the task is completed.

The issue is that after the submission is recorded all the way through the long running task, the connection pool uses an "active" connection. This could potential use up any size pool I have if the burst was large enough.

I am using spring boot with the following structure:

Controller - responds at "/" and has the "service" autowired.
Service - Contains all the JPA repositories and @Transactional methods to interact with the database.

When every the first service method call is made from the controller it opens an active connection and doesn't release it until the controller method returns.

So, Is there a way to return the connection to the pool after each service method?

Here is the service class in total:

@Service
@Slf4j
class SubmissionService {

@Autowired
CompanyRepository companyRepository;

@Autowired
SubmissionRepository submissionRepository;

@Autowired
FailureRepository failureRepository;

@Autowired
DataSource dataSource

@Transactional(readOnly = true)
public Long getCompany(String apiToken){
    if(!apiToken){
        return null
    }
    return companyRepository.findByApiToken(apiToken)?.id
}

@Transactional
public void successSubmission(Long id) {
    log.debug("updating submission ${id} to success")
    def submissionInstance = submissionRepository.findOne(id)
    submissionInstance.message = "successfully analyzed."
    submissionInstance.success = true
    submissionRepository.save(submissionInstance)
}

@Transactional
public long createSubmission(Map properties) {
    log.debug("creating submission ${properties}")
    dataSource.pool.logPoolState()
    def submissionInstance = new Submission()
    for (key in properties.keySet()) {
        if(submissionInstance.hasProperty(key)){
            submissionInstance."${key}" = properties.get(key)
        }
    }
    submissionInstance.company = companyRepository.findOne(properties.companyId)
    submissionRepository.save(submissionInstance)
    return submissionInstance.id
}

@Transactional
public Long failureSubmission(Exception e, Object analysis, Long submissionId){
    //Track the failures
    log.debug("updating submission ${submissionId} to failure")
    def submissionInstance
    if (submissionId) {
        submissionInstance = submissionRepository.findOne(submissionId)
        submissionRepository.save(submissionInstance)
    }

    def failureInstance = new Failure(submission: submissionInstance, submittedJson: JsonOutput.toJson(analysis), errorMessage: e.message)

    failureRepository.save(failureInstance)

    return failureInstance.id
  }
}

Why do you think a connection is retrieved for a controller method call and only given back to the pool when the controller method returns? The connection is given back to the pool when the transaction ends. — JB Nizet, Jul 01 '15 at 19:49
Well that depends if there is a `OpenEntityManagerInViewFilter` (or interceptor) a session and connection are opened and maintained for the incoming request. — M. Deinum, Jul 02 '15 at 06:04
@JBNizet from my debugging that seems to the when it is released. I was able to log the pools active connections and connection isn't returned after the Transactional method. — Tim Overly, Jul 02 '15 at 14:13
@M.Deinum I up-voted your comment, and removed it by mistake, I am going to get someone else to up-vote it again. — Tim Overly, Jul 07 '15 at 18:35

score 0 · Accepted Answer · answered Jul 07 '15 at 18:35

It turns out that @M.Deinum was onto the right track. Spring Boot JPA automatically turns on the "OpenEntityManagerInViewFilter" if the application property spring.jpa.open_in_view is set to true, which it is by default. I found this in the JPA Configuration Source.

After setting this to false, the database session wasn't held onto, and my problems went away.

Spring Boot JPA and HikariCP maintaining active connections

1 Answers1