Fix database connection pool starvation — real cause & fix

You're running a standard CRUD app with HikariCP (or Tomcat JDBC pool) on a Spring Boot backend. Everything works fine for weeks. Then one day, at 10:15 AM with 200 concurrent users, the app goes completely dark. Requests time out after 30 seconds. The database CPU is at 30% — not even maxed out. You check the pool metrics: all 50 connections are active, waiting time is 45 seconds, and the pool is rejecting new ones with Connection is not available, request timed out after 30001ms.

Your pool is starved. But the database itself isn't under heavy load. So what's actually holding all those connections?

Root cause in plain English

A connection pool works like a taxi fleet. You have 50 taxis (connections) that drive to the database, do a trip (execute a query), and come back to the pool. When every taxi is out on a trip that takes 10 minutes instead of 10 milliseconds, the fleet runs out. No cab for anyone.

The core problem is long-running queries that hold connections open. These can be:

A single slow query that scans millions of rows (no index, bad join)
A transaction that's doing multiple queries and waiting on user input (like a batch update that locks rows for seconds)
A connection leak — code that opens a connection but never closes it, even if the query finishes
Deadlocks or lock waits where two transactions fight over the same rows

The reason the pool gets stuck is not the database being overloaded. It's a few connections grabbing all the resources because they're not giving them back fast enough. That's the key difference — and why just adding more connections to the pool won't fix it.

The fix — 4 steps, not 1

Skip adding more connections first. That just delays the crash. Do this instead.

Step 1: Find the slow queries or locks right now

Run this on MySQL (8.x) while the outage is happening:

SELECT * FROM performance_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 20;

For PostgreSQL (14+):

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 20;

Look for queries with time over 5 seconds. That's your culprit. If you see the same query pattern repeated (like an UPDATE ... WHERE missing an index), you've found the leaky pipe.

Step 2: Kill the stuck queries

If the app is already down, kill the longest-running queries to free up connections immediately. In MySQL:

KILL [thread_id];

In PostgreSQL:

SELECT pg_terminate_backend([pid]);

This works because killing a query releases the connection back to the pool. The app will start responding again within seconds. But it's a band-aid — you need to fix why those queries ran slow.

Step 3: Fix the slow query or transaction

Use EXPLAIN on the offending query. If you see a full table scan (no index used), add an index. Real example from a recent fix:

EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND created_at < NOW() - INTERVAL 7 DAY;

The explain showed a sequential scan on 2 million rows. Adding a composite index on (status, created_at) dropped the query from 12 seconds to 40ms. Connection pool went from 50 active connections to 3. No more starvation.

If the issue is a long transaction that's waiting on user input (like a web form that opens a transaction and then waits for the user to click submit), that's a design bug. Move the database work into the same request, don't hold a transaction across a network round trip.

Step 4: Set a connection timeout on the pool

This prevents a single slow query from killing the entire app. In HikariCP, set connectionTimeout to something sane like 5000ms (5 seconds). If a query takes longer, the pool throws an error instead of blocking everything. In application.properties:

spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=600000

Also add a validationTimeout of 2000ms so the pool can detect dead connections quickly:

spring.datasource.hikari.validation-timeout=2000

What to check if it still fails

Connection leaks in code: Look for try-with-resources blocks that are missing a finally close. Or use a tool like p6spy to log every connection open/close. I've seen apps where a PreparedStatement was never closed — 200 connections leaked per minute.
Database server max connections: If your pool size is 50 but the database max_connections is 100, another service could eat the rest. Check SHOW VARIABLES LIKE 'max_connections' and make sure your pool + other apps don't exceed 80% of that.
Network latency: If the database is on a different availability zone or region, network latency adds up. Each connection handshake takes 10ms. With 50 connections, that's 500ms of overhead per second. Not your main problem, but it can make things worse.
Deadlock detection: If the pool shows connections stuck on INNODB_LOCK_WAITS, you've got a deadlock. Run SHOW ENGINE INNODB STATUS and look for the LATEST DETECTED DEADLOCK section. Fix the transaction order in your code.

Connection pool starvation is almost always a symptom of bad queries or transaction design. The pool itself is fine. Tune the queries, set timeouts, and your app will breathe again.