HikariCP连接池活跃连接无法释放问题调试排查

I’ve dealt with similar connection pool bottlenecks in Spring Boot/Hibernate stacks, so let’s walk through your questions with practical, actionable steps:

1. How to diagnose why active connections are stuck via Hibernate or Postgres logs? #

Hibernate Side #

• Turn up logging for org.hibernate.resource.jdbc to DEBUG level. This will log every step of connection acquisition, usage, and release—you’ll see exactly when connections are picked up and if they’re never returned to the pool.
• For even more detail, enable org.hibernate.engine.jdbc.spi.SqlStatementLogger at TRACE level to track which SQL statements are tied to each connection.

Postgres Side #

• First, run this query directly in Postgres—it’s my go-to for quick connection status checks:

  SELECT pid, usename, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE state != 'idle';
  

  This shows if connections are stuck running a long query (active state) or holding a transaction open without finishing (idle in transaction).
• Temporarily update postgresql.conf:
  • Set log_statement = 'all' to log every SQL statement hitting the database—this helps spot slow or problematic upsert logic.
  • Enable log_lock_waits = on and log_min_duration_statement = 0 to catch lock waits and all query durations.

2. If leak-detection-threshold=30s flags a leak, how to confirm Hibernate is the culprit? #

• When HikariCP logs a leak, it will include a full stack trace of where the connection was acquired. Scan this trace for Hibernate-specific classes (e.g., org.hibernate.Session, org.springframework.orm.jpa.JpaTransactionManager)—if the acquisition happens within Hibernate’s session/transaction lifecycle, that’s a red flag.
• Check your transaction boundaries:
  • Make sure @Transactional is applied correctly to service methods (not missing on critical operations, or using the wrong propagation behavior like REQUIRES_NEW unnecessarily).
  • Verify transactions are being committed or rolled back properly—unfinished transactions will hold connections open indefinitely.
• If you’re manually managing Hibernate sessions (instead of letting Spring handle it), double-check that you’re calling session.close() or sessionFactory.close() after use.

3. If it’s a database-level lock wait, how to find the root cause? #

• Use Postgres’s pg_locks view to map waiting queries to the locks holding them up:

  SELECT l.locktype, l.relation, l.pid, l.mode, l.granted, a.query
  FROM pg_locks l
  JOIN pg_stat_activity a ON l.pid = a.pid
  WHERE NOT l.granted;
  

  This will show you which query is waiting for a lock, and which other transaction is holding that lock.
• For your upsert operation, confirm you’re using Postgres’s native INSERT ... ON CONFLICT ... DO UPDATE instead of a separate SELECT + INSERT/UPDATE flow. The latter is race-prone and can lead to long-held row locks under concurrency.
• Watch for idle in transaction connections in pg_stat_activity—these are transactions that were started but never committed/rolled back, and they’re a common source of stuck locks and connections.

Update: Resolved Connection Leak #

With help from @brettw, a thread dump confirmed a connection leak. After checking HikariCP community discussions and Spring’s official JIRA, the root issue was Hibernate’s default connection handling mode holding connections longer than needed.

The fix was adding this property to application.properties:

spring.jpa.properties.hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION

This configures Hibernate to acquire connections only when required and release them immediately after the transaction finishes, eliminating unnecessary connection retention.

A critical reminder: Never mask connection leaks by increasing the pool size—this only delays the problem and can lead to database exhaustion or degraded performance under sustained load.

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