Periodic Closed Socket Exception with Tomcat Oracle
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
nameremoveAbandoned/name
valuetrue/value
/parameter
parameter
nameremoveAbandonedTimeout/name
value300/value
/parameter
parameter
namelogAbandoned/name
valuetrue/value
/parameter
parameter
namefactory/name
valueorg.apache.commons.dbcp.BasicDataSourceFactory/value
/parameter
parameter
namedriverClassName/name
valueoracle.jdbc.driver.OracleDriver/value
/parameter
parameter
namevalidationQuery/name
valueselect * from dual/value
/parameter
parameter
nameusername/name
valueanyUser/value
/parameter
parameter
nameurl/name
valuejdbc:oracle:thin:@localhost:1521:reqmgr/value
/parameter
parameter
namepassword/name
valueanyPassword/value
/parameter
parameter
namemaxActive/name
value20/value
/parameter
parameter
namemaxWait/name
value-1/value
/parameter
parameter
namemaxIdle/name
value10/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
Periodic Closed Socket Exception with Tomcat Oracle
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
nameremoveAbandoned/name
valuetrue/value
/parameter
parameter
nameremoveAbandonedTimeout/name
value300/value
/parameter
parameter
namelogAbandoned/name
valuetrue/value
/parameter
parameter
namefactory/name
valueorg.apache.commons.dbcp.BasicDataSourceFactory/value
/parameter
parameter
namedriverClassName/name
valueoracle.jdbc.driver.OracleDriver/value
/parameter
parameter
namevalidationQuery/name
valueselect * from dual/value
/parameter
parameter
nameusername/name
valueanyUser/value
/parameter
parameter
nameurl/name
valuejdbc:oracle:thin:@localhost:1521:reqmgr/value
/parameter
parameter
namepassword/name
valueanyPassword/value
/parameter
parameter
namemaxActive/name
value20/value
/parameter
parameter
namemaxWait/name
value-1/value
/parameter
parameter
namemaxIdle/name
value10/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
