Wednesday, October 15, 2003 07:18 AM
You need to make the connection pool test for bad connections and throw them out and make new ones. This is different from the "abandoned" connections where a client just forgets to close it. This is the server side closing the physical connection out from underneath the connection pool, as you apparently are experiencing? (once your socket connection times out, presumably that jdbc physical connection is dead and needs to be weeded out from the pool)
I think this depends on the db connection pool vendor. I recall seeing somewhere in some source code, maybe with tomcat? that there is even a definition of a "ping query" e.g. something like "select sysdate from dual" which the connection pool manager should periodically do, to make sure that physical connections in the pool are still alive? or it can perform this check before giving out a connection, or perform it upon returning a connection to the pool, instead of doing it periodically?
In the Tomcat documentation now for 4.X, it mentions that for mySQL, you add "autoreconnect=true" to the JDBC URL. It doesn't say what to do for Oracle. But maybe there is more in the commons dbcp doc? if that is what you are using? etc.
In our experience the Oracle "classes12" connection pool ("OracleConnectionCacheImpl") does not know how to check for these bad/dead physical connections (e.g. overnight our db server closes idle connections, but the connection pool doesn't know they are closed, and continues to blithely manage them in the pool, giving them to clients through getConnection, causing exceptions to be thrown).
We had to wrap it (oracle's PooledConnectionCacheImpl, or whatever it's named) to catch the exceptions ourselves, toss out the whole connection pool, and start a new one if necessary. Search the jguru archives for a bunch more details I've posted in the past, including the wrapper code.
But this prevents us from letting the app server manage the connection pool as a DataSource. I don't know how other people can use Oracle classes12 successfully in this manner, unless maybe their db server doesn't close idle connections the way ours does? maybe their apps are busy enough that their connections don't go idle? anyway I've never found any documentation on this, not even in Oracle OTN or MetaLink.
I am just about to test Oracle's more recent jdbc driver, in fact, to make sure that it has fixed this problem so it can be used as a server-managed pool, instead of needing our own custom wrapper.