I have a rest API project using spring boot and spring data JPA that is hosted in a tomcat server. The application works fine, but it slowly creates too many inactive oracle db sessions, that never clears out.
Db.properties
# Oracle DB properties
spring.datasource.url=jdbc:oracle:thin:@***
spring.datasource.hikari.username=***
spring.datasource.hikari.schema=***
spring.datasource.hikari.password=***
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=3000000
spring.datasource.hikari.pool-name=UserDbPool
spring.datasource.hikari.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.isolation-level=TRANSACTION_READ_COMMITTED
spring.datasource.min-idle-connections=8
spring.datasource.driver.type=thin
UserController.java
@PostMapping("/process")
public PostResponse processUser(@RequestBody PostRequest inputPayLoad) {
return service.processUser(inputPayLoad);
}
UserService.java
public PostResponse processUser(PostRequest request) {
PostResponse response = new PostResponse();
String composedKey = request.getUserCode + "|" + request.getUserLastName;
User user = utils.findUser(utils.fetchUsersMap(), composedKey);
response.setUser(user);
return response;
}
Utils.java
public Map<String, User> fetchUsersMap() {
Map<String, User> usersMap = new HashMap<>();
Iterable<User> userList = userRepo.findAll();
for (User user : userList) {
usersMap.put(user.getUserCode + "|" + user.getUserLastName, user);
}
return usersMap;
}
public User findUser(Map<String, User> usersMap, String key) {
return usersMap.get(key);
}
Below are the only warnings I see in catalina.out, no errors at all:
4342 [01 Feb 2024 09:48:13.521] WARN *** [main] com.zaxxer.hikari.HikariConfig - UserRestApi - leakDetectionThreshold is less than 2000ms or more than maxLifetime, disabling it.
4343 [01 Feb 2024 09:48:13.522] WARN *** [main] com.zaxxer.hikari.HikariConfig - UserRestApi - idleTimeout has been set but has no effect because the pool is operating as a fixed size pool.
8816 [01 Feb 2024 09:48:17.995] WARN *** [main] org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration$JpaWebConfiguration - spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
Am I missing something here that is making the idle db connections not to be clear out?
UPDATE
Now I see below logs from time to time:
2291347 [05 Feb 2024 09:52:52.868] DEBUG *** [UserDbPool connection closer] com.zaxxer.hikari.pool.PoolBase - UserDbPool - Closing connection oracle.jdbc.driver.T4CConnection@44fd65be: (connection has passed maxLifetime)
2306347 [05 Feb 2024 09:53:07.868] DEBUG *** [UserDbPool connection closer] com.zaxxer.hikari.pool.PoolBase - UserDbPool - Closing connection oracle.jdbc.driver.T4CConnection@44fd65be failed
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:1922)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2746)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: oracle.net.nt.TimeoutInterruptHandler$IOReadTimeoutException: Socket read timed out
at oracle.net.nt.TimeoutSocketChannel.handleInterrupt(TimeoutSocketChannel.java:544)
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:420)
at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:1119)
at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:267)
at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:199)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:141)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:114)
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:98)
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:834)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:487)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:622)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:74)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:1912)
... 6 common frames omitted
I think that this is what is leaving the idle connections open, since it is failing to close them. Any idea on how to prevent this from happening or how to fix this?
The issue I was having was specific to the Hikari settings.
See HikariCP official documentation
maxLifetime: This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool.
We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)
In my case the DB setting values were too low compared to the maxLifetime I had setup in my properties and that was creating issues with the idle connections not being closed.
Bottom line is that, be very careful setting up the Hikari values, check the documentation and work closely with your dba to ensure you have the correct settings.