Spring Data JPA生成过多未关闭Oracle无效会话问题排查

API 探路者

2026-7-1

问题描述

基于Spring Boot + Spring Data JPA的REST API部署在Tomcat上，应用运行正常，但会缓慢创建大量未被清理的Oracle数据库无效会话。

数据库配置（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);
}

初始警告日志（catalina.out）

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

更新错误日志

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

解决方案

针对连接无法清理、关闭失败的问题，按以下步骤处理：

1. 修复HikariCP配置警告

调整连接泄漏检测阈值：当前leak-detection-threshold设为50分钟，超过了max-lifetime的30分钟，导致该功能被禁用。将其改为小于max-lifetime的值，比如25分钟（1500000ms），开启泄漏检测：
```
spring.datasource.hikari.leak-detection-threshold=1500000
```
解决idleTimeout无效问题：你的配置中min-idle-connections不是Hikari的标准配置键，导致Hikari默认minIdle等于maximum-pool-size，变成固定池。修正为标准配置键，让minIdle小于maxPoolSize，使idleTimeout生效：
```
spring.datasource.hikari.min-idle=8
```
若不需要动态调整池大小，也可直接移除idleTimeout配置。

2. 关闭Spring Open-in-View特性

默认开启的spring.jpa.open-in-view会让数据库连接在整个请求生命周期保持打开，容易引发连接泄漏。显式关闭：

spring.jpa.open-in-view=false

3. 解决连接关闭时的Socket超时问题

日志显示Hikari尝试关闭过期连接时因Socket读取超时失败，导致无效会话残留，可通过以下配置优化：

添加连接有效性验证：配置connection-test-query，让Hikari在借出连接前验证可用性，避免使用已被Oracle断开的连接：
```
spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
```
缩短连接最大生命周期：设置为小于Oracle默认连接超时时间（建议20分钟即1200000ms），避免Hikari尝试关闭已被Oracle主动断开的连接：
```
spring.datasource.hikari.max-lifetime=1200000
```

配置JDBC URL超时参数：在URL中添加连接和读取超时，减少关闭连接时的等待时间：

spring.datasource.url=jdbc:oracle:thin:@***?oracle.net.CONNECT_TIMEOUT=10000&oracle.net.READ_TIMEOUT=15000

调整Oracle端连接清理参数：修改Oracle的SQLNET.EXPIRE_TIME参数（建议设为10分钟），让数据库主动检测并清理死连接，避免无效会话堆积。

4. 优化代码减少连接占用

fetchUsersMap()方法每次请求都会全量查询用户并转换为Map，频繁查询会增加连接使用时长和次数。可通过缓存优化：

使用Spring@Cacheable注解缓存用户Map，避免重复查询：

@Cacheable(value = "usersMap", key = "#root.methodName")
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;
}

注意在用户数据更新时，添加@CacheEvict或@CachePut注解刷新缓存。

内容的提问来源于stack exchange，提问作者Somebody

Spring Data JPA生成过多未关闭Oracle无效会话问题排查

数据库配置（Db.properties） #

核心代码片段 #

UserController.java #

UserService.java #

Utils.java #

初始警告日志（catalina.out） #

更新错误日志 #

1. 修复HikariCP配置警告 #

2. 关闭Spring Open-in-View特性 #

3. 解决连接关闭时的Socket超时问题 #

4. 优化代码减少连接占用 #