Talk about jdbsockettimeout settings.

  jdbc

Order

This article mainly introduces the setting of socket timeout in jdbc.

Jdbtimeout class

There are mainly the following categories

  • transaction timeout

The setting is the execution time of a transaction, which may contain multiple statement.

  • statement timeout(It is also equivalent to result set fetch timeout.)

The setting is the timeout period for the execution of a statement, that is, the timeout period for the driver to wait for the completion of the execution of the statement and receive data (Note that the timeout of the statement is not the timeout of the entire query, but the timeout returned by completing the execution of the statement and pulling fetchSize data. after that, the next of resultSet will trigger fetch data when necessary. the timeout of each fetch is calculated separately, and the default is also the timeout set by the statement.)

  • jdbc socket timeout

Set the timeout time of JDBC I/O Socket Read and Write Operations to prevent the driver from blocking waiting all the time due to network problems or database problems. (It is recommended to take longer than the statement timeout.)

  • os socket timeout

This is the operating system level socket setting (If jdbc socket timeout is not set and os-level socket timeout is set, the system's socket timeout value is used.)。

The top different levels of timeout have higher priority as they go down, that is to say, if the following configuration is smaller than the above configuration value, timeout will be triggered preferentially, which is equivalent to the above configuration value being “invalid”.

jdbc socket timeout

The jdbc driver implementations for this different data are different.

mysql

jdbc:mysql://localhost:3306/ag_admin?useUnicode=true&characterEncoding=UTF8&connectTimeout=60000&socketTimeout=60000

Pass through url parameters

pg

jdbc:postgresql://localhost/test?user=fred&password=secret&&connectTimeout=60&socketTimeout=60

Pg is also passed through url, but its unit is different from mysql, mysql is milliseconds, while pg is seconds

oracle

Oracle needs to be set through the oracle.jdbc.ReadTimeout parameter. the connection timeout parameter is oracle.net.CONNECT_TIMEOUT

  • Settings via properties
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Properties props = new Properties() ;
            props.put( "user" , "test_schema") ;
            props.put( "password" , "pwd") ;
            props.put( "oracle.net.CONNECT_TIMEOUT" , "10000000") ;
            props.put( "oracle.jdbc.ReadTimeout" , "2000" ) ;
            Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@10.0.1.9:1521:orcl" , props ) ;
  • Through environment variable setting
String readTimeout = "10000"; // ms
System.setProperty("oracle.jdbc.ReadTimeout", readTimeout);
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection(jdbcUrl, user, pwd);

Note that environment variables need to be set before the connection connection

  • tomcat jdbc pool

In general, we do not directly use jdbc connection, but use connection pool. Since tomcat jdbc pool is the default database connection pool used by springboot, here’s how to set it up under tomcat jdbc pool.

spring.datasource.tomcat.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000

Note that here are semicolons separated and the unit is milliseconds. Prefixes (Tomcat jdbc connection pool, the default is spring.datasource.tomcat), can be customized, such as

    @Bean
    @Qualifier("writeDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }

Assuming that you have customized prefix to spring.datasource.write here, the above configuration will become

spring.datasource.write.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000

If oracle.jdbc.ReadTimeout is not set, the default value in driver is 0

oracle.jdbc.ReadTimeout

Driver internally sets this value to the oracle.net.READ_TIMEOUT variable

  • oracle.net.nt.TcpNTAdapter
    @Override
    public void setReadTimeoutIfRequired(final Properties properties) throws IOException, NetException {
        String s = ((Hashtable<K, String>)properties).get("oracle.net.READ_TIMEOUT");
        if (s == null) {
            s = "0";
        }
        this.setOption(3, s);
    }
    
    public void setOption(int var1, Object var2) throws IOException, NetException {
        String var3;
        switch(var1) {
        case 0:
            var3 = (String)var2;
            this.socket.setTcpNoDelay(var3.equals("YES"));
            break;
        case 1:
            var3 = (String)var2;
            if(var3.equals("YES")) {
                this.socket.setKeepAlive(true);
            }
        case 2:
        default:
            break;
        case 3:
            this.sockTimeout = Integer.parseInt((String)var2);
            this.socket.setSoTimeout(this.sockTimeout);
        }

    }

The last thing you can see is socket’s soTimeout.

Example

    @Test
    public void testReadTimeout() throws SQLException {
        Connection connection = dataSource.getConnection();
        String sql = "select * from demo_table";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getObject(i));
                }
                System.out.println("");
            }
            System.out.println("============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //close resources
        }
    }

Timeout error output

//部分数据输出......
java.sql.SQLRecoverableException: IO 错误: Socket read timed out
    at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1128)
    at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:373)
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:277)
    at com.example.demo.DemoApplicationTests.testReadTimeout(DemoApplicationTests.java:68)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: oracle.net.ns.NetException: Socket read timed out
    at oracle.net.ns.Packet.receive(Packet.java:339)
    at oracle.net.ns.DataPacket.receive(DataPacket.java:106)
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:102)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1119)
    ... 35 more

At first there will be data output, but when it comes to the next of a resultSet, a timeout is reported (close_or_fetch_from_next), this timeout specifies that when the result.next method triggers the pull of a new batch of data (When one fetchSize data is consumed, the following next will trigger fetch of a new batch of dataAfter that, no data returned by the database was received within the timeout time return.

Oracle’s jdbc default fetchSize is 10, that is, each fetch throws a timeout exception if no data is received after the specified time.

Summary

The setting of jdbc socketTimeout value should be very careful. jdbc driver settings are different for different databases, especially if different connection pools are used, the settings may also be different. For services that rely heavily on database operations, it is very necessary to set this value, otherwise in case of network or database exceptions, service threads will always be blocked in Java. net.socketinputstream.socketread0.

  • If there is more data to be queried, the data list held by the thread cannot be released, which is equivalent to memory leakage and finally leads to OOM
  • If the requested database has many operations and is blocked, it will result in fewer woker threads available to the server, and in serious cases, the service will be unavailable. nginx reports 504 Gateway Timeout

doc