Trouble Shooting: Escape from HikariCP Deadlock
3 min read
HikariConnection Pool ERROR · Issue #144 · JeongSeonggil/SubMarket
1. Problem definition
When deploying my project to a server, I ran into an issue where certain tasks were taking too long to run. It worked fine in my local environment, but I ran into problems when running it on the server.
2. Fact gathering
The problem doesn’t always happen, but I confirmed that it does happen occasionally. It occurs intermittently even when executing the same logic.
I checked the server log and found that the DB connection timed out.

Server Log
3. Cause inference
DB performance issues
Issues caused by changing the storage of AWS Free Tier RDS from SSD to Magnetic
JPA infinite reference problem
Waiting requests caused by infinite references
Network issues between servers
Network issues between servers, because I deployed to different servers (Azure and GCP)
Connection pool problems
Found while searching for an issue
4. Write a solution and solve it
About DB performance issues
At first I was sure this was the problem, but the problem continued to occur even after changing to SSD.
About JPA infinite reference problem
I requested each service directly and checked the values, and confirmed that infinite reference does not occur.
About network issues between servers
I confirmed this by logging in the controller.

There was no problem between servers, because the log appeared immediately after the request.
About connection pool problems
On checking, I found that if there is no request to the DB for a certain period of time, the connection is disconnected.
So as long as the connection was valid, there was no problem. I also configured the connection pool in application.yml.
spring:
datasource:
url: jdbc:mariadb://{DB Path}?autoReconnect=true
hikari:
# Connection lifetime
max-lifetime: 40000
read-only: false
connection-timeout: 9000
# Max DB connection pool size : 8
maximum-pool-size: 8
# Test SQL connection
connection-init-sql: SELECT 1
# Default connection size : 4
minimum-idle: 4
dbcp2:
# Check when borrowing a connection
test-on-borrow: true
# Validation query
validation-query: SELECT 1
# Test while idle
test-while-idle: true
hikari
HikariCPis a lightweight, high-speed JDBC connection pool framework. If you’ve ever used Spring Boot, you’ve probably heard the name at least once. Spring Boot usesHikariCPfor connection pool management.
spring.datasource.hikari.max-lifetime=40000Sets the maximum lifetime of a connectionspring.datasource.hikari.maximum-pool-size: 10Sets the maximum number of connections that can be kept in the pool, including idle and busy connections.spring.datasource.hikari.minimum-idle: 4Minimum number of connections in the CP (Connection Pool)spring.datasource.hikari.connection-timeout: 9000Maximum time to wait before getting a connection from the pool
Dbcp2
spring.datasource.dbcp2.test-on-borrow=trueValidates before getting a connection from the poolspring.datasource.dbcp2.validation-query=SELECT 1Query to be used when checking valid connections Use the lightest one,SELECT 1spring.datasource.dbcp2.test-while-idle=trueValidates in the idle state
Connection pool size and performance
“The bigger the connection pool, the better?” At first glance, more seems to be advantageous. However, if the size of the connection pool is greater than the number of threads using the connections, unused connections are created and memory is wasted.
MySQL’s official reference mentions that 15–20 connections are sufficient to handle 600 users. MySQL recommends setting the maximum number of connections to unlimited and then performing a load test to find an optimized value.
Pool size setting formula
pool size = Tn * (Cm - 1) + 1
Tn: total number of threads
Cm: number of connections required simultaneously in one task
If you want to know more about the size and performance of the connection pool, please refer to the following. Number Of Database Connections