Creating a job during a spring boot web requests with a default db connection pool size leads to JDBC timeout [BATCH-2780]

FlorianSW opened BATCH-2780 and commented

During the work with Spring Batch for a project, I ran into the following problem. The project consists of:

• a MySQL database (mysqld 10.2.13-MariaDB)
• Spring Boot (2.1.1.RELEASE)
• Spring Batch (4.1.0.RELEASE)
• Spring Batch is configured to use the same datasource as the business logic for the JobRepository
• The hikari connection pool size for the datasource is configured to have a size of 4 (which is the default when pusing the app to our CloudFoundry instance and injected during the auto-reconfiguration of Spring)

As a reference you can take a look into the sample project where I created a minimal project to reproduce the problem (see reference URL).

The problem:
Given you've a controller, which handles one RequestMapping in which at least two things happen: The controller does an arbitrary action agains the business model database schema (e.g. saving or requesting an entity from the database) and afterwards starting a Spring Batch job through a call to JobLauncher#run. Spring Batch is configured to run the tasks asynchronous with a ThreadPoolTaskExecutor with the pool size of 1.

This works pretty fine, if the request mapping is queries only few times per second (round about 1-3 times). However, if the mapping is queried more than that, let's say 4+ (during testing I used 4 and up to 20 requests) times in a synchronized way (if testing locally), then the requests run into a deadlock, where some of them will be aborted with the following exception:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: Could not obtain last_insert_id(); nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 5001ms.] with root cause

This problem can be mitigated when increasing the size of the connection pool to at least 7 (I don't know where this number could come from). After restarting the application and executing my test JavaScript code[1] to dispatch a number of requests I can easily increase the number of fetch requests to 150 and the application will handle these requests without any problems (as expected). Where, before increasing the pool size, numerous requests run into an timeout.

I'm not sure, if this qualifies as a bug or as an Improvement or whatever, and I'm not sure if the component is the documentation or something like that, however, my intention of this issue is:

Finding out, if Spring Batch, together with Spring Boot, requires a minimum number of available connections in the DB connection pool? If so, should this be mentioned in the documentation?
Or, if this is a bug as the JDBC connections used during the request processing are not returned to the pool in a reasonable short amount of time?

[1]

for (i = 0; i <= 150; i++) {fetch('http://localhost:8080/api/jobs/' + i, {method: 'PUT'})}

Affects: 4.1.0

Reference URL: https://github.com/FlorianSW/spring-batch-connection-issue

Start with the linked sample project and reproduce the timeout using Spring Boot 2.1.1, Spring Batch 4.1.0, MariaDB, a pool size of 4, and concurrent requests that save or read data before launching an asynchronous job. Compare behavior with a larger pool and determine whether the result indicates a required connection-pool minimum, delayed connection return, or documentation gap; done means identifying the cause and the appropriate fix or documentation change.