Requests in a service start failing with timeouts while waiting to obtain a database connection, but the database server itself shows low CPU and I/O. How would you diagnose and fix this?
answer
- Pool saturated + idle server = held, not busy
- Check: leaks, remote calls inside txn, idle-in-transaction
- Little's Law: slower query = proportionally more connections
- Blocking chains hide as low CPU
- Bigger pool last, after guardrails and query fixes
basics
~20 sConnections are being held rather than used. Look for leaked connections never returned, transactions left open across slow non-database work, long or unindexed queries, and lock waits. Fix the holder; enlarging the pool only multiplies held connections and pushes contention into the database.
solid answer
~60 sAn idle database with a saturated pool means connections are **held, not busy**. Work through the causes: 1. **Leaks** - a code path that borrows a connection and never returns it on an error path. Pool leak-detection thresholds and a steadily falling idle count with no matching load point here. 2. **Connections held across non-database work** - acquiring at request start and keeping it through remote API calls, retries, or user interaction. Borrow late, release early. 3. **Long transactions** - visible server-side as sessions *idle in transaction*, which also hold locks and block version cleanup. 4. **Slow statements** - a missing index or a plan change raises service time, so Little's Law says required concurrency rises proportionally. 5. **Lock waits** - sessions active but blocked on each other. 6. **Sizing/topology** - pool far too small for legitimate concurrency, or one pool shared by fast OLTP and slow reports. Diagnose by correlating pool metrics (active, idle, pending, acquisition wait time) with server-side session states. Fix the holder, add per-workload pools and statement timeouts, and only then reconsider size.
code
text · 6 linespool: total=20 active=20 idle=0 pending=87 avg_acquire_wait=30s
server: active=1 idle=3 idle_in_transaction=16 oldest_txn_age=00:14:22
cpu: 6% disk: idle
=> connections are checked out but not executing: transactions opened and
left open across non-database work. Fix the holder, not the pool size.go deeper
Recognise that the error means all pooled connections were checked out, and that the usual cause is code holding a connection instead of the database being slow.
Enumerate the causes - leaks, remote calls inside transactions, slow queries, lock waits - and connect slower queries to proportionally higher connection demand.
Drive the diagnosis by correlating pool metrics with server session states, apply the fix hierarchy, and add guardrails such as statement and idle-in-transaction timeouts.
Set the standards that prevent recurrence: bulkheaded pools per workload, mandated timeouts and leak detection, transaction-scope rules in service design, and a fleet connection budget reviewed with autoscaling.
## Read the symptom precisely "Timed out waiting for a connection" is an *application-side* error: every pooled connection was checked out for longer than the acquisition timeout. Combined with an idle database, it rules out the obvious explanation (the database is overloaded) and points at connections that are checked out but not doing database work. ## The instrumentation you need From the pool: total, active (checked out), idle, threads pending, and the distribution of acquisition wait time and connection-usage time. From the server: the session list classified as *active*, *idle*, and *idle in transaction*, the age of the oldest transaction, and current lock waits. The pairing is what makes the diagnosis quick - for instance, active-in-pool but idle-on-server means the application is holding connections without using them. ## Cause 1 - leaked connections A path that borrows a connection and misses the release, typically on an exception branch or in code that does not use the language's automatic resource management. The signature is a monotonic decline in available connections that does not recover when traffic drops, and the fix is structural: always release in a finally-equivalent block, prefer framework-managed transactions, and enable the pool's leak-detection threshold so a stack trace is logged for connections held beyond a sane limit. ## Cause 2 - connections held across non-database work The most common cause in service code. A request acquires a connection early, then makes an HTTP call to another service, waits on a retry, serialises a large response, or - worst - waits for user input, all while holding the connection. The database is idle by definition during that time. Fixes: acquire immediately before the database work and release immediately after; never wrap remote calls inside a database transaction; and if a workflow genuinely spans external calls, split it into separate short transactions with idempotency rather than one long one. ## Cause 3 - long or abandoned transactions Server-side *idle in transaction* sessions are the fingerprint. They hold locks and keep an old snapshot alive, which blocks cleanup of dead row versions and degrades the whole server later even though CPU looks fine now. Guardrails: a server-side idle-in-transaction timeout, a statement timeout, and code review discipline that a transaction contains only database work. ## Cause 4 - genuinely slow statements Here the connections *are* busy, but each one for far too long - a dropped index, a plan flip after statistics changed, or a query whose data volume grew. Since required concurrency equals arrival rate times service time, a query going from 3 ms to 300 ms multiplies connection demand a hundredfold. CPU can still look modest if the queries are I/O-bound or waiting. The fix is the query, not the pool. ## Cause 5 - lock contention Sessions are active on the server but blocked behind one another. Total resource use stays low while everything waits. Inspect blocking chains, find the head of the chain, and address the pattern - hot row updates, wide-range locks, unindexed foreign-key checks causing extra locking, or transactions taking locks in inconsistent order. ## Cause 6 - sizing and topology Only after the above: the pool may simply be too small for legitimate concurrency, or - very commonly - one pool serves both millisecond OLTP queries and multi-second reports, so a handful of reports starve everything else. Separate pools per workload class, possibly pointing reporting at a replica, fixes the interference. Autoscaling can also invert the problem: instance count grew, per-instance pools stayed the same, and the *server* cap is now the binding constraint rather than the pool. ## The fix hierarchy 1. Stop connections being held while no database work happens (leaks, remote calls inside transactions, idle-in-transaction). 2. Reduce service time (indexes, plans, batch sizes). 3. Bulkhead by workload with separate pools and per-pool timeouts. 4. Add server-side guardrails: statement timeout, idle-in-transaction timeout, leak detection. 5. Only then adjust pool size, re-derived from measured concurrency and checked against the fleet's total against the server cap. ## Why "just make the pool bigger" is dangerous If connections are held rather than busy, a bigger pool simply holds more of them and fails a little later. If they are busy because queries are slow, a bigger pool pushes more concurrent slow work into the database, where contention and memory pressure make every query slower - converting a partial outage into a total one. Enlarging the pool is a legitimate action only when the database has spare CPU and I/O *and* connections are genuinely executing useful, already-optimised work.
- Which server-side session state most strongly indicates an application bug rather than a capacity problem, and why?Sessions sitting *idle in transaction*. They have opened a transaction and then stopped doing database work, which almost always means application logic - a remote call, a retry, or user interaction - runs inside the transaction. Beyond occupying a pool slot, they hold locks and keep an old snapshot alive, blocking cleanup of dead row versions and degrading the whole server. An idle-in-transaction timeout is the standard server-side guardrail.
- How do pool leak detection and maximum lifetime differ in what they protect against?Leak detection flags connections checked out by the application for longer than a threshold and logs the borrowing stack trace, catching code paths that never return a connection. Maximum lifetime retires healthy connections after a period so that failovers, DNS changes, and rotated credentials take effect, and it bounds slow per-session resource growth. One is an application-bug detector, the other is routine hygiene against stale sessions.
- When is increasing the pool size actually the right fix?When the connections are genuinely executing useful, already-tuned database work, the database still has spare CPU and I/O headroom, and the fleet total plus other clients remains comfortably under the server's connection cap. In that situation the pool is an artificial bottleneck. Even then the increase should be modest and validated by load testing, because throughput stops improving - and latency starts degrading - once the database's real concurrency limit is reached.
Every checkout till is occupied but no one is scanning items: the queue is not caused by too few tills, it is caused by customers standing at the till doing something else.
saying these in an interview costs you the question
- Raising the pool maximum as the first response to acquisition timeouts
- Ignoring idle-in-transaction sessions because CPU looks fine
- Holding a connection across HTTP calls or retries inside a transaction
- Sharing one pool between fast transactional queries and long reports
- Assuming garbage collection will return a leaked connection to the pool