Solved

Reuse database connection without leading to memory leak

Posted on 2008-06-26
4
908 Views
Last Modified: 2013-11-05
Let me just describe the whole situation before I talk about my problem.

We developed a software that connects to a database (MySQL) through a very, very slow connection (There is nothing that can be done to increase the connection speed).

Knowing that the connection is slow, we created a class (Singleton) that handles all MySQL queries and that reuses the Connection.
The way it is done can be seen in the code snippet. There is one method (getStatement) that tries to ask the current Connection for a statement. If that fails (the connection is gone) it reconnects to the database before returning the statement.

This technique worked very well and we were able to reduce the queries delay significantly. But then came our current problem:
The software had a memory leak that caused it to consume all available heap space in a couple of hours.

After investigation we discovered that even though there are no references to the Statement or ResultSet objects, the Garbage Collector does not purge them until the connection is closed ...
To test this theory we closed the connection after each use (as seen on the code snippet) and the memory leak was gone.

Finally, my question:
- Is there a way to free the results of a query (Statement and ResultSet) without closing the connection?
- Is there another way to do achieve this goal (reduce the connection overhead) without these consequences (memory leak)?

Thanks in advance
public ResultSet selectQuery(String sql){

		Statement ts = getStatement();

		if(ts == null) return null;

		try {

			ResultSet res = ts.executeQuery(sql);

			

			lc.close(); // ADDED LATER!!! 

			return res;

		} catch (SQLException e) {

			if(debug) e.printStackTrace();

			return null;

		} catch (Exception ee){ // ADDED LATER!!!

			return null;

		}

	}

Open in new window

0
Comment
Question by:b_loco
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
Nellios earned 250 total points
ID: 21872894
You can use connection poolling for that. With pooling you don't actually close the connection when you invoke connection.close() but you return it to the pool. You can get connections from the pool without having to create new connections etc.

Give dbcp a glimpse.
0
 
LVL 92

Expert Comment

by:objects
ID: 21872962
Are you closing the result set and statement once you are done with them?

0
 
LVL 92

Expert Comment

by:objects
ID: 21873063
don't see how connection pool (of effectively 1) will make any difference to how your app is currently bahaving. You effectively have a connection pool at the moment.
0
 
LVL 10

Expert Comment

by:Nellios
ID: 21873247
@objects:
If I get it right, the problem lies in the the database connection is slow and it costs too much to open and close connections. To that problem connection pooling is the answer.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groupNoAdj 7 86
groupSum5 challenge 5 71
VB Script to add site to Java Exception List 4 50
Problem with Simple PHP/mySQL Query 3 51
Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now