Spring Boot异步调用Oracle存储过程，大数据量时执行挂起

当异步执行Oracle存储过程的任务列表规模增大到1000左右时，线程会在执行到930~950个任务时冻结。调整线程池核心数（从5到20）、最大数，以及Hikari连接池大小（从20到100）后，问题依然存在。尝试在线程内通过EntityManagerFactory创建/销毁EntityManager，也未解决问题。

异步方法实现 #

@Autowired
EntityManager entityManager;

@Async ("taskExecutor")
public CompletableFuture<ResultObject> executeProcedure(Arg arg1) {
    ResultObject resultObject = null;
    try{
       StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("PROCEDURE_NAME");
       storedProcedure.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
       storedProcedure.registerStoredProcedureParameter(2, ResultSet.class, ParameterMode.REF_CURSOR);
       storedProcedure.setParameter(1, arg1);
       resultObject = storedProcedure.getResultList();
       log.info("Retrieved result for arg {}", arg1);
    } catch (Exception e) {
     //exception handling
    }
    return CompletableFuture.completedFuture(resultObject);
}

调用方代码 #

List<ResultObject> results = argList
.stream()
.map(arg -> serviceClass.executeProcedure(arg))
.map(CompletableFuture::join)
.toList();

线程池配置 #

@Bean(name = "taskExecutor")
public TaskExecutor adminTaskExecutor() {
    final ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
    executor.setCorePoolSize(20);
    executor.setMaxPoolSize(50);
    executor.setKeepAliveSeconds(threadTtl);
    executor.setThreadNamePrefix("Task thread - ");
    executor.initialize();
    return executor;
}

Oracle存储过程代码 #

CREATE OR REPLACE PROCEDURE get_delta_users (
    p_user_schema IN VARCHAR2,
    p_person_schema IN VARCHAR2,
    p_where_clause IN VARCHAR2,
    p_person_id IN NUMBER,
    p_cursor OUT SYS_REFCURSOR
)
IS
v_user_store_query VARCHAR2(4000);
v_role_membership_query VARCHAR2(4000);
BEGIN

v_user_store_query := ' SELECT username FROM ' || p_user_schema || '.users_view WHERE ';
v_person_store_query := ' SELECT username FROM ' || p_person_schema || '.person WHERE person_id = ' || p_person_id;

OPEN p_cursor FOR '(' || v_user_store_query || '(' || p_where_clause || ')' || ' MINUS ' || v_person_store_query || ')' || ' UNION ' || '(' || v_person_store_query || ' MINUS ' || v_user_store_query || '(' || p_where_clause || ')' || ')' ;

END;

请注意：忽略示例代码中传递给存储过程的参数差异

问题排查方向 #

• 数据库游标未释放：存储过程返回的REF_CURSOR在获取结果后，底层JDBC资源（ResultSet、Statement）可能未自动关闭，大量未关闭的游标会耗尽数据库资源，导致后续任务阻塞。
• 线程池无界队列积压：ThreadPoolTaskExecutor默认使用无界队列，当任务数超过线程处理能力时，任务堆积在队列中。若数据库资源耗尽，后续任务会一直等待连接，表现为线程挂起。
• EntityManager资源泄漏：即使通过EntityManagerFactory创建实例，若未在finally块中正确关闭，会导致连接池资源耗尽。
• 数据库会话限制：Oracle数据库的processes、sessions参数存在上限，并发任务过多时会达到会话上限，新任务无法创建连接而阻塞。
• 调用方join()的串行等待：stream().map(CompletableFuture::join)会逐个等待任务完成，虽异步提交但最终串行等待，可能放大资源耗尽后的阻塞影响。

解决方案建议 #

1. 手动关闭数据库游标资源
   在获取结果后，通过unwrap获取JDBC对象强制关闭游标：

   try {
       StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("PROCEDURE_NAME");
       // 参数配置与执行逻辑
       resultObject = storedProcedure.getResultList();
       // 关闭底层CallableStatement
       if (storedProcedure.isOpen()) {
           storedProcedure.unwrap(CallableStatement.class).close();
       }
   } catch (Exception e) {
       // 异常处理
   }
   

2. 配置线程池有界队列
   修改线程池配置，设置队列容量和拒绝策略，避免无界队列导致的资源耗尽：

   @Bean(name = "taskExecutor")
   public TaskExecutor adminTaskExecutor() {
       final ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
       executor.setCorePoolSize(20);
       executor.setMaxPoolSize(50);
       executor.setKeepAliveSeconds(threadTtl);
       executor.setQueueCapacity(200); // 设置有界队列
       executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy()); // 拒绝策略
       executor.setThreadNamePrefix("Task thread - ");
       executor.initialize();
       return executor;
   }
   

3. 严格管理EntityManager生命周期
   确保每个异步任务内的EntityManager在finally块中关闭：

   EntityManager em = entityManagerFactory.createEntityManager();
   try {
       // 执行存储过程逻辑
   } finally {
       if (em.isOpen()) {
           em.close();
       }
   }
   

4. 检查并调整数据库资源限制
   查询Oracle数据库的会话和游标限制：

   SELECT name, value FROM v$parameter WHERE name IN ('processes', 'sessions', 'open_cursors');
   

   根据并发需求调整对应参数。

5. 优化存储过程性能
   存储过程中的动态SQL易产生硬解析，可改用绑定变量优化，同时简化SQL逻辑减少数据库资源占用。

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