A Java Spring Boot payment-reconciliation service uses a HikariCP pool (`maximumPoolSize: 30`, `connectionTimeout: 30000ms`) against RDS Postgres. Every 6-8 hours, all worker threads freeze and requests fail with `SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms`. Postgres `pg_stat_activity` shows 30 connections in state `idle in transaction` or `idle` held by the app for hours, yet RDS CPU is 8%. Identify the leak pattern in manual JDBC error handling, fix the resource-lifecycle code, and harden HikariCP + Postgres to detect and survive leaks.
From patterns to decisions.
A Java Spring Boot payment-reconciliation service uses a HikariCP pool (`maximumPoolSize: 30`, `connectionTimeout: 30000ms`) against RDS Postgres. Every 6-8 hours, all worker threads freeze and requests fail with `SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms`. Postgres `pg_stat_activity` shows 30 connections in state `idle in transaction` or `idle` held by the app for hours, yet RDS CPU is 8%. Identify the leak pattern in manual JDBC error handling, fix the resource-lifecycle code, and harden HikariCP + Postgres to detect and survive leaks.
Unclosed Connection objects on exception.
Unclosed Connection objects on exception paths stay open in the JVM (until GC, if ever) with autoCommit=false, leaving Postgres idle in transaction; sequential close() calls in finally abort if the first close() throws.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Killing Sessions in Postgres vs. Fixing App Leaks; pro: `idle_in_transaction_session_timeout` protects DB locks and VACUUM immediately.; con: Does not fix the JVM's HikariCP accounting if the app still holds a dead proxy reference without calling `close()`. |
Step 1
Query pg_stat_activity for state = 'idle in transaction' and now() - state_change.
Step 2
Audit all dataSource.getConnection() calls not wrapped in try-with-resources.
Step 3
Check pods * maximumPoolSize against Postgres max_connections.
Alternative 1
Use pg_terminate_backend(pid) as an emergency relief valve while rolling the code fix.
name
Pool Size Exceeds Postgres max_connections; description: 20 pods x 30 = 600 > 500 max_connections, crashing new pods on boot.; mitigation: Drop maximumPoolSize to 10 or deploy RDS Proxy / PgBouncer.
SELECT pid, usename, client_addr, state, now() - state_change AS idle_duration, left(query, 80)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;
Implementation & Guardrails (2).
Acquire Connection in try-with-resources, handle rollback() safely inside, and configure HikariCP with leak-detection-threshold: 5000 and maximum-pool-size: 10.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Raw try-with-resources vs. Spring `TransactionTemplate`; pro: `TransactionTemplate` eliminates boilerplate `setAutoCommit`/`rollback` code completely.; con: Requires Spring TX manager; raw `try-with-resources` works in any plain Java/JDBC code. |
Step 1
Wrap Connection in outer try (...) and PreparedStatement/ResultSet in inner try (...).
Step 2
Catch exceptions to call conn.rollback() before rethrowing; outer try always calls conn.close().
Set `leak-detection-threshold
5000, connection-timeout: 5000, maximum-pool-size: 10`.
Alternative 1
Use Spring TransactionTemplate.executeWithoutResult(status -> jdbcTemplate.batchUpdate(...)).
name
Rollback Throws Secondary Exception; description: If conn.rollback() throws because the socket died, the original exception can be masked if not suppressed.; mitigation: Outer try-with-resources still closes conn cleanly; log rollback failure as suppressed.
public void reconcileBatch(List<Payment> batch) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement("UPDATE payments SET status = ? WHERE id = ?")) {
for (Payment p : batch) {
ps.setString(1, p.status());
ps.setLong(2, p.id());
ps.addBatch();
}
ps.executeBatch();
conn.commit();
} catch (Exception ex) {
conn.rollback();
throw ex;
}
} // Always closes conn and returns it to HikariCP
}
Add PgBouncer or RDS Proxy.
Add PgBouncer or RDS Proxy when horizontal pod scaling (pods * pool_size) or serverless Lambdas exceed Postgres max_connections (or ~200+ connections), as Postgres forks 1 OS process per connection.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | PgBouncer Transaction Mode vs. Direct HikariCP; pro: Supports 1,000+ client connections backed by 40 Postgres processes.; con: Adds ~0.5 ms network hop and requires disabling session-scoped state. |
Step 1
Calculate max_pods * maximum_pool_size (e.g., 50 pods x 10 = 500 -> hits limit).
Step 2
Deploy PgBouncer in transaction pooling mode (default_pool_size = 40).
Step 3
Keep prepareThreshold=0 (or PgBouncer 1.21+ protocol-level prepared statement support).
Alternative 1
AWS RDS Proxy for managed multi-AZ connection pooling and fast failover.
name
Prepared Statement Collision in Old PgBouncer; description: JDBC server-side prepared statements fail with prepared statement "S_1" does not exist in transaction mode.; mitigation: Upgrade to PgBouncer >= 1.21 (max_prepared_statements = 100) or set prepareThreshold=0 in JDBC URL.
# application.yml HikariCP settings
spring:
datasource:
hikari:
maximum-pool-size: 10
minimum-idle: 2
connection-timeout: 5000
max-lifetime: 1500000
leak-detection-threshold: 5000
Set PostgreSQL JDBC socketTimeout=15 and.
Set PostgreSQL JDBC socketTimeout=15 and connectTimeout=3 alongside maxLifetime=1500000 so dead TCP sockets don't hang threads forever, and isolate slow batch queries onto a separate pool.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Single Shared Pool vs. Separate OLTP & Batch Pools; pro: Batch jobs cannot starve real-time checkout APIs of connections.; con: Doubles total pool configurations to monitor. |
Step 1
Add ?connectTimeout=3&socketTimeout=15 to the JDBC URL.
Step 2
Separate OLTP (leakDetectionThreshold=5000) from Batch (leakDetectionThreshold=60000) DataSources.
Step 3
Alert on hikaricp_connections_active == hikaricp_connections_max.
Alternative 1
Use Kubernetes preStop + graceful shutdown so HikariCP drains active connections on pod termination.
name
Missing socketTimeout During AZ Partition; description: An AZ network blip leaves JDBC read() blocked for 15+ minutes waiting on OS TCP retries.; mitigation: Always set socketTimeout (e.g., 15s) in the JDBC connection string.
# JDBC URL with TCP timeouts so half-open sockets fail fast
jdbc:postgresql://prod-pg.internal:5432/payments?connectTimeout=3&socketTimeout=15&tcpKeepAlive=true
Implementation & Guardrails (5).
Enforce SonarQube/SpotBugs static analysis (java:S2095) on PRs, and run an integration test with leakDetectionThreshold=1000 that asserts zero leak warnings under simulated SQLExceptions.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Static Analysis Gate vs. Runtime Chaos Test; pro: Static analysis catches 95% of leaks at compile time in seconds.; con: Helper methods that return an open `Connection` can bypass local static checks, so JUnit pool assertions remain essential. |
Step 1
Enable SpotBugs OBL_UNSATISFIED_OBLIGATION / Sonar java:S2095 as a required PR check.
Step 2
Write a JUnit test injecting an exception mid-transaction and asserting HikariPoolMXBean.getActiveConnections() == 0 afterward.
Alternative 1
Migrate remaining raw JDBC DAOs to Spring JdbcClient / JdbcTemplate.
name
Factory Method Returning Unclosed Connection; description: A utility method opens a Connection and returns it to a caller that forgets try-with-resources.; mitigation: Ban methods returning raw Connection or ResultSet; pass lambdas/callbacks (Consumer<Connection>) instead.
@Test
void connectionReturnedOnException() {
assertThrows(DataAccessException.class, () -> dao.reconcileFailingBatch());
HikariPoolMXBean mxBean = ((HikariDataSource) dataSource).getHikariPoolMXBean();
assertEquals(0, mxBean.getActiveConnections());
}
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Enforce
try-with-resources(or SpringJdbcTemplate/JPA) on everyConnection,PreparedStatement, andResultSet, enableleak-detection-threshold=5000, and capmaxLifetimebelow DB/NAT idle timeouts. - 02Trade-off tip: factor: PgBouncer Transaction Mode vs. Direct HikariCP; pro: Supports 1,000+ client connections backed by 40 Postgres processes.; con: Adds ~0.5 ms network hop and requires disabling session-scoped state.
- 03Watch out for: name: Missing
socketTimeoutDuring AZ Partition; description: An AZ network blip leaves JDBCread()blocked for 15+ minutes waiting on OS TCP retries.; mitigation: Always setsocketTimeout(e.g., 15s) in the JDBC connection string.
Preventing Jdbc Connection Pool Leaks — Normal → Load / Fault → Recovery
Enforce try-with-resources (or Spring JdbcTemplate/JPA) on every Connection, PreparedStatement, and ResultSet, enable leak-detection-threshold=5000, and cap maxLifetime below DB/NAT idle timeouts.
Putting Preventing JDBC Connection Pool Leaks Together
Enforce `try-with-resources` (or Spring `JdbcTemplate`/JPA) on every `Connection`, `PreparedStatement`, and `ResultSet`, enable `leak-detection-threshold=5000`, and cap `maxLifetime` below DB/NAT idle timeouts.
Putting Preventing JDBC Connection Pool Leaks Together
- Wrap
Connectionin outertry (...)andPreparedStatement/ResultSetin innertry (...). - Catch exceptions to call
conn.rollback()before rethrowing; outertryalways callsconn.close(). - Set
leak-detection-threshold: 5000,connection-timeout: 5000,maximum-pool-size: 10.
Interview Readiness Check
Set PostgreSQL JDBC socketTimeout=15 and connectTimeout=3 alongside maxLifetime=1500000 so dead TCP sockets don't hang threads forever, and isolate slow batch queries onto a separate pool.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Preventing JDBC Connection Pool Leaks
Enforce try-with-resources (or Spring JdbcTemplate/JPA) on every Connection, PreparedStatement, and ResultSet, enable leak-detection-threshold=5000, and cap maxLifetime below DB/NAT idle timeouts.
