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

Question

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

My understanding:

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.

Thanks.

Solution

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:

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

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

   dbms_lock.sleep(125); -- sleeps for 125 seconds
   

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

Comments

[kart0624]

Thanks for the input. For the Closed Connection error, I am trying to figure out if there's difference between DB closing the connection and network disturbance, whether both provides the same error or if there's a distinction. Also, both I believe both are exceptions right java.sql.SQLRecoverableException: Closed Connection Also what about Socket timed out exception, on what are the specific causes for it like "network" disruption.

[troy_frommer]

@kart0624 I'm not sure about that. Try to log the error codes to see if they are distinct. Obviously they happen for different reasons but they may or may not use the same error code. Here is some information on logging in java if you are not familiar with it. link