Java sql sqlrecoverableexception ошибка ввода вывода socket read timed out

I am using Spring JdbcTemplate class for performing database operations. My server is Tomcat 7.

When I click the button of my application to perform some DB operations I get the following error

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [XXXXX (my query) ];
IO Error: Socket read timed out; nested exception is java.sql.SQLRecoverableException: IO Error: Socket read timed out

Strange Behaviour:- This is happening only for the very first time. When I click the button next time everything is fine.

I even tried accessing the DB directly from toad and I am not getting any connection time out there.

DB Properties in context.xml:-

Resource name=»jdbc/dsStaloneTelefoni»
auth=»Container»
type=»javax.sql.DataSource»
driverClassName=»oracle.jdbc.OracleDriver»
url=»jdbc:oracle:thin:@someIP:XXXXX»
username=»XXXX»
password=»XXXXXXXXXX»
testOnBorrow=»true»
testOnReturn=»true»
validationQuery=»select 1 from dual»
maxActive=»20″
maxIdle=»30″
maxWait=»-1″/>

Please help.

I am using Spring JdbcTemplate class for performing database operations. My server is Tomcat 7.

When I click the button of my application to perform some DB operations I get the following error

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [XXXXX (my query) ];
IO Error: Socket read timed out; nested exception is java.sql.SQLRecoverableException: IO Error: Socket read timed out

Strange Behaviour:- This is happening only for the very first time. When I click the button next time everything is fine.

I even tried accessing the DB directly from toad and I am not getting any connection time out there.

DB Properties in context.xml:-

Resource name=»jdbc/dsStaloneTelefoni»
auth=»Container»
type=»javax.sql.DataSource»
driverClassName=»oracle.jdbc.OracleDriver»
url=»jdbc:oracle:thin:@someIP:XXXXX»
username=»XXXX»
password=»XXXXXXXXXX»
testOnBorrow=»true»
testOnReturn=»true»
validationQuery=»select 1 from dual»
maxActive=»20″
maxIdle=»30″
maxWait=»-1″/>

Please help.

java.sql.SQLException: Io exception: Socket read timed out vs Closed Connection

I am trying to research this issue on the following two errors connecting to Oracle DBs:

1. Closed Connection
2. java.sql.SQLException : Io exception: Socket read timed out

1. Closed Connection : Is occurring either because there was some sort of network disruption or the DB closed the session due to some sort «inactivity»
2. java.sql.SQLException : Io exception: Socket read timed out : This is a case where the connection was made successfully but for some reason the socket/data was empty and eventually it timed-out because no data was available.

Is it possible to replicate the above errors in a local Oracle DB env ? What are the steps ?

I appreciate you taking the time to respond.

2 Answers 2

Your understanding on closed connection is right. reason for closed connection: External devices such as firewall, network devices, and remote database listeners can force network connections to close after a period of inactivity

ReadTimeOut will happen even on active connections. If a query or procedure is taking lot of time, you will get read time out exception.

• Closed Connection : Shutdown the database listener when database is running
• ReadTimedOut : Add sleep in procedure for more than 10 minutes and call that procedure from application

Replication of Socket read time out error in Oracle DB env:

setNetworkTimeout for SQL connection // for example sake, set timeout as 120 seconds

Call a database procedure from java and sleep in that procedure for time more than setNetworkTimeout

Since procedure is not returning with-in 120 seconds due to 125 seconds sleep, java will throw socket read time out in above scenario.

Источник

What are the reasons for getting a Socket read timed out Exception in Java?

What are the reasons for getting a Socket read timed out Exception in Java?

3 Answers 3

A timeout was set by the library and yet it didn’t read anything before the timeout was reached.

This could happen if the other end it is reading from stop for a long time. The library determines what counts as a long time. I would look for any errors logged on the server it is connected to.

Depending on your environment, there can be many different reasons: it could be a simple network outage between your machine and the database server. But it could also be that there is a congestion on a lower level; and one layer just sits there; all connection threads taken; waiting for some other side to move . that won’t move any more.

In that sense, the unspecific answer to your unspecific question is: identify the different components that exist in your system and that need to communicate with each other in order to deliver the functionality that they are supposed to deliver. When you have that «map»; you start checking each location on that map by its own.

And hint: if you don’t have such a (mental) map yet; well, welcome to the world of professional IT. When you are responsible for a complex system; you simply have to understand the different components within that system; and how they interact. And that is not something that stackoverflow can give to you; that is something that you have to figure yourself.

Источник

What does the «IO Error: Socket read timed out» means (SQL developer)

When I try to open SQL developer then I am getting this error:

What is the problem?

3 Answers 3

Typical causes for that error:

1. Your database listener is not running
2. It is running, but maybe there is a firewall intervening
3. It is running, no firewall issue, but you have provided incorrect connection details.

Make sure your installation is on a LOCAL drive!

If your SQL Developer is launched from a network location (specifically, a different location than your database network location), that could be the issue.

I was having the same problem and I moved my installation to my local drive and it connected right away 🙂

Above answers are exact causes but to resolve this:

Go to server machine and login as root then run:

Then come to oracle user and run:

Hot Network Questions

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

Intermittent «login timeout occured while connecting to the database» or «IO Error: Socket read timed out» Messages Signaled when Running ODI 11g Processes (Doc ID 2263587.1)

Last updated on DECEMBER 15, 2022

Applies to:

Oracle Data Integrator — Version 11.1.1.3.0 to 11.1.1.9.99 [Release 11gR1]
Information in this document applies to any platform.

Symptoms

The following intermittent error messages are signaled by Oracle Data Integrator (ODI) 11g relative to a Data Server MYDB when executing a Session:

oracle.odi.jdbc.datasource.ConnectionTimeoutException: A login timeout occurred while connecting to the database

java.sql.SQLException: IO Error: Socket read timed out

+ java.sql.SQLRecoverableException: IO Error: Socket read timed out

+ oracle.net.ns.NetException: Socket read timed out

The ODI processes fails with the two alternative error messages due to the JDBC driver not being able to successfully connect to the Oracle database on connection MYDB.

. or losing a previously established connection to the same Data Server.

Note theВ MYDB database alert file reports ORA-609, and «TNS-12637: Packet receive failed» errors, which are relevant to the ODI execution failures:

Fatal NI connect error 12637 , connecting to:
(LOCAL=NO) В VERSION INFORMATION:

В В В TNS for Solaris: Version 12.1.0.1.0 — Production
В В В Oracle Bequeath NT Protocol Adapter for Solaris: Version 12.1.0.1.0 — Production
В В В TCP/IP NT Protocol Adapter for Solaris: Version 12.1.0.1.0 — Production
В Time: 02-MAY-2017 11:53:41
В Tracing not turned on.
В Tns error struct:
В В В ns main err code: 12637

В TNS-12637: Packet receive failed
В В В ns secondary err code: 12532
В В В nt main err code: 0
В В В nt secondary err code: 0
В В В nt OS err code: 0
В В В Tue May 02 11:53:41 2017
В opiodr aborting process unknown ospid (29418) as a result of ORA-609

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Socket read timed out Whilst Executing Load Plan In ODI (Doc ID 2350396.1)

Last updated on OCTOBER 18, 2022

Applies to:

Symptoms

Timeout connection whilst executing Load Plan in ODI with JDBC 11.2.0.4.

While executing a Load Plan, it fails always in same step with the following error message:

java.lang.Exception: The application script threw an exception: TableException: Custom Exception: Exception occured when trying to establish Target Connection

If the Load Plan is executed, the same will finish with no errors (sometimes it needs to be re-executed more than once).

WebLogic AdminServer.log shows:
java.sql.SQLRecoverableException: IO Error: Socket read timed out

while the database shows:
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (3975) as a result of ORA-609

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

While applying the R12.2 upgrade driver, we faced the issue of WFXLoad.class failing in adworker log but showing up as running on adctrl

        Control
Worker  Code      Context            Filename                    Status
——  ———  ——————  —————————  —————
     1  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     2  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     3  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     4  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     5  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     6  Run       AutoPatch R120 pl                              Wait        
     7  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     8  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     9  Run       AutoPatch R120 pl  WFXLoad.class               Running      
    10  Run       AutoPatch R120 pl                              Wait        

adworker log shows:

This was happening again and again. The DBAs were suspecting network issue, cluster issue, server issue and all the usual suspects.  In Database alert log we saw these errors coming every few seconds:

We tried changing the parameters in sqlnet.ora and listener.ora as instructed in the article:

Checked from remote app tier and it was same 4 seconds.

Stopped listener and checked on DB server that uses bequeath protocol:

Again it took 4 seconds.

A few days back, I had seen that connect time had increased after turning setting the DB initialization parameter pre_page_sga to true in a different instance.  On a hunch, I checked this and indeed pre_page_sga was set to true.  I set this back to false:

alter system set pre_page_sga=false scope=spfile;

shutdown immediate;

exit

sqlplus /nolog

conn / as sysdba

startup

The connections were happening instantly.  So I went ahead and resumed the patch after setting:

update fnd_install_processes 

set control_code=’W’, status=’W’;

commit;

I restarted the patch and all the workers completed successfully.  And the patch was running significantly faster.  So I did a search on support.oracle.com to substantiate my solution with official Oracle documentation.  I found the following articles:

Slow Connection or ORA-12170 During Connect when PRE_PAGE_SGA init.ora Parameter is Set (Doc ID 289585.1)

Health Check Alert: Consider setting PRE_PAGE_SGA to FALSE (Doc ID 957525.1)

The first article (289585.1) says:

PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. The advantage that PRE_PAGE_SGA can afford depends on page size.

The second article (957525.1) says:

Having the PRE_PAGE_SGA initialization parameter set to TRUE can significantly increase the time required to establish database connections.

The golden words here are «Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.».  That is exactly what happens when you do adpatch or adop.

Keep this in mind, whenever you do adpatch or adop, make sure that pre_page_sga is set to false.  It is possible that you may get the error «java.sql.SQLRecoverableException: IO Error: Socket read timed out» if you don’t.  Also the patch will run significantly slower if pre_page_sga is set to true.  So set it to false and avoid these issues.

I’m facing the same issue. Any sugestions to resolve it?
06-09-20 21:49:04.772 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:04.772 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@135c4bab: (connection has passed maxLifetime)
06-09-20 21:49:05.875 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@686ae288 failed
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451) [HikariCP-3.4.5.jar:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_241]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_241]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_241]
Caused by: java.net.SocketTimeoutException: Socket read timed out
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
… 6 common frames omitted
06-09-20 21:49:05.926 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:05.926 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@77661966: (connection has passed maxLifetime)
06-09-20 21:49:06.922 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Pool stats (total=6, active=1, idle=5, waiting=0)
06-09-20 21:49:06.922 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:08.433 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Pool stats (total=10, active=1, idle=9, waiting=0)
06-09-20 21:49:08.433 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:15.250 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Pool stats (total=10, active=1, idle=9, waiting=0)
06-09-20 21:49:15.250 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:16.574 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Pool stats (total=10, active=1, idle=9, waiting=0)
06-09-20 21:49:16.574 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:19.784 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@135c4bab failed
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451) [HikariCP-3.4.5.jar:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_241]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_241]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_241]
Caused by: java.net.SocketTimeoutException: Socket read timed out
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
… 6 common frames omitted
06-09-20 21:49:19.811 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:19.811 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@2e29e860: (connection has passed maxLifetime)
06-09-20 21:49:20.935 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@77661966 failed
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451) [HikariCP-3.4.5.jar:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_241]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_241]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_241]
Caused by: java.net.SocketTimeoutException: Socket read timed out
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
… 6 common frames omitted
06-09-20 21:49:20.960 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:20.961 DEBUG com.zaxxer.hikari.pool.PoolBase : HikariCP-ABC — Closing connection oracle.jdbc.driver.T4CConnection@7230fdc5: (connection has passed maxLifetime)
06-09-20 21:49:23.595 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Pool stats (total=6, active=1, idle=5, waiting=0)
06-09-20 21:49:23.596 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:25.216 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Pool stats (total=6, active=1, idle=5, waiting=0)
06-09-20 21:49:25.216 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:27.395 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Pool stats (total=7, active=1, idle=6, waiting=0)
06-09-20 21:49:27.396 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:28.421 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Pool stats (total=6, active=1, idle=5, waiting=0)
06-09-20 21:49:28.421 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-BH — Fill pool skipped, pool is at sufficient level.
06-09-20 21:49:28.592 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Pool stats (total=6, active=1, idle=5, waiting=0)
06-09-20 21:49:28.592 DEBUG com.zaxxer.hikari.pool.HikariPool : HikariCP-ABC — Fill pool skipped, pool is at sufficient level.

последовательность

Эта статья в основном знакомит с настройками тайм-аута сокета jdbc.

Категория тайм-аута jdbc

В основном это следующие категории

• transaction timeout

Устанавливается время выполнения транзакции, которая может содержать несколько операторов.

• statement timeout(Также эквивалентно таймауту выборки набора результатов)

Устанавливается время ожидания выполнения оператора, то есть время ожидания, в течение которого драйвер ожидает завершения выполнения оператора и принимает данные (Обратите внимание, что тайм-аут оператора - это не тайм-аут всего запроса, а тайм-аут, когда оператор выполняется и данные fetchSize извлекаются. После этого следующий из resultSet запускает данные выборки, когда это необходимо. Тайм-аут каждой выборки рассчитывается отдельно, и значение по умолчанию также В зависимости от тайм-аута, установленного в заявлении)

• jdbc socket timeout

Период ожидания для операций чтения и записи сокета ввода-вывода jdbc установлен, чтобы предотвратить блокировку и ожидание драйвера из-за сетевых проблем или проблем с базой данных. (Рекомендуется быть больше, чем время ожидания запроса)

• os socket timeout

Это настройка сокета на уровне операционной системы (Если таймаут сокета jdbc не установлен, но установлен тайм-аут сокета на уровне ОС, используется значение тайм-аута системного сокета.)。

Чем ниже указанные выше различные уровни тайм-аута, тем выше приоритет. Это означает, что если следующая конфигурация меньше, чем указанное выше значение конфигурации, тайм-аут будет запущен первым, что эквивалентно указанному выше значению конфигурации «недействительно».

jdbc socket timeout

Реализация этих разных данных в драйвере jdbc отличается

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

• Устанавливается по свойствам

            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);
Скопировать код

Обратите внимание, что переменные среды необходимо установить перед подключением

• tomcat jdbc pool

Как правило, мы не используем напрямую соединение jdbc, а используем пул соединений. Поскольку пул jdbc tomcat — это пул соединений с базой данных, используемый Springboot по умолчанию, вот как установить его в пуле jdbc tomcat.

spring.datasource.tomcat.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
Скопировать код

Обратите внимание, что здесь используется точка с запятой, единица измерения — миллисекунды, здесь вы можете настроить префикс (Для пула соединений jdbc tomcat значение по умолчанию - spring.datasource.tomcat), можно настроить, например

    @Bean
    @Qualifier("writeDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }
Скопировать код

Предполагая, что вы настроили префикс на spring.datasource.write, приведенная выше конфигурация станет

spring.datasource.write.connectionProperties=oracle.net.CONNECT_TIMEOUT=10000;oracle.jdbc.ReadTimeout=60000
Скопировать код

Если oracle.jdbc.ReadTimeout не установлен, значение по умолчанию в драйвере равно 0

oracle.jdbc.ReadTimeout

Драйвер внутренне устанавливает значение переменной 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);
        }

    }
Скопировать код

Вы можете видеть, что последний набор — это 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: ошибка ввода-вывода: сокет 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, сообщается тайм-аут (close_or_fetch_from_next) этот таймаут указывает, когда метод result.next запускает новый пакет извлечения данных (Когда данные fetchSize потребляются, следующий следующий вызовет выборку нового пакета данных.) После этого данные, возвращаемые базой данных, не принимаются в течение времени ожидания.

Размер fetchSize jdbc Oracle по умолчанию равен 10, то есть для каждой выборки, если данные не принимаются в течение более указанного времени, будет сгенерировано исключение тайм-аута.

резюме

Будьте очень осторожны при установке значения socketTimeout для jdbc. Параметры драйвера jdbc для разных баз данных отличаются, особенно если вы используете разные пулы соединений, параметры также могут отличаться. Для служб, которые в значительной степени зависят от операций с базой данных, необходимо установить это значение, в противном случае, в случае сбоев в сети или базе данных, поток службы будет заблокирован на java.net.SocketInputStream.socketRead0.

• Если данных запроса слишком много, список данных, хранящийся в потоке, не может быть освобожден, что эквивалентно утечке памяти и, в конечном итоге, приводит к OOM
• Если запрашивается и блокируется много операций с базой данных, это приведет к тому, что для сервера будет доступно меньше рабочих потоков. В серьезных случаях служба будет недоступна, и nginx сообщит о 504 тайм-ауте шлюза.

doc

• oracle.jdbc.ReadTimeout
• Глубокое понимание настроек тайм-аута JDBC
• Как контролировать время ожидания при доступе к данным на основе JDBC в Spring
• BugFix－HttpURLConnection