内容简介:聊聊jdbc socketTimeout的设置
主要有如下几个类别
- transaction timeout
设置的是一个事务的执行时间,里头可能包含多个statement
- statement timeout(
也相当于result set fetch timeout
)
设置的是一个statement的执行超时时间,即driver等待statement执行完成,接收到数据的超时时间( 注意statement的timeout不是整个查询的timeout,只是statement执行完成并拉取fetchSize数据返回的超时,之后resultSet的next在必要的时候还会触发fetch数据,每次fetch的超时时间是单独算的,默认也是以statement设置的timeout为准
)
- jdbc socket timeout
设置的是jdbc I/O socket read and write operations的超时时间,防止因网络问题或数据库问题,导致driver一直阻塞等待。( 建议比statement timeout的时间长
)
- os socket timeout
这个是操作系统级别的socket设置( 如果jdbc socket timeout没有设置,而os级别的socket timeout有设置,则使用系统的socket timeout值
)。
上面的不同级别的timeout越往下优先级越高,也就是说如果下面的配置比上面的配置值小的话,则会优先触发timeout,那么相当于上面的配置值就"失效"了。
jdbc socket timeout
这个不同数据的jdbc driver实现不一样
mysql
jdbc:mysql://localhost:3306/ag_admin?useUnicode=true&characterEncoding=UTF8&connectTimeout=60000&socketTimeout=60000
通过url参数传递即可
pg
jdbc:postgresql://localhost/test?user=fred&password=secret&&connectTimeout=60&socketTimeout=60
pg也是通过url传递,不过它的单位与 mysql 不同,mysql是毫秒,而pg是秒
oracle
oracle需要通过oracle.jdbc.ReadTimeout参数来设置,连接超时参数是oracle.net.CONNECT_TIMEOUT
- 通过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 ) ;
- 通过环境变量设置
String readTimeout = "10000"; // ms System.setProperty("oracle.jdbc.ReadTimeout", readTimeout); Class.forName("oracle.jdbc.OracleDriver"); Connection conn = DriverManager.getConnection(jdbcUrl, user, pwd);
注意需要在connection连接之前设置环境变量
- tomcat jdbc pool
一般我们不直接使用jdbc connection,而是使用连接池。由于tomcat jdbc pool是springboot默认使用的数据库连接池,这里就讲述一下如何在tomcat jdbc pool下设置。
spring.datasource.tomcat.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
注意,这里是分号分隔,单位是毫秒,这里可以根据各自的情况配置前缀( tomcat jdbc连接池的话,默认是spring.datasource.tomcat
),可以自定义,比如
@Bean @Qualifier("writeDataSource") @ConfigurationProperties(prefix = "spring.datasource.write") public DataSource writeDataSource() { return DataSourceBuilder.create().build(); }
假设你这里是自定义了prefix为spring.datasource.write,那么上述配置就变为
spring.datasource.write.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
oracle.jdbc.ReadTimeout如果没有设置的话,driver里头默认是0
oracle.jdbc.ReadTimeout
driver内部将该值设置到oracle.net.READ_TIMEOUT变量上
- 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); } }
可用看到最后设置的是socket的soTimeout
实例
@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 } }
超时错误输出
//部分数据输出...... 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
刚开始会有数据输出,但是到了某个resultSet的next的时候,报了超时( close_or_fetch_from_next
),这个超时指定的是当result.next方法触发新的一批数据的拉取( 当一个fetchSize的数据消费完之后,接下来的next会触发新一批数据的fetch
)之后在timeout时间返回内没有收到数据库返回的数据。
oracle的jdbc默认的fetchSize为10,也就是每个fetch,如果超过指定时间没接收到数据,则抛出timeout异常。
小结
jdbc的socketTimeout值的设置要非常小心,不同数据库的jdbc driver设置不一样,特别是使用不同连接池的话,设置也可能不尽相同。对于严重依赖数据库操作的服务来说,非常有必要设置这个值,否则万一网络或数据库异常,会导致服务线程一直阻塞在java.net.SocketInputStream.socketRead0。
- 如果查询数据多,则会导致该线程持有的data list不能释放,相当于内存泄露,最后导致OOM
- 如果请求数据库操作很多且阻塞住了,会导致服务器可用的woker线程变少,严重则会导致服务不可用,nginx报504 Gateway Timeout
doc
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 聊聊flink JobManager的heap大小设置
- 聊聊flink TaskManager的heap大小设置
- [CentOS7]redis设置开机启动,设置密码
- hadoop地址配置、内存配置、守护进程设置、环境设置
- OpenMediaVault 设置
- scrapy代理的设置
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。