Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Doubt in closing connection, resultset, statemnet object??? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Doubt in closing connection, resultset, statemnet object???" Watch "Doubt in closing connection, resultset, statemnet object???" New topic
Author

Doubt in closing connection, resultset, statemnet object???

gopal kishan
Ranch Hand

Joined: Feb 23, 2005
Posts: 99
Hi All,

If i not close the Statement, Resultset, Connection object then
what will happen??
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

In pooling environments this could increase contention for free connections.


My Blog: Down Home Country Coding with Scott Selikoff
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by gopal kishan:
Hi All,

If i not close the Statement, Resultset, Connection object then
what will happen??


Many bad things.

- wasted memory and other system resources in your program
- wasted memory and other system resources on the database server

The potential outcomes of this include any/all of the following

- You will run out of connections that can be opened or used
- You will not be able to create any new result sets
- You will not be able to execute any queries of any kind
- Your program will get very slow
- The database server get very slow
- Your program will crash
- The database server will crash

So in short. You must always make sure to close all the resources (ResultSets, Statements, Connections) that you aquire.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

Your database will crash? That seems a bit extreme, care to share an example?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Scott Selikoff:
Your database will crash? That seems a bit extreme, care to share an example?


Two that I have seen this with are MS-SQL Server and MySQL. In neither of those cases was the culprit Java based however the problem was the same abuse of not closing connections, result sets etc.

In the SQL Server case the bad code was written as a mixture of ASP and COM components. In the MySQL case it was a mixture of ASP and PHP.

But in both cases the servers experienced such heavy resource loads (particularly with sockets) that eventually the OS (Windows 2000 Server) would crash taking the database down with it.

Most unpleasant.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Scott Selikoff:
Your database will crash? That seems a bit extreme, care to share an example?


In common older configurations of Oracle/Veritas failover clusters, Veritas will force a failover to the standby database when the configured Oracle process limit is reached (and in direct connection mode, each connection = 1 additional process). Sucah a failover will take between 10 seconds and 5 to 10 minutes, depending on how much interrupted transactions need to be rolled back. And then the standby database will attempt to fail back when its process limit is reached, but if the primary DB has not yet been reset (commonly done manually), then your DB is down until you can fix it.

Alternately, you can set your Oracle process limit astronomically high; eventually your system will crash after all swap space is consumed.

Or you can simply forgo things like failover and set a relatively high process limit that won't exhaust swap space; Oracle will simply start refusing connections because it's been told not to spawn more child processes.

You MUST close your connections, or be considered a clueless amateur.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by gopal kishan:
Hi All,

If i not close the Statement, Resultset, Connection object then
what will happen??


Oh, by the way, it most modern, well-behaved drivers, closing a Statement will close any/all of its ResultSets, and closing a Connection will close all of its Statements; this is mandated by the 3.0 JDBC API, and probably earlier versions as well. Sadly, a few drivers didn't do this right in the past and some people explicilty close their ResultSets and Statments to be sure.

In some databases, ResultSets and Statements also consume significant database resources and it's good practice to close them as soon as they're not needed.
Hemant Agarwal
Ranch Hand

Joined: Nov 21, 2005
Posts: 138
Your database will crash? That seems a bit extreme, care to share an example?


But if our object that is calling and holding the resultset destroyed, then the resultset would also destroyed.

Then what is the need of closing the connections?
Annie Smith
Ranch Hand

Joined: Mar 05, 2005
Posts: 172
Originally posted by Hemant Agarwal:

But if our object that is calling and holding the resultset destroyed, then the resultset would also destroyed.




Cheers!<br /><b>Annie</b>
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

I'm still skeptical of those who claim the database will fail from not closing connections. Granted the database may become overloaded via connections or have 100% utilization, but that could happen just as easily if too many people are using the database at once, or if one person is doing too many complicated joins.

I was thinking more along the lines of crashes that could only happen if you leave connections open, and could not happen otherwise.
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
The answer of course is, it depends.

If you are writing a simple java program which opens a connection, gets some data from the database and exits, chances are you could get by without closing resultsets, statements and connections without any adverse effects (they'll all be closed when your program exits). Of course if you are looping and opening a new connection in each loop, then this does not apply.

If on the other hand your program will not terminate but continue running, then the situation changes dramatically.

There are two scenarios with connections :
You are not using connection pooling or
You are using connection pooling

#1. You are not using connection pools, so you explicitly create and close DB connections. If your program does not close connections, and keeps opening new connections, it is a matter of time before you run out of all available connections. The limit of maximum connections is a configurable database parameter. When this happens, the database will not crash, it will just not allow any new connections to the database. Applications which already have connections open will not be impacted, but any applications which need to open a new connection will error out and hang. If you terminate or close any existing connections, then you'll be able to continue.

Of course you could increase the limit of max open connections. Each open connection, depending upon which database you are using, and how it is configured, will require a network socket, file descriptor, even a dedicated process, and some memory resources. So eventually you'll hit operating system resource limits.

#2. You obtained a DB connection from a connection pool, so you should return the connection to the pool, not close it. If you do not return the connection, and keep taking new connections, at first you'd hit the limit for maximum connections set in the pool. If you increase that, then you'd hit the maximum connections to the database limit and things as described in #1 will happen.

If you do not close your result sets and statements (which you don't need anymore) and keep opening new statements and result sets, eventually you'll exceed the maximum number of cursors a connection can open. Can you increase the limits to delay this, definitely. However there is a cost associated in terms of memory on the database and the JVM with each open cursor. So it is good practice to close the result sets and statements in a finally block when you are done with them.


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
I'm still skeptical of those who claim the database will fail from not closing connections.


I fail to see why you would still be skeptical. In this thread you have two people who have experienced such failures with three different databases.

Moreover why do you think this advice is given not just here but all over the place time and again including the JDBC specs and by vendors themseleves? What is the conclusion to a scenario in which more and more resources are consumed by a program and not released?

Granted the database may become overloaded via connections or have
100% utilization, but that could happen just as easily if too many people
are using the database at once, or if one person is doing too many
complicated joins.

I am afraid I am unable to understand the point you are making here.

If the database cannot accept any more connections that for all intents and purposes it might as well have crashed. Similarly if it locks up to the point where one cannot administer it to correct the problem I would say it has crashed.

In my cases the mysql process froze up and had to be killed. In the case of SQL server the actual OS locked up and had to be physically rebooted.

And in both cases rectifying the code resolved the problem. In the case of MySQL the offending code was fixed to release the connections. In the case of SQL server the offending code was given a connection pool to connect to instead. At any rate my experience for me proves the theorem that bad code (aka the kind that does not release resources) can in fact cause a database server to lock up to the point of being totally nonresponsive. And the proof is that by fixing what was thought to be the culprit the problem was resolved.

Certainly if you are in a lower load environment one may be able to get away with bad practices because eventually the sockets timeout etc however code like that will scale poorly and perhaps even disasterously and it is such a simple practice really to close all the resources one uses that I do not understand why this should be such an issue at all.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Scott Selikoff:
I'm still skeptical of those who claim the database will fail from not closing connections.


Maybe your DB has super magic powers... Feel free to try it yourself... Here's some handy code, just update the first 4 strings. For some DBs, you might need to give it extra memory before the DB dies/becomes unresponsive:
java -Xmx512m foo

Don't do this on important systems of course, unless you want to make people really really mad.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Doubt in closing connection, resultset, statemnet object???