Hibernate Operation timed out error seen intermittently

Once we were intermittently getting “java.sql.SQLException: Io exception: Operation timed out” error on production environment.
After few trial and error, we came to the conclusion that it was due to the proxy between application server and new database server. The proxy was terminating the idle connection after some time.

Solution:

Hibernate connection pool configuration was changed to test the connection validity every few minutes using “idle_test_period” and “prefferedTestQuery
Connection pool parameter were changed from :

<property name="hibernate.c3p0.max_size">30</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>

to following

<property name="hibernate.c3p0.max_size">30</property>
<property name="hibernate.c3p0.min_size">0</property>
<property name="hibernate.c3p0.timeout">1200</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>
<property name="c3p0.preferredTestQuery">SELECT 1 from dual</property>

And after this small change, we never received the Operation timed out error again. 🙂 🙂

 

 

Detailed troubleshooting of the Issue (Short Story Long 😉 )

In my experience, I have found that intermittent production issue like this are very hard to debug. In the end issue and solution both might seem very trivial, but person who actually works on live issue might not be able to narrow down the root cause so easily. So I will share my experience on how the issue was debugged and narrowed down. It might help other in troubleshooting similar issues.

 

Once upon a time we were receiving annoying error “java.sql.SQLException: Io exception: Operation timed out” intermittently on our production server. And the only thing which had changed was that production application was moved to new faster server and production database was moved to new database server, the network speed was even faster than the previous environment. :O network speed was faster then it rules out the possibility of intermittent issues in network (network might be congested or something like that) . So I knew that operation timed out error is not due to network problem.

On new environment, there was a proxy between production application and production database and on previous environment proxy was not there between production application and production database.But how come this has caused problem? If proxy allows to establish connection between two machine once, it will allow all the time, It does not intermittently deny connections.

One possibility was that query was taking more time to execute and due to which operation was being timed out. I searched for setting of hibernate that increases query timeout to some greater value then the default and thus could buy some more time before operation times out. But then I remembered my earlier findings that query which was failing, used to execute within a second and operation time out error was coming after wait of 80 seconds. If database is heavily loaded then also it will not take 80 seconds to execute a query that used to be executed within seconds. Even if proxy was also heavily loaded, then also it was very unlikely that request would not go through till 80 seconds.

I explained our DBA about the problem and ask for his opinion and his first question was : Are you receiving any error starting with ORA. And I was like NOPE.  DBA explained to me that, if there is any error on oracle database side then it will always throw an error which will have ORA number. At that time I realized that ohh the error is not coming from the database. Then I looked at the error log again. The error “Io error : Operation timed” out was clearly saying that it was coming while jdbc was trying to read/write any data on the connection stream. connection stream??? Jdbc first creates connection with the database and it writes and reads data from that connection as we read/write data from any network stream. So now I knew that exception was generic java operation timed out exception and it was not coming from database.

If we think for java opeartion timed out error then it comes in the case when somebody is waiting on some operation(operation like getting resource, getting connection, etc ) and it does not completes in time. For e.g. If java program tries to connect to some url and that url cannot be connected in time then Operation timeout exception will be thrown. So in this case it should be trying to connect to the database and it somehow it should not be able to connect to database. But if I rethink about my previous statement (if it connects once it should connect always), I will straight away discard that possibility, because our another production application works perfectly on same server connecting to same database server. The only difference I see in other application is that it connects to database using direct jdbc connection and not through hibernate. Ok ok, so by now I know that there is a jdbc operation time out exception and it should not be coming while establishing connections.

After these findings, I again checked hibernate configurations file to look for any suspicion and I came across c3po connection pool configuration. Connection pool creates a connection in advance and when application wants to create a database connection it simply returns connection if it’s available in it’s pool and if it’s not available in the pool it creates a new connection and returns it. I looked at connection pool settings and found that connection pool was configured in such a way that at a time it should have minimum 10 connection in its pool and it number of connection should not exceed 30. Connection time out was set to 5000 seconds which is around 83 mins, :O 83 minutes, its quite high value. and time period to test if connection is idle was set to 3000 seconds, 50 mins, this interval for testing connection is idle is quite high.

If we consider at two important configuration then we can find out that timeout was set to 83 mins and idle test period was set to around 50 mins. It meant that after interval of 50 mins c3p0 will check for all connections that if connection are still valid or not by executing some validation query on the connection. and if the connection was idle till 83 mins then it will automatically time out.

So in our case c3p0 was checking connection was valid or not at the interval of 50 minutes. But proxy might have been configured to terminate idle connection before 50 mins.

Now at application startup c3p0 would have been creating and pooling min 10 connection. And they stays there in its pool. If application asks for connection it returns connection from its pool.If a application ask for connection before 50 mins say at 45 mins then c3p0 would not have checked that connection is still valid or not and it simply gives one connection from its pool assuming that they are still valid. But meanwhile if proxy is configured to disconnect connections that are idle for 30 mins then proxy should have dropped the connection to the database. So connection is not valid anymore but since c3p0 valid connection check interval is 50 mins it assumes that before 50 mins all connections are still valid and returns that connection to application.

Now application assumes that any connection returned from c3p0 is valid and it starts executing a query and that query does not executes since connection to the database is not valid and eventually this query execution operation times out giving error Io : Operation timeout error. and in this why we were receiving operation timed out error. Error must have been coming due to higher connection validation check timeout.

Solution for the problem is reduce connection timeout time and idle test period time so that c3p0 will execute a test query on connection before it is dropped by proxy. As c3p0 executes validation query every few minutes, connection no longer remains idle and proxy do not drops the connection and hence we do not get Operation time-out error anymore and which solves our problem. In addition to this we reduces min_size to 0 so that c3p0 will not have any connection in its pool.

So we changed connection timeout to 20 minutes, idle test period to 5 minutes and minimum connection to 0 and added preferredTestQuery. This query will be used to check validity of connection when c3p0 checks for validity of connection after idle test period.

<property name="hibernate.c3p0.min_size">0</property>
<property name="hibernate.c3p0.timeout">1200</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="c3p0.preferredTestQuery">SELECT 1 from dual</property>

 

 

Oracle aggregate values into comma separated string

In Oracle many times we want to group rows of values in such a way that aggregated value is the comma separated list/string (csv) of all values.
Also, We might want that the aggregated csv value do not contain any duplicate inside the group function.

Below are two approaches to achieve this.

  1. Using LISTAGG function
  2. Using XMLAGG and XMLELEMENT function

 

Example Scenario:

To demonstrate the query, consider following sample scenario

There is an Employee table. It has three columns department_id, designation, first_name (There could be many other columns in the table, obvious ones are employee_id, grade, project, etc.. but for the sake of simplicity only three columns are included here. if more columns are there solution will work just fine with them also)

In each department, there could be various designations and multiple persons can have same designation. So for each department_id, there could be multiple designation and first_name

As a final solution following should be achieved,

  • For each department combine all designation and first_name into comma separated string.
  • Additionaly, duplicate values should be removed So for e.g. if there are multiple Employee has the same designation Software Developer then in the aggregated string Software Developer should appear only once

 
Sample Input:

department_id designation first_name
1 Developer Pranav
1 Tester Ravi
1 Developer Krupal

Sample Output:

department_id aggregate_designation aggregate_first_name
1 Developer,Tester Krupal,Pranav,Ravi

 

1. Using LISTAGG function

LISTAGG function has following syntax

LISTAGG(column_name, separator_character) WITHIN GROUP (ORDER BY ord_col_name) [OVER (PARTITION BY)]

  • column_name : name of the column, whose values needs to be combined in csv form
  • seperator_character : character to be used for separating values
  • ord_col_name : column name based on which result will be ordered
  • [OVER(Partition by)] : frankly speaking I don’t understand what this does and this is optional so I haven’t used it.

Following query will generate the desired output

SELECT department_id, 
       (SELECT LISTAGG(n.fn, ',') within GROUP (ORDER BY n.fn) 
        FROM  ( SELECT DISTINCT department_id, designation AS fn 
                FROM   employees e 
                ORDER  BY e.designation ) n 
        WHERE  n.department_id = emp.department_id) AS designation, 
       (SELECT LISTAGG(n.fn, ',') within GROUP (ORDER BY n.fn) 
        FROM  ( SELECT DISTINCT department_id, 
                                first_name AS fn 
                FROM   employees e 
                ORDER  BY e.first_name ) n 
        WHERE  n.department_id = emp.department_id) AS first_name 
FROM   employees emp 
GROUP  BY department_id 
ORDER  BY department_id

In the query group by is used for department_id column. So for each department_id all designation and first_name will be combined by the LISTAGG function and csv value will be generated.

Why below select subquery is required, woudn’t ground by with LISTAGG work without it ??

SELECT Listagg(n.fn, ',') 
         within GROUP (ORDER BY n.fn) 
FROM   (SELECT DISTINCT department_id, 
                        designation AS fn 
        FROM   employees e 
        ORDER  BY e.designation) n 
WHERE  n.department_id = emp.department_id

If we use LISTAGG with group by and without select subquery, then the final concatenated values will include duplicate entries.
To remove the duplicate sub-query in the select clause is used. This sub-query first finds out the unique value for each department and then LISTAGG function is applied on the unique value so that final concatenated values contains no duplicate.
For e.g. in the above sub-query first unique designation for each department is found (DISTINCT department_id, designation) then from that list designation for current department is taken (where n.department_id = emp.department_id ). Now these unique values are concatenated and a final csv is generated by applying listagg function on these unique values.

 

2. Using XMLAGG and XMLELEMENT function

XMLAGG : This function aggregates the values of xml-elements.

xmlagg(xmlelement ORDER BY column_name)

  • xmlelement: XML Element
  • column_name: Name of the column on which ordering should be done

XMLELEMENT : This function generates xml element from the column value.

xmlelement( xml_element_name , column_name )

  • xml_element_name : Any valid XML Node name
  • column_name : Name of the column whose values needs to be combined. Here multiple column name can also be used using “||” if value from multiple columns needs to be combined. If some constant value needs to be appended after each row’s value then add that constant value after “||”

extract(‘//text()’) : This function extracts the text part of the xml element. For e.g. if we have node <a>data</a>, It will return “data”, the text part of xml element

GetClobVal : Use this function if in your data, if final CSV is going to contain more than 4000 characters, if in doubt then it is always better to user this function, because it can handle larger data set

RTRIM: It trims the trailing character.

The final query looks like below

 SELECT   department_id,
         (
                  SELECT   rtrim( xmlagg( Xmlelement(e, ie.designation
                                    || ',').extract('//text()') order BY ie.designation ).getclobval(), ',')
                  FROM     (
                                           SELECT DISTINCT department_id,
                                                           designation
                                           FROM            employees ) ie
                  WHERE    ie.department_id = e.department_id ) AS designation,
         (
                  SELECT   rtrim( xmlagg( xmlelement(e, first_name
                                    || ',').extract('//text()') ORDER BY first_name).getclobval(), ',')
                  FROM     (
                                           SELECT DISTINCT department_id,
                                                           first_name
                                           FROM            employees ) ie
                  WHERE    ie.department_id = e.department_id ) AS first_name
FROM     employees e
GROUP BY department_id
ORDER BY department_id 

Simlar to approach 1, sub-query is used for removing the duplicates from the result

SELECT   rtrim( xmlagg( Xmlelement(e, ie.designation
                  || ',').extract('//text()') order BY ie.designation ).getclobval(), ',')
FROM     (
                         SELECT DISTINCT department_id,
                                         designation
                         FROM            employees ) ie
WHERE    ie.department_id = e.department_id 

 
 
Summary:

1st  approach seems simple, but it has limitation that concatenated string should not exceed 4000 character(varchar2 max length)
2nd approach does not have such limitations and can be used for all situatuions
 

Hope this helps 🙂

Singleton in Java

What is Singleton?

Singleton is simply a class that is instantiated exactly once.

How to create Singleton in Java?

Following are broadly three different ways in which we can create Singleton in Java.

  1. Private constructor and public static final field
  2. Private constructor and public static factory method
  3. Enum with one element

1. Private constructor and public static field

//Singleton with private constructor and public static final field
public class Singleton{
	
	//Create Instance of the Class and make it as final, so that later on it cannot be changed
	public static final Singleton INSTANCE = new Singleton();
	
	//Make constructor private, so that it can be instantiated only from inside of the class 
	private Singleton(){
		//Throw exception if someone tries to instantiate the object by accessing the constructor using reflection
		if(INSTANCE != null){
			//Instance is not null, means already one instance of the class is created, hence throw execption.
			throw new RuntimeException("Class should be instantiated only once, don't try to be smart");
		}
	}
	
	/**
	 * Other code of the class goes here
	 * /
}

The private constructor is called only during initialization of the INSTANCE variable and since INSTANCE variable is final, it can not be assigned to something else.

NOTE: It is not enough to make constructor private, since a privileged client can invoke private constructor of the class using reflection and set it’s accessible proerty to true using setAccessible(true) and then create new instances, this will create more than one instance of the class and thus it won’t be singleton anymore. This is shown in the below code snippet

Singleton instance = Singleton.INSTANCE;
//Get list of constructor for this class
Constructor[] constructors =  Singleton.class.getDeclaredConstructors();
for(Constructor constructor : constructors){
	//setAccessible to true, this will now allow code to invoke the private Constructor
	constructor.setAccessible(true);
	
	//If the constuctor does not throw exception for second instantiation,
	//then below code will create new instance of the calss and sysout will return false
	Singleton newInstance = (Singleton)constructor.newInstance();
	System.out.println("New instance is equal to instance? " + (newInstance == instance));
}

To prevent this throw exception from the constructor, if it is making the second instance of the class

 

2. Private constructor and public static factory method

//Singleton with private constructor and public static final field
public class Singleton{
	
	//Create Instance of the Class and make it as final, so that later on it cannot be changed
	private static final Singleton INSTANCE = new Singleton();
	
	//Make constructor private, so that it can be instantiated only from inside of the class 
	private Singleton(){
		//Throw exception if someone tries to instantiate the object by accessing the constructor using reflection
		if(INSTANCE != null){
			//Instance is not null, means already one instance of the class is created, hence throw execption.
			throw new RuntimeException("Class should be instantiated only once, don't try to be smart");
		}
	}
	
	//Provide public static factory method
	public static Singleton getInstance(){
		return INSTANCE;
	}
	
	/**
	 * Other code of the class goes here
	 * /
}

Here again, the private constructor is called only during initialization of the INSTANCE variable and since INSTANCE variable is final, it can not be assigned to something else.

But it has the same caveat as of the approach 1, and private constructor should throw exception if it is being called to instantiate object more than once.

3. Enum with one element

//Singleton using Enum with one field -- preferred approach
public enum Singleton{
	INSTANCE;

	/**
    * Other code of the class goes here
	**/
}

Third and recommended approach of creating Singleton is with the Java ENUM. It provides guarantee against multiple instantiation and against reflection / serialization attacks.

 
 

Singleton and Serialization of object

If Singleton created using method 1 & 2 above, implements Serializable then it should declare all instance fields as transient and provide implementation for readResolve() method. If fields are not declared transient, then each time serialize instance is deserialized a new instance will be created.

Following readResolve() method can be added for deserialization of the objects

// readResolve method to preserve singleton property
private Object readResolve() {
	// Return the Singleton instance and let the garbage collector take care of its impersonator.
	return INSTANCE;
}

NOTE: 3rd approach mentioned above(Enum with one element) does not suffer from the serialization issue mentioned above and it is the recommended approach to implement the Singleton

 
 

Singleton and object Clone

If Singleton class does not extend any Class, then there is no need to worry about the cloning. Because default clone method will throw CloneNotSupportedException

But if class extends any class, then there is one caveat that if parent class implements Cloneable, then multiple instance of the Singleton class can be created. To prevent this implement following clone method

//throw exception on cloning
public Object clone() throws CloneNotSupportedException {
	throw new CloneNotSupportedException(); 
}

NOTE: Approach 3 mentioned above does not suffer from this cloning caveat and it is the preferred method for implementing Singleton
 
 

What is the difference between Java Singleton object and Spring Singleton bean?

Java Singleton is unique for the JVM, whereas Spring Singleton bean is unique for container. There could be multiple Spring container inside the same JVM, and in that each container will have its own Singleton bean, thus multiple instance of the Singleton inside JVM and hence different from the Java Singleton.