While reading “Release It!” by Michael T. Nygard, I came across the following line that had me pause:
“The IP address used to create the connection will have moved from one host to another, but the current state of TCP connections will not carry over to the second database host.”
Got me thinking about my understanding of database connections and in TCP/IP fundamentals in general.
I’ve connected to databases a bunch of times, but I never really took the time to think about what’s happening under the hood. A database failover scenario (where the database host switches) underscores the stateful nature of TCP connections. Everything–source/destination IP, port #, sequence #s for data packets–must be re-established.
The code opening the db connection did not catch exceptions that would be thrown if a sql statement was executed with a closed/invalid connection.
High level image of the issue may be something like:
If the code you call can throw an exception, you better be handling it ;D
Learning for me is to think about the following:
- checking if the connection is valid before using it (ie.
conn.isValid()) - setting connection timeouts (ie.
conn.setConnectionTimeout) - periodically refreshing the resource pool (ie. perhaps the db framework itself has API that can do this)