When concurrently running simple select query on OracleDb using hikari pool I get socket timeout exception. Is there anything that could help? Below is sample code which produces the exception.
Only thing that did help was reducing the number of threads to 2 and setting some time limit but that's not the solution. I've found some info on similar bag from 2014 but it was fixed in later versions of hikari. Java 8.
Or I wonder if it has something to do with querying the database too often? I checked active connections and cursors but they are below max limit. I guess DB should handle such queries.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class Overflow {
public static void main(String[] args) throws Exception {
for (int i = 0; i < 10; i ++) {
new Thread(new Runnable() {
@Override
public void run() {
try {
runQueries();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
}
}
private static void runQueries() throws SQLException {
for (int i = 0; i < 10000; i++) {
Connection con = DbConnection.getConnection();
querySelect(con);
}
}
private static void querySelect(Connection con) throws SQLException {
PreparedStatement st = con.prepareStatement("select 1 from dual");
st.execute();
System.out.println("done");
con.close();
}
}
class DbConnection {
private static HikariConfig config = new HikariConfig("hikkari.properties");
private static HikariDataSource ds = new HikariDataSource(config);
private DbConnection() {}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
}
Hikkari settings:
jdbcUrl=jdbc:oracle:thin:@//adress:1521/db
dataSource.user=user
dataSource.password=password
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048
dataSource.connectionTimeout=10000
dataSource.maxLifetime=45000
#dataSource.readOnly=true
autoCommit=true
maximumPoolSize=8
#connectionInitSql=select 1 from dual
After some time this code produces socket timeout exception:
2018-09-05 16:17:41,990 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=8, active=8, idle=0, waiting=2)
2018-09-05 16:17:42,708 [Thread-9] WARN c.z.hikari.pool.ProxyConnection - HikariPool-1 - Connection oracle.jdbc.driver.T4CConnection@79b0cad1 marked as broken because of SQLSTATE(08006), ErrorCode(17002)
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:899)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at Overflow.querySelect(Overflow.java:37)
at Overflow.runQueries(Overflow.java:31)
at Overflow.access$0(Overflow.java:28)
at Overflow$1.run(Overflow.java:19)
at java.lang.Thread.run(Unknown Source)
Oracle and hikari versions:
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.2.0</version>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4</version>