Why pool
Opening a database connection is expensive. A connection pool keeps a fixed number of live connections that threads borrow when needed and return when done, reusing them instead of reconnecting.
The bounded resource pattern
A pool is a bounded resource problem. It needs:
- A counting semaphore initialised to the pool size, so a thread blocks when no connection is free.
- A thread safe collection of idle connections guarded by a mutex.
- A clear return path so a borrowed connection always goes back, even on error.
The classic pitfalls
- Leaks happen when a thread forgets to return a connection on an error path, slowly draining the pool until everyone blocks forever. Always return in a finally style block.
- Validation matters because a pooled connection may have gone stale, so the pool tests or recycles it before handing it out.
- Timeouts prevent indefinite blocking when the pool is exhausted, surfacing backpressure to the caller instead of hanging.
Key idea
A connection pool is a bounded resource guarded by a counting semaphore where every borrow must be returned, and leaks timeouts and validation are the main hazards.