sql socket timeout configuration

http://www.cubrid.org/blog/dev-platform/understanding-jdbc-internals-and-timeout-configuration/

 

 jetty jndi

 <Set name="connectionProperties">

<New class="java.util.Properties">

<Call name="setProperty">

             <Arg>oracle.net.CONNECT_TIMEOUT</Arg>

             <!-- ms -->

             <Arg>60000</Arg>

        </Call>

        <Call name="setProperty">

             <Arg>oracle.jdbc.ReadTimeout</Arg>

             <!-- ms -->

             <Arg>60000</Arg>

        </Call>

</New>

</Set>

 

Tomcat jndi config

 <Resource auth="Container" name="jdbc/aaa" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="30" maxIdle="4" maxWait="1500" 

              removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"

username="ad" password="aaa" type="javax.sql.DataSource" url="jdbc:oracle:thin:@10.2.2.2:1521:uat" testOnBorrow="true" validationQuery="SELECT 1 FROM DUAL" connectionProperties="{oracle.jdbc.ReadTimeout=60000,oracle.net.CONNECT_TIMEOUT=60000}"/>

 

JDBC driver type 4 uses the socket to connect to the DBMS, and the connection timeout process between the application and the DBMS is not carried out by the DBMS.

Socket timeout value for JDBC driver is necessary when the DBMS is terminated abruptly or an network error has occured (equipment malfunction, etc.). Because of the structure of TCP/IP, there are no means for the socket to detect network errors. Therefore, the application cannot detect any disconnection with the DBMS. If the socket timeout is not configured, then the application may wait for the results from the DBMS indefinitely. (This connection is also called a "dead connection.") To prevent dead connections, a timeout must be configured for the socket. Socket timeout can be configured via JDBC driver. By setting up the socket timeout, you can prevent the infinite waiting situation when there is a network error and shorten the failure time.

It is not recommended to use the socket timeout value to limit the statement execution time. So the socket timeout value must be higher than the statement timeout value. If the socket timeout value is smaller than the statement timeout value, as the socket timeout will be executed first, and the statement timeout value becomes meaningless and will not be executed.

Socket timeout has 2 options listed below, and their configurations vary by driver.

 

• Timeout at socket connection: Time limit for Socket.connect(SocketAddress endpoint, int timeout)
• Timeout at socket reading/writing: Time limit for Socket.setSoTimeout(int timeout)

 

By checking the source for CUBRID, MySQL, MS SQL Server (JTDS) and Oracle JDBC, we confirmed that all the drivers we checked use the 2 APIs above.

How to configure SocketTimeout is as explained below.

 

How to configure SocketTimeout is as explained below.

JDBC Driver	connectTimeout	Default	Unit	Application Method
socketTimeout	Default	Unit
MySQL Driver	connectTimeout	0	ms	Specify the option in the DriverURL. Format: jdbc:mysql://[host:port],[host:port].../[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]... Example:   jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000
socketTimeout	0	ms
MS-SQL Driver jTDS Driver	loginTimeout	0	sec	Specify the option in the DriverURL. Format: jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]] Example:   jdbc:jtds:sqlserver://server:port/database;loginTimeout=60;socketTimeout=60
socketTimeout	0	sec
Oracle Thin Driver	oracle.net.CONNECT_TIMEOUT	0	ms	Not possible with the driverURL. Must be delivered to the properties object via OracleDatasource.setConnectionProperties() API. When DBCP is used, use the following APIs: BasicDatasource.setConnectionProperties() BasicDatasource.addConnectionProperties()
oracle.jdbc.ReadTimeout	0	ms
CUBRID Thin Driver	No separate configuration	5,000	ms	Not possible with the driverURL. Timeout occurs in 5 seconds. Note 1: When timeout occurs with althost option specified in the URL, it can be connected to the designated host. Note 2: C API can be used to state the login_time option in ms in the URL.

• Note 1: The default value for connectTimeout and socketTimeout is "0," which means that the timeout does not occur.
• Note 2: You can also configure through properties without directly using the separate API of DBCP.