Introduction

HikariCP is the default connection pool in Spring Boot. It maintains a fixed pool of database connections. When all connections are in use and a new request needs a connection, it waits up to connectionTimeout milliseconds. If no connection becomes available, it throws SQLTransientConnectionException. This happens when queries are slow, connections are leaked (not closed), or the pool is undersized for the traffic.

Symptoms

  • java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms
  • HikariPool-1 - Timeout after 30000ms of waiting for a connection
  • Requests queue up and timeout during traffic spikes
  • Database connections all in STATE_SLEEPING or STATE_IN_USE
  • pool stats: total=20, active=20, idle=0, waiting=15

``` 2024-01-15 10:30:00.123 ERROR --- [nio-8080-exec-25] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool creation

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms. at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197) ```

Common Causes

  • Pool size too small for concurrent request volume
  • Slow database queries holding connections too long
  • Connection leaks - connections not returned to pool
  • Long-running transactions blocking other connections
  • Network latency between application and database

Step-by-Step Fix

  1. 1.Enable connection leak detection:
  2. 2.```properties
  3. 3.# application.properties
  4. 4.# If a connection is held longer than this, log a warning
  5. 5.spring.datasource.hikari.leak-detection-threshold=60000

# This logs the stacktrace where the connection was acquired # helping you find the code that is not closing it ```

  1. 1.Tune pool size based on workload:
  2. 2.```properties
  3. 3.# HikariCP recommends: pool_size = cpu_cores * 2 + effective_spindle_count
  4. 4.# For SSD/NVMe: pool_size = cpu_cores * 2
  5. 5.# For traditional disks: pool_size = cpu_cores * 2 + disk_count

spring.datasource.hikari.maximum-pool-size=20 spring.datasource.hikari.minimum-idle=10 spring.datasource.hikari.connection-timeout=10000 spring.datasource.hikari.idle-timeout=300000 spring.datasource.hikari.max-lifetime=1200000

# Increase timeout if database is slow spring.datasource.hikari.connection-timeout=30000 ```

  1. 1.Use try-with-resources to prevent connection leaks:
  2. 2.```java
  3. 3.// WRONG - connection not closed on exception
  4. 4.public User findUser(Long id) throws SQLException {
  5. 5.Connection conn = dataSource.getConnection();
  6. 6.PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
  7. 7.ps.setLong(1, id);
  8. 8.ResultSet rs = ps.executeQuery();
  9. 9.// If exception occurs here, conn is never returned to pool!
  10. 10.return mapUser(rs);
  11. 11.}

// CORRECT - try-with-resources ensures cleanup public User findUser(Long id) throws SQLException { String sql = "SELECT * FROM users WHERE id = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setLong(1, id); try (ResultSet rs = ps.executeQuery()) { return rs.next() ? mapUser(rs) : null; } } } ```

  1. 1.Monitor pool metrics in production:
  2. 2.```java
  3. 3.import com.zaxxer.hikari.HikariDataSource;
  4. 4.import io.micrometer.core.instrument.MeterRegistry;

@Component public class PoolMetrics {

private final HikariDataSource dataSource;

public PoolMetrics(HikariDataSource dataSource, MeterRegistry registry) { this.dataSource = dataSource;

registry.gauge("hikari.pool.active", dataSource, ds -> ds.getHikariPoolMXBean().getActiveConnections()); registry.gauge("hikari.pool.idle", dataSource, ds -> ds.getHikariPoolMXBean().getIdleConnections()); registry.gauge("hikari.pool.total", dataSource, ds -> ds.getHikariPoolMXBean().getTotalConnections()); registry.gauge("hikari.pool.waiting", dataSource, ds -> ds.getHikariPoolMXBean().getThreadsAwaitingConnection()); } } ```

  1. 1.Set database-side connection limits:
  2. 2.```sql
  3. 3.-- PostgreSQL: check max connections
  4. 4.SHOW max_connections; -- Default: 100

-- Check current connections SELECT count(*) FROM pg_stat_activity;

-- Ensure Hikari pool_size * number_of_app_instances < max_connections -- E.g., 20 connections * 5 instances = 100 = max_connections (too tight!)

-- Increase if needed (requires restart) -- ALTER SYSTEM SET max_connections = 200; ```

Prevention

  • Set leak-detection-threshold to catch connection leaks in development
  • Use Spring's @Transactional to manage connections automatically
  • Monitor threadsAwaitingConnection metric - alert when > 0
  • Size the pool based on load testing, not guesses
  • Use max-lifetime to prevent stale connections (should be less than database timeout)
  • Set connection-timeout lower than your HTTP request timeout
  • In Kubernetes, account for connection count: pool_size * pod_count < db_max_connections