Connection Validation and Stale Connections
What You'll Learn
You'll learn why pooled database connections can become invalid and how to test them before use to prevent application errors.
The Problem with Idle Connections
When a database connection sits idle in a pool, several things can go wrong:
- Network failures can sever the underlying TCP connection
- Database restarts invalidate all existing connections
- Firewall timeouts may close connections after periods of inactivity
- Database-side connection limits might force-close old connections
The application, however, still thinks these connections are perfectly fine because they're sitting in the pool. When your code grabs one and tries to execute a query, it fails unexpectedly.
Connection Validation
Connection validation is the practice of testing a connection before handing it to application code. Think of it like checking if milk is still fresh before pouring it into your coffee—you don't want to discover it's spoiled mid-sip.
Common Validation Approaches
Test-on-borrow: Execute a lightweight query (like SELECT 1) before giving the connection to the application. This catches stale connections immediately but adds a small latency cost.
Test-while-idle: Periodically check idle connections in the background. This prevents validation delays during actual requests but may not catch very recent failures.
Test-on-return: Validate when returning a connection to the pool, marking bad ones for removal.
Real-World Impact
Without validation, your application might fail intermittently—especially after quiet periods when connections have been idle longest. With validation, the pool automatically discards broken connections and creates fresh ones.
Key Takeaway: Connection validation prevents stale connections from causing query failures by testing connections before use or during idle periods, ensuring your application always receives working database connections.