Www Cubrid Org(9)

7/30/2019 Www Cubrid Org(9)

1/9

| Login | Register

Understanding JDBC Internals & Timeout Configuraon

posted last year in Dev Plaorm category by Woon Duk Kang

Tweet

An applicaon with a proper JDBC meout can cut down the failure me. In this arcle we would like to talk about different kinds of meout values

and recommended meout applicaon methods when you import values from DBMS.

Web Applicaon Server became unresponsive aer a D Dos aack one day

(This is a close reconstuon of an actual event.)

The enre service did not work normally aer a DD os aack. The network was disconnected because L 4 was not working, which caused WAS to be

noperable as well. Shortly aerwards, the security team blocked all DD os aacks, and restored the network back to normal. Yet, WAS was sll not

working.

Through the ThreadDump of WAS, the service team was able to confirm that WAS had stopped during API call from JDBC. Aer 20 minutes, WAS was

ll in WAITING status and the service was sll not working. About 30 minutes had passed when an excepon suddenly occurred, and the service was

estored.

Why was WAS in W AITING status for 30 minutes when QueryTimeout value was set to 3 seconds, and why did WAS start working again a er 30 minutes?

You can find the answer if you understand how the JD BC Timeout works.

Why Do We Need to Know about the JDBC Driver?

When there is a performance issue or an error, WAS and DBMS are the two important ers we pay aenon to. In NH N, WAS and DB MS are generally

handled by different departments, so each department tries to figure out this situaon by focusing on their own area of experse. When this happens,

you get a blind spot between WAS and DBMS, that does not receivemuch aenon. For Java applicaons, the blind spot would be between DBCP and

DBC. In this arcle we will focus on JDBC.

What is a JDBC Driver?

D BC is a standard API that you use to access the DB MS in Ja va applicaons. There are 4 types of JDBC drivers (Wikipedia) defined by Sun. NHN

mainly uses the type 4. JDBC type 4 driver is wrien enrely in Java (pure Java) and communicates with a DBMS using sockets in Java applicaons.

Figure 1: JDBC Type 4.

Type 4 drivers process byte stream via sockets, and have the same basic operaons as a network library like HpClient. This uses up a lot of CPU

esources and loses response meout, while sharing the same error points with other network libraries. If you have used HpClient before, then you

must have encountered errors from not seng the meout value. Type 4 driver may have the same error (a hang occurs) if the socket meout value is

not set properly.

Let's learn about how to configure the socket meout value for JDBC driver, and what needs to be considered.

Timeout Class at WAS - DBMS Communicaon

Like 6 people like this. Sign Up to see what yourfriends like.

AboutAbout DownloadsDownloads DocumentaonDocumentaon Dev ZoneDev Zone CommunityCommunity BlogBlog

converted by Web2PDFConvert.com
http://www.cubrid.org/abouthttp://www.cubrid.org/?mid=downloads&item=any&os=detect&cubrid=8.4.3http://www.cubrid.org/documentationhttp://www.cubrid.org/dev_zonehttp://www.cubrid.org/communityhttp://www.cubrid.org/bloghttp://www.cubrid.org/http://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&l=kohttp://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&act=dispMemberLoginFormhttp://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&act=dispMemberSignUpFormhttp://www.web2pdfconvert.com/?ref=PDFhttp://www.web2pdfconvert.com/?ref=PDFhttp://en.wikipedia.org/wiki/JDBC_driverhttp://www.cubrid.org/blog/tags/NHN/http://www.cubrid.org/blog/dev-platform/how-to-analyze-java-thread-dumps/http://www.cubrid.org/bloghttp://www.cubrid.org/communityhttp://www.cubrid.org/dev_zonehttp://www.cubrid.org/documentationhttp://www.cubrid.org/?mid=downloads&item=any&os=detect&cubrid=8.4.3http://www.cubrid.org/abouthttp://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&act=dispMemberSignUpFormhttp://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&act=dispMemberLoginFormhttp://www.cubrid.org/?mid=textyle&category=dev-platform&alias_title=understanding-jdbc-internals-and-timeout-configuration&vid=blog&l=kohttp://www.cubrid.org/


2/9

Figure 2: Timeout Class.

Figure 2 above shows a simplified version of the meout class when WAS and DBMS are communicang.

The higher level meout is dependent on the lower level meout. The higher level meout will operate normally only if the lower level meout

operates normally as well. If the JDBC driver socket meout does not work properly, then higher level meouts such as statement meout and

ransacon meout will not work properly either.

We have received a lot of comments that said:

Even aer the statement meout was configured, the applicaon sll did not recover from the error because the statement meout did not work at the me of network

fai lure.

The statement meout does not handle the meouts at the me of network failure. Statement meout does only one thing: restricts the operaon

me of 1 statement. Handling meout to prevent network failure must be done by JDBC Driver.

The JDBC driver's socket meout is affected by the OS's socket meout configuraon. This would explain why JDBC connecon hang recovers 30

minutes aer the network connecon failure, even when the JDBC driver's socket meout is not configured.

DBCP Connecon Pool is located on the le side of Figure 2. You can see that the meout classes and DBCP are separated. DBCP is in charge of

creang and managing connecons, and is not involved in processing meouts. When a connecon is c reated within D BC P or a validaon query is

ent to check the validity of the connecon, the socket meout does affect these processes but does not affect the applicaon directly.

However, when getConnection() is called to DBCP from the applicaon logic, then you can specify the meout unl the applicaon acquires the

connecon. However, this has nothing to do with the JDB C's connect meout.

http://www.web2pdfconvert.com/?ref=PDFhttp://www.web2pdfconvert.com/?ref=PDF


3/9

Figure 3: Timeout for Each Levels.

What is Transacon Timeout?

Transacon meout is a meout valid inframeworks (Spring, EJB container) or at the applicaon level.

Transacon meout can be an unfamiliar concept. Simply put, transacon meout is "

Statement Timeout * N (number of statements being processed) + @ (garbage collection, etc.)."

Transacon meout is used to limit the total statement processing me to the maximum amount allowed.

For example, if it takes 0.1 second to process 1 statement, processing a few statements would not be a problem, but processing 100,000 statements

would take 10,000 seconds (approx. 7 hours). Statement meout can be used here.

EJB CMT (Container Managed Transacon) would be a typical example of actual implementaons. EJB CMT varies in its implementaon methods and

operang process depending on developers. NHN does not use EJB Container, so transacon meout of Spring Framework would be the most common

example. In Spring, you may use XML as shown below or use @Transactional from Java source codes, for configuraon.

Statement meout provided by Spring is very simple. It records the starng me a nd the elapsed me for each transacon, and checks the elapsed

me when an event occurs. If the meout is abnormal, it generates an excepon.

n S pring, the connecon is stored in, and used from ThreadLocal. This is called Transacon Synchronizaon. When a connecon is saved in

ThreadLocal

the starng me and the meout me of the transacon is also recorded. When a statement is being created by using the proxy connecon, the

elapsed me is checked to generate an excepon.

The E JB CM T i mplementaon is done in a s imilar way. The structure itself i s very simple. If the transacon meout is very important but the container

or the framework you are using does not provide this feature, you could implement it yourself without major problems. There is no standard API for

ransacon meout.

Lucy 1.5 and 1.6 Framework does not have a transacon meout feature, but you can get the same result by using Transacon Manager from Spring.

f the processing me of the statement (5 or less ) is 200 ms and the processing me of other business logics or framework operaon is 100 ms, the

ransacon meout me should be set to 1,100 ms ((200 * 5) + 100) or more.

What is Statement Timeout?

t is a limitaon on how long a statement should run. It sets the meout value for the statement, which is a JDBC API. The JDBC driver processes the

tatement meout based on this value. Statement meout is configured via java.sql.Statement.setQueryTimeout(int timeout),

which is a JD BC API . In recent developing environments, the developers rarely configure the statement meout value directly through Java source

codes, but oen configure it by using the framework.

123



4/9

To use iBas as an example, the default value can be configured by using @defaultStatementTimeout value in sqlMapConfig/sengs ofsql-

map-config.xml. By using @timeout value, you can configure statement, select, insert and update syntax ofsql-map.xmlseparately.

When MangedDatasource of Lucy 1.5 and 1.6 is used, the queryTimeout opon can be used to get a statement of which meout is configured

at the datasource level.

The statement meout me is configured based on the features of each applicaon, so there is no recommended configuraon value.

Statement Timeout Execuon Process for JDBC Driver

Statement meout works differently per DB MS and driver. T he way it works is s imilar between Oracle and M S S QL Server. It is a lso s imilar betweenMySQL and CUBRID.

QueryTimeout for Oracle JDBC Statement

1. Creates a statement by calling Connection.createStatement().

2. Calls Statement.executeQuery().

3. The statement transmits the Query to Oracle DBM S by using its own connecon.

4. The statement registers a statement toOracleTimeoutPollingThread (1 for each classloader) for meout process .

5. Timeout occurs.

6. Oracle TimeoutPollingThread calls OracleStatement.cancel().

7. Sends a cancel message through the connecon and cancels the query being executed.

Figure 4: Query Timeout Execuon Process for Oracle JDBC Statement.

QueryTimeout for JTDS (MS SQLServer) Statement

1. Creates a statement by calli ng Connection.createStatement().

2. Cal ls Statement.executeQuery().

3. The statement transmits the Query to MS Sql Server by using the internal conn econ.

4. The statement registers a s tatement in Ti merThread for meout process.

5. T imeout occurs .

6. TimerThread calls up TsdCore.cancel() insi de the JtdsStatement object.

7. Sends a canc el messa ge through the ConneconJD BC a nd cancels the query being executed.



5/9

Figure 5: QueryTimeout Execuon Process for JTDS (MS SQLServer) Statement.

QueryTimeout for MyS QL JDBC S tatement (5.0.8)



3. The statement transmits the Query to MySqlS erver by usin g the internal connecon.

4. The statement creates a new meout-execuon thread for meout process.

5. For version 5.1.x, it changes to assi gn 1 thread for each connecon.

6. Registers the meout execuon to the thread .

7. T imeout occurs .

8. The meout-execuon thread cr eates a connecon that has the same configuraons a s the statement.

9. Transmits the cancel Query (KI LL Q UERY "conneconId) by using the connecon.

Figure 6: QueryTimeout Execuon Process for MySQL JDBC Statement (5.0.8).

QueryTimeout for CUBRID JDBC Statement



3. The statement transmits the Query to CU BR I D DB MS by using the internal connecon.

4. The statement creates a new meout-execuon thread for meout process.

5. Registers the meout execuon to the thread .6. T imeout occurs .

7. The meout-execuon thread cr eates a connecon that has the same configuraons a s the statement.

8. Transmi ts the cancel message using the connecon.



6/9

Figure 7: QueryTimeout Execuon Process for CUBRID JDBC Statement.

What is Socket Timeout for JDBC Driver?

D BC driver type 4 uses the s ocket to connect to the DB MS, a nd the connecon meout process between the applicaon and the D BMS is not carried

out by the DBMS.

Socket meout value for JDBC driver is necessary when the DBMS is terminated abruptly or an network error has occured (equipment malfuncon,

etc.). Because of the structure of TCP/IP, there are no means for the socket to detect network errors. Therefore, the applicaon cannot detect any

disconnecon with the D BM S. I f the socket meout is not configured, then the applicaon may wait for the results from the D BM S indefinitely. (T his

connecon is also called a "dead connecon.") To prevent dead connecons, a meout must be configured for the socket. Socket meout can be

configured via JDBC driver. By seng up the socket meout, you can prevent the infinite waing situaon when there is a network error and shorten

he failure me.

t is not recommended to use the socket meout value to limit the statement execuon me. So the socket meout value must be higher than t he

tatement meout value. If the socket meout value is smaller than the statement meout value, as the socket meout will be executed first, and the

tatement meout value becomes meaningless and will not be executed.

Socket meout has 2 opons listed below, and their configuraons vary by driver.

Timeout at socket connecon: Time limit for Socket.connect(SocketAddress endpoint, int timeout)

Timeout at socket reading/wring: 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.

JDBC

Driver

connectTimeout Default UnitApplicaon Method

socketTimeout Default Unit

MySQL

Driver

connectTimeout 0 ms

Specify the opon in the DriverURL.Format:

jdbc:mysql://[host:port],[host:port].../[database]

[?

propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Example:

socketTimeout 0 ms

MS-SQL

Driver

jT DS

Driver

loginTimeout 0 secSpecify the opon in the DriverURL. Format:

jdbc:jtds:://[:][/]

[;=[;...]]

Example:socketTimeout 0 sec

oracle.net.CONNECT_TIMEOUT 0 ms Not possible with the driverURL. Must be delivered to the properes object via

jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000

jdbc:jtds:sqlserver://server:port/database;loginTimeout=60;socketTimeout=60



7/9

Oracle

Thin

Driver

OracleDatasource.setConnectionProperties()API. When DBCP is used, use thefollowing APIs:

BasicDatasource.setConnectionProperties()

BasicDatasource.addConnectionProperties()

oracle.jdbc.ReadTimeout 0 ms

CUBRID

Thin

Driver

No separate configuraon 5,000 ms

Not possible with the driverUR L. Timeout occurs in 5 seconds.

Note 1: When meout occurs with althost opon specified in the URL, it can be connected to the

designated host.

Note 2: C API can be used to state the login_me opon in ms in the URL.

Note 1: The default value for connectTimeout and socketTimeout is "0," which means that the meout does not occur.

Note 2: You can a lso configure through properes without directly using the separate AP I of DB CP.

When you configure properes, pass on the character string where the key value is conneconProperes, and the format value is

[propertyName=property;]*. The following example shows configuring properes through xml in iBas.

OS Level SocketTimeout Configuraon

f the socket meout or the connect meout is not configured, most of the me, applicaons cannot detect network errors. So, unl the applicaons are

connected or are able to read data, they will wait indefinitely. However, if you look at the actual issues NHN services encountered, the problems were

oen resolved aer the applicaons (WAS) tried to reconnect to the network 30 minutes aer. This is because the O S can also configure socket

meout me. Linux s ervers used by NH N have s et the socket meout to 30 minutes. T his checks the network connecon at the OS level. B ecause the

KeepAlive checking cycle for NH N's Linux servers is 30 minutes, even when socket meout is set to 0, the D BMS network connecon problems ca used

by network issues do not surpass 30 minutes.

Generally, the applicaon hangs from network issues when the applicaon is ca lling Socket.read(). However, depending on the network

composion or the error type, it can rarely be in waing status while running Socket.write(). When the applicaon calls Socket.write(),

he data is recorded to the OS kernel buffer and then the right to control is returned to the applicaon immediately. Thus, as long as a valid value is

ecorded to the kernel buffer, Socket.write() is a lways s uccessful. However, if the O S kernel buffer is full due to a special network error, even

Socket.write() can be put into waing s tatus. In this case, the OS tries to resend the packet for a certain amount of me, and generates an error

when it reaches the limit. In N HN's Linux server environment, the meout for this s ituaon is set to 15 minutes.

have explained the internal operaons of JDBC so far. I hope that this will help you with the correct meout configuraon and reducing errors.

f you have more quesons or any good informaon related to JDBC, please leave your comments below.

Lastly, I have listed some of the frequently asked quesons below.

FAQ

Q1. I configured the query meout by using Statement.setQueryTimeout(), but it does not work as expected when there is a network error.

Query Timeout only works when it is connected to the socket correctly. Therefore, it cannot be used to solve an exceponal situaon with a network error. To be

prepared for network errors, s ocket meout in JDBC driver must be configured.

Q2. How are transacon meout, statement m eout and JDBC driver socket m eout related to the DBCP configuraon values?

When the connecon i s a cquired from DBCP to JDBC, nothing but waitTimeout is a ffected.

Q3. If JDBC S ocketTimeout is configured, wouldn't the connecons that stayed in idle status for a long me in DBCP be closed?

No. The s ocket opon is appl ie d when the actual da ta is being wrien or read, so it does not affect the connecons i n idle status in DBCP. The socket opon can ha ve

certain effect when new connecons that lack in inside of DBCP are created, old idle connecons are removed, or the validaon is checked, but this does not cause any

significant issues unless the network has an error.

123456

....



8/9

Q4. How long should SocketTimeout be set to?

As I have menoned in the main arcle above, it must be much bigger than the statement meout, and there is no recommended value. Socket meout value for the

JDBC driver becomes effecve aer a ne twork error occurs. A careful configuraon for the value cannot prevent such the errors from happeni ng, but someme s s hortens the

me that the network is disa bled (i f the network is restored right away).

By Woon Duk Kang, Soware Engineer at Web Plaorm Development Lab, NHN Corporaon.

See also

How to Analyze Java Thread Dumps

Dev Plaorm When there is an obstacle, or when a Java based Web applicaon is running much slower than expected, we need to use ...

ast year by Tae Jin Gu 10 67063

MaxClients in Apache and its effect on Tomcat during Full GC

Dev Plaorm This is the fourth arcle in the series of "Become a Java GC Expert". In the first issue Understanding Java Garbage Collect...

3 months ago by Dongsoon Choi 0 5289

Spring Roo: Fast Java Applicaon Development Tool

Dev Plaorm Spring Roo is a development tool that allows eas y and fas t Java Applicaon development. Let s develop a Java applicaon by ...

7 months ago by Kee Seon Baek 0 5590

How Statement Pooling in JDBC affects the Garbage Collecon

Dev Plaorm There are various techniques to improve the performance of your Java applicaon. In this arcle I will talk about Statement ...

10 months ago by Dongsun Choi 2 5212

OOQ: Java Object Oriented Querying now supports CUBRI D Database

CUBRID Apps&Tools It was great couple of weeks for CUBRID project! We opened this month with parcipang at the Russian Internet ...

11 months ago by Esen Sagynov 0 3915

http://www.web2pdfconvert.com/?ref=PDFhttp://www.web2pdfconvert.com/?ref=PDFhttp://www.cubrid.org/blog/cubrid-appstools/jooq-java-object-oriented-querying-now-supports-cubrid-database/#commentshttp://www.cubrid.org/blog/categories/cubrid-appstools/http://www.cubrid.org/blog/cubrid-appstools/jooq-java-object-oriented-querying-now-supports-cubrid-database/http://www.cubrid.org/blog/cubrid-appstools/jooq-java-object-oriented-querying-now-supports-cubrid-database/http://www.cubrid.org/blog/dev-platform/how-statement-pooling-in-jdbc-affects-garbage-collection/#commentshttp://www.cubrid.org/blog/categories/dev-platform/http://www.cubrid.org/blog/dev-platform/how-statement-pooling-in-jdbc-affects-garbage-collection/http://www.cubrid.org/blog/dev-platform/how-statement-pooling-in-jdbc-affects-garbage-collection/http://www.cubrid.org/blog/dev-platform/spring-roo-fast-java-application-development-tool/#commentshttp://www.cubrid.org/blog/categories/dev-platform/http://www.cubrid.org/blog/dev-platform/spring-roo-fast-java-application-development-tool/http://www.cubrid.org/blog/dev-platform/spring-roo-fast-java-application-development-tool/http://www.cubrid.org/blog/dev-platform/maxclients-in-apache-and-its-effect-on-tomcat-during-full-gc/#commentshttp://www.cubrid.org/blog/categories/dev-platform/http://www.cubrid.org/blog/dev-platform/maxclients-in-apache-and-its-effect-on-tomcat-during-full-gc/http://www.cubrid.org/blog/dev-platform/maxclients-in-apache-and-its-effect-on-tomcat-during-full-gc/http://www.cubrid.org/blog/dev-platform/how-to-analyze-java-thread-dumps/#commentshttp://www.cubrid.org/blog/categories/dev-platform/http://www.cubrid.org/blog/dev-platform/how-to-analyze-java-thread-dumps/http://www.cubrid.org/blog/dev-platform/how-to-analyze-java-thread-dumps/


9/9

About CUBRID | Contact us |

2012 CUB RI D.org. All rights reserved.

1 comment

Leave a message...

Discussion Community Share

Reply

klyk 2 months ago

One can also set the socket properties with:

DriverManager.getConnection(String url, Properties info) using the property names as in the above table. Note though that for Oracle the value should

be in fact in milliseconds and not seconds as stated in table.

0

0

Share
http://www.cubrid.org/contacthttp://www.cubrid.org/about

Www Cubrid Org(9)

Documents

Transcript of Www Cubrid Org(9)