File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Using single connection cause memory problems? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using single connection cause memory problems?" Watch "Using single connection cause memory problems?" New topic
Author

Using single connection cause memory problems?

Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
Hi all,

In our project, they have used singleton pattern for connection handling part such that not more than one connection is open to the database. So, the same connection will be used again and again. will this actually affect memory usage in any way?
Because recently we had some issues and had to monitor the memory usage and it seemed to be increasing constanly.But when i explicitly closed the connection and opened it again (here, when i say closing the connection means sending it back to the connection pool), the memory seems to remain constant. I dont get the whole problem. Is using the same connection again and again for many operations going to cause a memory probelm? please explain.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19722
    
  20

Using the same connections, and possibly the same (prepared) statements, is not the problem I think.

Do you close all your result sets? Probably not, and this is why the memory leak is there. When you close your connection, the result sets are closed as well, leading to the release of the memory. Since you don't close your connection, you must close your result sets yourself.

You'd best put this in a finally block:


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
hi rob

Thanks for your reply. But i have closed the statements and resultssets in my finally block. But that didnt help.Only when i closed the connection and reopened again the constant increase in memory usage didnt happen. And when i say reopen, i am getting the same connection that was used earlier back from the pool. Not that there is only one connection. I send it back to the connection pool by invoking the close method and retrieving the same connection back. But this is actually preventing the constant increase in memory.
i dont get this point only. can someone please explain.
Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
hi rob

Thanks for your reply. But i have closed the statements and resultssets in my finally block. But that didnt help.Only when i closed the connection and reopened again the constant increase in memory usage didnt happen. And when i say reopen, i am getting the same connection that was used earlier back from the pool. Note that there is only one connection. I send it back to the connection pool by invoking the close method and retrieving the same connection back. But this is actually preventing the constant increase in memory.
i dont get this point only. can someone please explain.
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
I don't understand how closing the connection can make any difference because, typically, the closure will return the connection to the pool. When you get the connection again, then the connection is obtained again from the pool.

Remember, the whole point of pooling is to keep the pool of Connection objects always connected to the database. In fact, you should be maintaining the appropriate number of connections for your application, and that number may be > 1.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
hi Roger Chung-Wee,

what you are mentioning is absolutely correct and i am also not able to actually understand how sending a connection to pool and retrieving it back is actually in anyway relate to memory usage.Thats why i have posted this in the first case.If anyone gets any idea of what is happening can help me.i just thought about one more thing. If i close resultsets and statements without closing connection, do they actually get closed? Because initially in my finally block i had only closed the resultsets and statements and not connections. Anyone has any idea?
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
You should certainly close your Statements because failure to do so will increase the risk of a cursor leak (this is true for for Oracle).

And when you are using a connection pool, then you should also close the Connection as soon as possible as this will return the Connection to the pool for reuse. Failure to do this will cause requests for database access to be blocked until they either time out or a Connection becomes available from the pool again.

I very much doubt whether connection pooling (used with the proper closure of all JDBC objects) has any great impact on memory. If you are still running short of memory, then use a tool to get information on memory usage.
Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
Thanks for all your replies. But let me state the problem clearly.
In our application , for some parts they have used iBATIS and for the rest it is normal connection pooling. We dont have a memory leak in the iBATIS part.But there is a problem with the normal connection pooling part.

They have used Singleton pattern for establishing connection i.e at any point of time only one connection will exist.
Have a look at the method below :

static public SQLgoodies getSQLGoodies()
{
if ( globalSQLGoodies == null)
{
globalSQLGoodies = new SQLgoodies();
Properties props = getConnectionProps();
globalSQLGoodies.connectToDB("", props);
}
return globalSQLGoodies;
}
The method connectToDB will establish the connection and there is another method getConnection() that will return the connection. So whenever connection to the DB is required the following will be used

connection = getSQLGoodies().getConnection();

The connection is not closed anywhere and so only the very first time the connectToDB() method will be called and from there on the same connection will be used again and again until the user closes the application.
So,no where has the connection,statement or resultsets have been closed.

Is not closing the resultsets and statements the cause for memory leak?

Is there any meaning to closing the resultsets and statements without actually closing the connection?
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Why would a singleton return only once instance of the Connection? And even if it did, it would make no sense as you are using connection pooling. You should close all JDBC objects in the way that I have said in a previous post.
Amali Prem
Greenhorn

Joined: Dec 13, 2007
Posts: 17
ok. Thanks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using single connection cause memory problems?