Solving Database Connection Pool Exhaustion in Spring Boot Batch Applications

I recently hit a wall with one of my Spring Boot applications — a batch processing system that performs computationally intensive operations on large datasets. Everything worked fine during development, but when I scaled up to process multiple records in parallel, the application started failing with connection pool exhaustion errors.

I’ll share here how I solved this issue and hopefully it would help you also to solve this :)

Context

My application is a Spring Batch job that:

1. Loads input data for multiple record identifiers from PostgreSQL
2. Performs CPU-intensive computations on the data (each operation takes ~10 minutes)
3. Writes computed results back to the database
4. Processes multiple records in parallel using Spring Batch’s partitioning

The error was clear: HikariPool-1 - Connection is not available, request timed out after 30000ms. The connection pool was exhausting, but why?

First Instinct: Increase the Pool Size?

My initial reaction was to bump up the HikariCP pool size from 16 to 20, maybe even 50. But before doing that, I remembered reading about proper connection pool sizing. According to the HikariCP documentation, there’s a formula¹:

connections = ((core_count * 2) + effective_spindle_count)

For my 8-core machine with SSDs, that’s roughly 16-20 connections which I already had configured. The problem wasn’t the pool size. It was how long I was holding connections.

As this excellent blog post points out, connection pool exhaustion is usually about duration, not capacity. I needed to find where connections were being held unnecessarily.

Debugging with JMX

I enabled JMX monitoring (see my previous post about JMX) and connected VisualVM to the running application. HikariCP exposes MBeans that show:

• Active connections
• Idle connections
• Total Connections
• Threads waiting for connections²

What I saw was revealing: during the computation phase, all connections were marked as “active” even though the application was just downloading the data at the begining of the processing and then using it in the processing loop, without updating or writing anything to the database.

Root Cause #1: Spring Transactions Holding Connections

Looking at my Spring Batch configuration, I found the culprit:

@Bean
fun primaryTransactionManager(): PlatformTransactionManager {
    return DataSourceTransactionManager(primaryDataSource())
}

Spring Batch’s chunk-oriented processing runs inside transactions by default. This meant that for each record being processed:

1. Transaction starts
2. Data loads from DB (connection acquired)
3. Computation happens (~10 minutes, connection still held!)
4. Results written
5. Transaction commits (connection finally released)

The computation doesn’t need a database connection, but Spring was holding one the entire time because of the transaction.

The Fix: ResourcelessTransactionManager

Spring Batch actually provides a solution: ResourcelessTransactionManager. It’s a no-op transaction manager that satisfies Spring Batch’s API requirements without actually managing transactions³:

@Bean
@Primary
fun primaryTransactionManager(): PlatformTransactionManager {
    // No-op transaction manager - HikariCP handles connections automatically
    return ResourcelessTransactionManager()
}

With this change, connections are returned to the pool immediately after each database operation. HikariCP handles connection management automatically using try-with-resources semantics.

I updated both the primary and batch transaction managers:

- return DataSourceTransactionManager(primaryDataSource())
+ return ResourcelessTransactionManager()

Root Cause #2: JPA Entities Not Being Detached

Even after switching to ResourcelessTransactionManager, I still saw connection leaks. Using JMX, I noticed that connections were held longer than expected during the data loading phase.

The issue was JPA’s persistence context. When I loaded thousands of entities for processing:

val entities = observationRepository.findByRecordIdAndTimestampBetween(
    recordId, startDate, endDate
)

The entities remained “managed” by the EntityManager. This keeps the underlying JDBC connection open, even after I’d finished using the entities.

The Fix: EntityManager.clear()

The solution is to explicitly detach entities once you’re done with them⁴:

// Load data
val entities = observationRepository.findByRecordIdAndTimestampBetween(
    recordId, startDate, endDate
)

// Convert to DataFrame (accesses all data)
val dataFrame = entities.toDataFrame().renameToPhysicalColumns(columnNameResolver)

// CRITICAL: Detach entities to release DB connection immediately
entityManager.clear()
logger.debug("[$recordId] Cleared EntityManager - connection released")

I added this to both my DataLoaderService and MetaDataLoaderService. The key is to call clear() after accessing any lazy-loaded relationships but before starting long-running operations.

Root Cause #3: Batch Writer Auto-Flushing

The third issue was in my BatchResultWriter. I had implemented auto-flushing every 100 results:

fun write(result: ProcessedResult) {
    val buffer = resultBuffer.get()
    buffer.add(result)

    if (buffer.size >= BATCH_SIZE) {
        flushBuffer(buffer)  // Acquires connection during processing!
    }
}

This meant during computation (when I generate thousands of results), I was constantly acquiring connections to flush data. Each flush held a connection for the duration of the database write.

The Fix: Defer Flushing Until Processing Completes

Instead of auto-flushing, I deferred all writes until after computation completes:

fun write(result: ProcessedResult) {
    val buffer = resultBuffer.get()
    buffer.add(result)

    // DO NOT auto-flush during processing - wait for explicit flush() call
    // This prevents holding DB connections during long-running computations
}

fun flush(): Int {
    val buffer = resultBuffer.get()
    val count = buffer.size

    if (count > 0) {
        logger.info("Flushing $count results in chunks of $WRITE_BATCH_SIZE")

        // Write in chunks to avoid holding connection too long
        buffer.chunked(WRITE_BATCH_SIZE).forEach { chunk ->
            flushBatch(chunk)
        }
    }

    buffer.clear()
    return count
}

Now results accumulate in memory during processing, then get written in efficient batches (500 at a time) once computation completes. This reduces connection hold time dramatically.

Bonus Fix: Transaction-Free UPSERT in the DB Library

In my database library, I had repositories using Spring Data JPA’s @Query annotation for UPSERT operations. These were implicitly transactional, which was unnecessary for simple INSERT ON CONFLICT operations.

I refactored to use JdbcTemplate directly:

@Repository
public class ProcessedResultRepositoryImpl implements ProcessedResultRepositoryCustom {

    private static final String UPSERT_SQL = """
        INSERT INTO processed_results
            (id, observation_id, record_id, result_value, confidence_score,
             processing_timestamp, metric_a, metric_b, metric_c, batch_index, updated_at)
        VALUES
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT (observation_id) DO UPDATE SET
            result_value = EXCLUDED.result_value,
            confidence_score = EXCLUDED.confidence_score,
            processing_timestamp = EXCLUDED.processing_timestamp,
            metric_a = EXCLUDED.metric_a,
            metric_b = EXCLUDED.metric_b,
            metric_c = EXCLUDED.metric_c,
            batch_index = EXCLUDED.batch_index,
            updated_at = CURRENT_TIMESTAMP
    """;

    private final JdbcTemplate jdbcTemplate;

    @Override
    public void batchUpsertResults(List<ResultData> results) {
        jdbcTemplate.batchUpdate(
            UPSERT_SQL,
            results,
            results.size(),
            (PreparedStatement ps, ResultData result) -> {
                ps.setString(1, result.id);
                ps.setString(2, result.observationId);
                ps.setString(3, result.recordId);
                ps.setInt(4, result.resultValue);
                ps.setDouble(5, result.confidenceScore);
                ps.setTimestamp(6, Timestamp.valueOf(result.processingTimestamp));
                ps.setDouble(7, result.metricA);
                ps.setDouble(8, result.metricB);
                ps.setDouble(9, result.metricC);
                ps.setInt(10, result.batchIndex);
            }
        );
    }
}

This eliminates transaction overhead and gives precise control over connection usage. The connection is acquired, the batch UPSERT executes, and the connection is immediately returned to the pool.

Hibernate Configuration for Aggressive Connection Release

Finally, I configured Hibernate to release connections as aggressively as possible⁵:

# JPA/Hibernate configuration - Aggressive connection release
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.connection.release_mode=after_statement
spring.jpa.properties.hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT

Critical note: spring.jpa.open-in-view=false is essential. The Open Session In View (OSIV) pattern, enabled by default in Spring Boot, keeps database connections open throughout the entire HTTP request lifecycle. As this blog post explains, it’s equivalent to having @Transactional on every controller method. Turn it off unless you have a specific reason to keep it.

HikariCP Configuration

Here’s my final HikariCP configuration for the primary datasource:

# Primary HikariCP settings - Optimized for quick connection release
spring.datasource.primary.hikari.maximum-pool-size=20
spring.datasource.primary.hikari.minimum-idle=2
spring.datasource.primary.hikari.connection-timeout=5000
spring.datasource.primary.hikari.idle-timeout=60000
spring.datasource.primary.hikari.max-lifetime=300000
spring.datasource.primary.hikari.leak-detection-threshold=10000
spring.datasource.primary.hikari.register-mbeans=true

Key settings:

• maximum-pool-size=20: Based on the formula (cores × 2 + spindles)
• minimum-idle=2: Keep pool small, connections are cheap to create
• leak-detection-threshold=10000: Alert if connections held longer than 10 seconds
• register-mbeans=true: Enable JMX monitoring (essential for debugging!)

Results

After implementing these changes:

• Connection pool exhaustion: eliminated
• Peak active connections: reduced significantly
• Application successfully processes multiple records in parallel
• No connection timeout errors during extended operation

Key Takeaways

1. Pool sizing isn’t always the answer — focus on connection hold duration first
2. Use JMX to debug — HikariCP’s MBeans show exactly what’s happening
3. Avoid unnecessary transactions — use ResourcelessTransactionManager when appropriate
4. Detach JPA entities — call EntityManager.clear() after data loading
5. Defer batch operations — accumulate in memory, flush after long operations complete
6. Turn off OSIV — spring.jpa.open-in-view=false unless you need it
7. Monitor connection usage — leak-detection-threshold catches issues early

Connection pool exhaustion is rarely about needing more connections. It’s almost always about holding connections longer than necessary. Find where you’re holding them, release them sooner, and your pool will be just fine.