Periodic Closed Socket Exception with Tomcat & Oracle

[Tristan Stephens <tr...@thiri.com.au>]

Hi
I am using Tomcat 4.1 with Oracle 9i as the datasource. I can connect and
use the database just fine for a few hours, but then I always get a "IO
Exception: Socket Closed Error". After this point because the socket is
closed, I can no longer access the database, and can't even reconnect to it.
The only way to fix the problem is to restart Tomcat. Since it's a small app
that I'm running, and not many users connect simultaneously, I rely on
connections that last the duration of a user session (they close if the
session times out after 5 minutes), rather than connecting/disconnecting per
call. 
I've been looking at this problem for a while, and have tried the following
solutions:
1. Putting in a verification string. I assumed that if the validation fails,
Tomcat tries to reopen the socket.
2. Using best practices with the database connections, statements and result
sets - the connections are pooled (and use a pooled datasource driver), I
close the statements and result sets after each transaction in a finally 
 clause.
3. Using the BasicDataSourceFactory (for pooling connections), rather than
Tomcat's default Datasource factory
4. Getting Tomcat to clean up abandoned database connections.
 
Here are the essentials of my server.xml file:
 
  <GlobalNamingResources>
    <Resource name="oracle92" scope="Shareable" auth="Container"
type="javax.sql.DataSource"/>
 
    <ResourceParams name="oracle92">
              <parameter>
        <name>removeAbandoned</name>
                        <value>true</value>
              </parameter>
              <parameter>
        <name>removeAbandonedTimeout</name>
        <value>300</value>
              </parameter>
      <parameter>
        <name>logAbandoned</name>
        <value>true</value>
              </parameter>
              <parameter>
        <name>factory</name>
        <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
              <parameter>
        <name>driverClassName</name>
        <value>oracle.jdbc.driver.OracleDriver</value>
      </parameter>
              <parameter>
        <name>validationQuery</name>
        <value>select * from dual</value>
      </parameter>
      <parameter>
        <name>username</name>
        <value>anyUser</value>
      </parameter>
      <parameter>
        <name>url</name>
        <value>jdbc:oracle:thin:@localhost:1521:reqmgr</value>
      </parameter>
      <parameter>
        <name>password</name>
        <value>anyPassword</value>
      </parameter>
      <parameter>
        <name>maxActive</name>
        <value>20</value>
      </parameter>
      <parameter>
        <name>maxWait</name>
        <value>-1</value>
      </parameter>
      <parameter>
        <name>maxIdle</name>
        <value>10</value>
      </parameter>
    </ResourceParams>
            ...
  <Service className="org.apache.catalina.core.StandardService" debug="0"
name="Java Web Services Developer Pack">
                <Engine className="org.apache.catalina.core.StandardEngine"
debug="0" defaultHost="localhost"
mapperClass="org.apache.catalina.core.StandardEngineMapper" name="Standard
Engine">
            <Context className="org.apache.catalina.core.StandardContext"
cachingAllowed="true"
charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true"
crossContext="false" debug="0" displayName="RequirementsManager"
docBase="p:\RequirementsManager\build"
mapperClass="org.apache.catalina.core.StandardContextMapper"
path="/RequirementsManager" privileged="false" reloadable="false"
useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
                      <ResourceLink global="oracle92" name="jdbc/reqmgr"/>
            </Context>
            ...
 
The connection code I use is:
                        private Connection connection;
                        private String dbName = "java:comp/env/jdbc/reqmgr";
                        
                        public void connect() throws SQLException,
NamingException
                        {
                                    InitialContext ic = new
InitialContext();
                                    System.out.println("Begin initial
context lookup");
                                    DataSource ds = (DataSource)
ic.lookup(dbName);
                                    System.out.println("ic.lookup ok. Begin
datasource.getConnection()");
                                    System.out.println("Datasource: "+ds);
                                    connection = ds.getConnection();
                                    System.out.println("ds.getConnection()
ok");
 
                                    // set transaction isolation level
                                    // doesn't work in Oracle -
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
 
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                                    
                        }
 
After a few hours, I get the Socket Closed Exception at:
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
 
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
If anyone could help me with this problem, and explain why this happens, and
a way to solve it, I'd be very appreciative
 
Tristan Stephens