Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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?
0
tcollogne
Asked:
tcollogne
  • 8
  • 7
  • 6
  • +1
2 Solutions
 
TimYatesCommented:
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
0
 
fargoCommented:
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
0
 
tcollogneAuthor Commented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fargoCommented:
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.
0
 
TimYatesCommented:
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
0
 
suprapto45Commented:
Please also check your Stored Proc whether the cursors are closed properly.

David
0
 
tcollogneAuthor Commented:
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;
      }
0
 
TimYatesCommented:
>> 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
0
 
fargoCommented:
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.

One way is to check how many cursors are open after and before closing the connection.
select sid, sql_text from v$open_cursor order by sid, sql_text

closing the connection, definitely ensures that the cursors are freedup...both from java and oracle. Next task is to refine the stored procedure handling with cursors. Try calling a stored procedure which has no handling inside...just an empty one, to see where the problem lies...in java or in oracle.

if you have time, have a look at the following post regarding the open_cursors
http://www.orafaq.com/node/758

0
 
fargoCommented:
>> 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.

0
 
TimYatesCommented:
>> 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...
0
 
fargoCommented:
i agree with the java code handling Tim. But i m just giving the options to check -:)
0
 
tcollogneAuthor Commented:
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?
0
 
fargoCommented:
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).
0
 
tcollogneAuthor Commented:
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.
0
 
tcollogneAuthor Commented:
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.
0
 
TimYatesCommented:
Again, sounds like somewhere in your code you're missing the:

    Connection conn = null ;
    PreparedStatement stmt = null ;
    ResultSet rslt = null ;
    try
    {
        // get the connection, statement, etc
    }
    catch( SQLException ex )
    {
        ex.printStackTrace() ;
    }
    finally
    {
        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 ) {}
    }

pattern, OR you are reusing a statement without closing the original like:

    stmt = conn.prepareStatement( "SELECT * FROM BLAH" ) ;
    rslt = stmt.executeQuery() ;
    // loop through rslt
    stmt = conn.prepareStatement( "SELECT * FROM BLAH" ) ;  // DID NOT CLOSE STATEMENT!!  OR RESULTSET!!

Tim  
0
 
tcollogneAuthor Commented:
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.
0
 
TimYatesCommented:
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...
0
 
tcollogneAuthor Commented:
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.
0
 
tcollogneAuthor Commented:
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.
0
 
TimYatesCommented:
fingers crossed... :-/

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

Good luck!

Tim
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now