asked on

Problem with closing cursors in web application.

Hi all,

I have developed a web application with lots of database interraction. I use a javax.sql.DataSource, which I declare in my server.xml
As webserver I use tomcat 5.5. All the queries for the database are stored procedures, so I use CallableStatement.

The problem is that after a while I get an "maximum open cursors exceeded" sqlexception. I have triple checked and all of my resultsets, callablestatements and connection are being closed in a finally block.

So, I am starting to think that, although I close everyting, not everything is being closed.

So here is my question. If I close a resultset, callablestatement or a connection are they closed immediately, or is there a period of time that should pass before everything is closed (perhaps with gc).

I am using a Oracle 8.1.7 database.

Does anyone know this?

TimYates

Can you post an example of how you are opening and closing your resultsets, statements and connections?

THis should be ok... if done right..

Tim

fargo

also check the max. open cursors allowed in the database. check the init.ora file for the db.

have a detail here
http://asktom.oracle.com/pls/ask/f?p=4950:8:15383759053304836584::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1041031921901

tcollogne

ASKER

I will post the code first thing tomorrow morning. As for the max open cursors allowed. This is configured. We've set it to 250. I think that should be enough, or not?

fargo

hi, the first thing is to check ur code for closing rs, stmt, conn...if that seems fine. It simply means the open cursors setting is not enough for your application traffic..and you need to increase it. 250 is ok figure. In a good traffic production db, we generally keep it to 500.

TimYates

It really depends on how many people you have accessing the site (as forgo says), the size of your connection pool, and the length of time that connections are held open...

It's probably a missed connection, or statement which you don't close properly... maybe when an exception is thrown...

Tim

suprapto45

Please also check your Stored Proc whether the cursors are closed properly.

David

tcollogne

ASKER

This is the code of one method. All methods work the same way. I close my Connection, ResultSet and Statement in the finally block. I thought that everything inside a finally block is executed no matter whether an exception is thrown or the method is finished correctly. Am I wrong?

I also wanted to add that the stored procedures are oracle procedures, not java. The class which holds the method below (and other methods) is stored in the session. Could this be the reason of the open cursors?

public MijnVtvResponse getGegevens(String lid_id, String paswoord, SessionContainer sc, HttpServletRequest request) {
            MijnVtvResponse mvr = new MijnVtvResponse();

            HttpSession session = request.getSession();
            UserInformation ui = sc.getUserInformation(request);
            Logger logger = Logger.getLogger(this.getClass().getName() + ".getGegevens");

            Connection conn = null;
            CallableStatement cmdPersgeg = null;
            ResultSet rs = null;

            try {
                  boolean isConnected = false;
                  if (ds != null) {
                        conn = ds.getConnection();
                  }
                  else {
                        mvr.setResult(false);
                        mvr.setErrorTextShort("Systeemfout. Contacteer de beheerder.");
                        mvr.setErrorTextLong("Database connectie kon niet worden opgezet, want datasource is null");
                        logger.error("Database connectie kon niet worden opgezet, want datasource is null");
                  }

                  // check if the connection is not null
                  if (conn != null) {
                        isConnected = true;
                  }

                  if(isConnected) {

                        cmdPersgeg = conn.prepareCall("{call P_VTVSITE.laad_mijngegevens(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                        cmdPersgeg.setString("lid",lid_id);
                        cmdPersgeg.registerOutParameter("vtv_nummer",Types.VARCHAR);
                        cmdPersgeg.registerOutParameter("naam",Types.VARCHAR);

                        cmdPersgeg.registerOutParameter("rc_clubemail",OracleTypes.CURSOR);

                        logger.debug("Procedure wordt aangeroepen");
                        cmdPersgeg.execute();
                        logger.debug("Procedure uitgevoerd, ophalen van resultaten ...");

                        ui.setLid_id(lid_id);
                        ui.setVtv_nummer(cmdPersgeg.getString("vtv_nummer"));
                        ui.setPaswoord(paswoord);
                        ui.setNaam(cmdPersgeg.getString("naam"));

                        logger.debug("Ophalen resultset rc_clubemail");
                  rs = (ResultSet)cmdPersgeg.getObject("rc_clubemail");
                  logger.debug("Resultset is opgehaald rc_clubemail");

                  String email = null;

                  while (rs.next() && email == null) {
                        email = rs.getString("email");
                  }

                  ui.setClubemail(email);
                  rs.close();
                        logger.debug("Resultaten werden opgehaald.");

              sc.setUserInformation(ui,request);
              mvr.setResult(true);

                  }
                  else {
                        mvr.setResult(false);
                        mvr.setErrorTextShort("Systeemfout. Contacteer de beheerder.");
                        mvr.setErrorTextLong("Database connectie kon niet worden opgezet");
                        logger.error("Database connectie kon niet worden opgezet");
                        session.removeAttribute("userInformation");
                  }
            }
            catch (SQLException sqle) {
                  mvr.setResult(false);
                  mvr.setErrorTextShort("Systeemfout. Contacteer de beheerder.");
                  mvr.setErrorTextLong("Fout bij het uitvoeren van de query met lid_id '" + lid_id + "' : " + sqle.getMessage());
                  logger.error("Fout bij het uitvoeren van de query met lid_id '" + lid_id + "' : " + sqle.getMessage());
                  session.removeAttribute("userInformation");
            }

            finally {
                  try {
                          if (rs != null) {
                               rs.close();
                               logger.debug("CallableStatement werd afgesloten");
                          }
                  }
              catch (SQLException e2) {
                        logger.error("CallableStatement kon niet worden afgesloten : " + e2.getMessage());
              }

                  try {
                          if (cmdPersgeg != null) {
                               cmdPersgeg.close();
                               logger.debug("CallableStatement werd afgesloten");
                          }
                  }
              catch (SQLException e2) {
                        logger.error("CallableStatement kon niet worden afgesloten : " + e2.getMessage());
              }

              try {
                     if(conn != null && !conn.isClosed()) {
                            conn.close();
                            logger.debug("Connectie met de database werd afgesloten.");
                     }

                  }
              catch (SQLException e2) {
                        logger.error("Database connectie kon niet worden afgesloten : " + e2.getMessage());
              }

            }

            return mvr;
      }

TimYates

>> Am I wrong?

No...that looks fine...

Hmmm...

It could be that you are opening cursors and not shutting them in the stored procedures though couldn't it?

Tim

ASKER CERTIFIED SOLUTION

fargo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fargo

>> the java code seems fine. The only thing i can say here is either the closing of connection from doesn't really work. OR the stored procedure is not keeping the cursors open.

should read

the java code seems fine. The only thing i can say here is either the closing of connection from java doesn't really work. OR the stored procedure is keeping the cursors open.

TimYates

>> The only thing i can say here is either the closing of connection from java doesn't really work. OR the stored procedure is keeping the cursors open.

I have never had a problem with closing connections in an almost identical way...

It must be cursors in the stored procedures...

fargo

i agree with the java code handling Tim. But i m just giving the options to check -:)

tcollogne

ASKER

We have difficulty checking the open_cursors due to a bug in the database 8.1.7. If you scroll down to the bottom of this page http://www.orafaq.com/node/758, you can view this issue.
We are going to check the stored procedures now, maybe we can find something there.

Concerning the amount of open_cursors : the appliction is a website with an average of 5000 visitors a day. Is an amount of 250 open_cursors enough?

fargo

as i said, 250 is a fine figure. But if the memory of the machine is good enough..u can even set it to 500.

..."This parameter does not affect performance in any way but Oracle will now need a little more memory to store the cursors. It will affect only used memory, not the resources that Oracle will need to support this increased value"....

Better now is to check the stored procedure. The EXPLICIT cursors has to be close properly in the stored procedures (if there are any).

tcollogne

ASKER

We think that there were issues cosing the cursors when an exception occured (for example NoDataFound). When such an exception is thrown by the procedure, we don't have a reference to the cursor, since the resultset is still null (not yet fetched). So now we catch the exception in the stored procedures and close the cursors there.

Now we have to wait to see whether that solves it. Keep you posted.

tcollogne

ASKER

It seems that the problem with the cursors is solved. Now we have an issue with the connection pooling.

At a certain time during the day, the database tables get updated. During this time, we raise an application error in our oracle stored procedures and show on our site that the database is down and will be up again in a moment.
During this time, the number of connection with the database increase dramatically. The result : connection pool exhausted.

Any idea why this is. It seems it only happens when an sqlexception is thrown.

SOLUTION

TimYates

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tcollogne

ASKER

I use the conn.isClosed() method. Could it be that this methods returns that connection is closed, allthough the connection is not really closed.
Because in my code I don't call conn.close() when conn.isClosed() returns true.

TimYates

Maybe. It shouldn't do... But maybe...

I always just use:

try { if( rslt != null ) rslt.close() ; } catch( SQLException ex ) {}
try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
try { if( conn != null ) conn.close() ; } catch( SQLException ex ) {}

what version of the oracle drivers are you using? There maybe a known problem wih this...

Hmmm...

tcollogne

ASKER

I use the latest jdbc version : 10g Release 2 (10.2.0.1.0) JDBC Drivers.

I think I will try with removing the isClosed() check. Perhaps that solves it.

tcollogne

ASKER

We have increased the amount of connection in the pool. It seems that did the trick. Perhaps that when we get the throw the error about the database being down, the connection needs some time to close. The site is up now for 2 days.

We will monitor it a little bit more. If the exception does not occure anymore, I can close this post.

TimYates

fingers crossed... :-/

Hope it hasn't just made the problem take longer to show itself ;-)

Good luck!

Tim