Spring Boot应用多线程执行存储过程遇连接池超时问题求助

Hey there, let's break down this connection pool timeout issue you're facing. You're using two custom threads in your Spring Boot app to run long-running stored procedures, and hitting the error:

Thread-103 Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available

This happens when all connections in your database pool get tied up and aren't released properly, or your setup is creating more concurrent database requests than the pool can handle. Let's go through the most likely fixes step by step:

1. Fix Connection Leaks (Most Common Culprit) #

If your objectOfRunnable1/objectOfRunnable2 aren't properly releasing database connections, the pool will quickly drain completely. Here's how to fix this:

If using raw JDBC connections: #

Always close connections, statements, and result sets in a finally block to ensure resources are released even if an error occurs:

Connection conn = null;
CallableStatement stmt = null;
try {
    conn = dataSource.getConnection();
    stmt = conn.prepareCall("{CALL your_stored_proc()}");
    stmt.execute();
} catch (SQLException e) {
    // Handle exception
} finally {
    // Clean up resources in reverse order
    if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
    if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
}

If using Spring's JdbcTemplate: #

Make sure your Runnable classes are properly injected with a Spring-managed JdbcTemplate (not manually creating it). JdbcTemplate automatically handles connection acquisition and release, so leaks are far less likely.

2. Replace Custom Threads with a Managed Thread Pool #

Creating raw Thread objects manually is risky—if your retrieveData method gets called frequently, you'll spawn dozens of threads, each grabbing a database connection and draining the pool. Use Spring's ThreadPoolTaskExecutor instead to control concurrency:

Step 1: Configure the thread pool bean #

Add this to your Spring configuration class:

@Configuration
public class ThreadPoolConfig {
    @Bean
    public ThreadPoolTaskExecutor storedProcExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(2); // Match the number of stored procedures you run
        executor.setMaxPoolSize(5); // Prevent excessive thread creation
        executor.setQueueCapacity(10);
        executor.setThreadNamePrefix("StoredProc-Worker-");
        executor.initialize();
        return executor;
    }
}

Step 2: Update your service to use the pool #

@Service
public class MyService {
    private final ReentrantLock reentrantLock = new ReentrantLock();
    private final ReentrantLock reentrantLock2 = new ReentrantLock();
    private final ThreadPoolTaskExecutor storedProcExecutor;
    private final Runnable objectOfRunnable1;
    private final Runnable objectOfRunnable2;

    // Inject dependencies via constructor
    public MyService(ThreadPoolTaskExecutor storedProcExecutor, 
                     Runnable objectOfRunnable1, 
                     Runnable objectOfRunnable2) {
        this.storedProcExecutor = storedProcExecutor;
        this.objectOfRunnable1 = objectOfRunnable1;
        this.objectOfRunnable2 = objectOfRunnable2;
    }

    public List<String> retrieveData() {
        // Fix race condition: Lock first, then check state
        boolean lock1Acquired = false;
        boolean lock2Acquired = false;
        try {
            lock1Acquired = reentrantLock.tryLock(1, TimeUnit.SECONDS);
            if (!lock1Acquired) {
                // Log "Lock is active"
                return Arrays.asList("Process running");
            }
            lock2Acquired = reentrantLock2.tryLock(1, TimeUnit.SECONDS);
            if (!lock2Acquired) {
                // Log "Lock is active"
                return Arrays.asList("Process running");
            }
            // Submit tasks to the managed pool
            storedProcExecutor.submit(objectOfRunnable1);
            storedProcExecutor.submit(objectOfRunnable2);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            return Arrays.asList("Process interrupted");
        } finally {
            // Ensure locks are always released
            if (lock2Acquired) reentrantLock2.unlock();
            if (lock1Acquired) reentrantLock.unlock();
        }
        return Arrays.asList("Process started");
    }
}

3. Tune Your Database Connection Pool #

Spring Boot uses HikariCP by default—its default settings might be too conservative for your workload. Update application.properties to adjust pool size and timeouts:

# Max number of connections in the pool (adjust based on your DB's capacity)
spring.datasource.hikari.maximum-pool-size=10
# Timeout for acquiring a connection (keep reasonable, don't set too high)
spring.datasource.hikari.connection-timeout=30000
# Auto-recycle idle connections to free up pool space
spring.datasource.hikari.idle-timeout=600000
# Max lifetime of a connection (prevents stale connections)
spring.datasource.hikari.max-lifetime=1800000

4. Fix Race Conditions in Your Lock Logic #

Your original code checks if locks are active before acquiring them—this creates a race condition where two requests could both check locks at the same time, see they're free, and both spawn threads (doubling the connection load). The updated code above fixes this by acquiring locks first with a timeout, then proceeding only if both locks are held.

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