firstly, I check firewall and Linux Socket setup whether have error,common oracle SQLPLUS can work on this, so I think Linux and firewall setup is right.
secondly, I check the Java JDBC setup whether have error,so I write one java code with JDBC setup to check whether can reproduce the error, I got the same error.
thirdly, I check the JDBC configuration, I found the root cause, because we setup the query time-out time is 600(10 minutes) but we still setup the removeAbandoned="true" and removeAbandonedTimeout="60",setup wrong on this, why?
Let's introduce the detail reason to you.
removeAbandoned is (boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned The default value is false.
removeAbandonedTimeoutremoveAbandonedTimeout is (int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.
logAbandoned is logAbandoned(boolean) Flag to log stack traces for application code which abandoned a Connection. Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated. The default value is false.
because Tomcat JDBC pool will remove abandoned connections if they exceed the removeAbandonedTimeout, we setup the query time-out is 10 minutes and removeAbandonedTimeout=60(seconds), so Tomcat JDBC Pool remove abandoned connection by 60 seconds, but this connection still query the data, because our report database have many data on this, it always more than 60 seconds to get the result.
Java testing codes for Tomcat JDBC Pool:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.tomcat.jdbc.pool.DataSourceFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
public class TestJDBC {
private static String driver = "com.inet.ora.OraDriver";
private static String url = "jdbc:inetora:***?service=***&failover=true&host1=**&port1=1521&loginTimeout=10&queryTimeout=600";
private static String userName = "***";
private static String password = "pass";
private static DataSource ds = null;
public static void main(String[] args) throws Exception {
initDataSource();
String sql = "INSERT INTO ......";
Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
long beginM = System.currentTimeMillis();
System.out.println("Begin:" + beginM);
try {
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeCon(null, pstmt, con);
}
long endM = System.currentTimeMillis();
System.out.println("End:" + endM);
System.out.println("Total:" + (endM - beginM));
}
public static void initDataSource() throws Exception {
if (ds == null) {
System.out.println("???????:[driver:" + driver + ",url:" + url
+ ",userName:" + userName + ",password:" + password + "]");
PoolProperties p = new PoolProperties();
p.setDriverClassName(driver);
p.setUrl(url);
p.setUsername(userName);
p.setPassword(password);
p.setMaxActive(50);
p.setInitialSize(10);
p.setMaxIdle(10);
p.setMaxWait(10000);
p.setConnectionProperties("streamstolob=true");
p.setTestOnBorrow(true);
p.setTimeBetweenEvictionRunsMillis(300000);
p.setRemoveAbandonedTimeout(60);
p.setRemoveAbandoned(true);
p.setValidationQuery("SELECT instance_name FROM v$instance");
p.setLogAbandoned(true);
p.setValidationInterval(1000);
ds = new DataSource();
ds.setPoolProperties(p);
}
}
public static void shutDownDataSource() throws Exception {
if (ds != null) {
ds.close();
}
}
public static Connection getConnection() {
Connection con = null;
try {
con = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void closeCon(ResultSet rs, PreparedStatement ps,
Connection con) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
No comments:
Post a Comment