This discussion is archived
2 Replies Latest reply: Jul 25, 2013 12:26 PM by jschellSomeoneStoleMyAlias RSS

Problem with cursors using jdbc Connection Pool in Glassfish Server

Samuel Rabini Newbie
Currently Being Moderated

We have a web service installed on Glassfish Server Open Source Edition 3.1.2 (build23). The web service is developed in Java. The web service connects to an Oracle Database 11.2.0.3 using a jdbc connection pool created in Glassfish with the following properties:

Resource Type: javax.sql.DataSource

Datasource classname: oracle.jdbc.pool.OracleDataSource

Ping: enabled

Initial and minimum pool size: 4

Maximum pool size: 32

Pool resize quantity: 2

Idle timeout: 300 seconds

Max wait time: 60000 milliseconds

Isolation level: guardanteed

Advanced properties: all set as default

Additional Properties: set only to establish the connection (URL, username and password)

The connection works fine and so far we did not have any issue.

Recently we add a new method to the web service that may extract in a single call a big amount of data. All the web services method make use of a library where all the query and calls to Oracle Packages are saved. Most of the calls (including the one that is creating the problem) create a statement, execute a query and returns a ResultSet without closing the statement (otherwise the ResultSet won't be accessible). The Statement in never closed directly. When the method receives the ResultSet uses it to create to fill a custom data structure and return it to the user that calls the web service. When the method ends the connection to the database is always closed calling the connection.Close() method and this is executed for sure even if the method terminates with an exception because the call to the Close method is executed in a finally block. This should ensure that all the jdbc resources connected with this connection are released immediately when the metod terminates.

The problem is that based on the amount of data returned by the method (and so basically based on the amount of data returned by the query done on the database) after a certain amount of subsequent calls to the method (it may be even only one if the query returns lot of records, generally more than 2000) any call done on the web service (any method, not only this one) returns an error with this message: java.sql.SQLEXception: ORA-01000: maximum open cursors exceeded

The only way of resolving the issue is to wait for a certain amount of time (according to some test we may need to wait up to 10 minutes) before doing another call or restarting the Glassfish server causing the connection pool to be reset.

This error affects only the connections in the Glassfish connection pool. All the other connections to the same database and to the same database service are not affected and keep working fine. We already try to increase the numbers of maximum open cursors directly on the database but it did not work.

So the problems seems to be in the glassfish jdbc connection pool.

Can anyone help us? Is there any parameter that we should set to increase the number of cursors for the glassfish jdbc connection pool? Is there any different way of programming the web service to avoid the problem?

 

Thanks

Regards

  • 1. Re: Problem with cursors using jdbc Connection Pool in Glassfish Server
    Joe Weinstein Expert
    Currently Being Moderated

    It sounds like you may need to either do your JDBC code better, or use a better appserver.

    You should rewrite your code so at the very least, when you are done with your result set,

    you can call the ResultSet's getStatement() method and close the statement. WebLogic

    connection pools would know that you'd abandoned statements when you closed the

    connection, and would clean them up for you. The DBMS needs a cursor for every open

    statement, so if you leak statements (lose the handle on them without first closing them)

    you will leak cursors, at least with the feeble pooling available in glassfish.

  • 2. Re: Problem with cursors using jdbc Connection Pool in Glassfish Server
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > Most of the calls (including the one that is creating the problem) create a statement, execute a query and returns a ResultSet without closing the statement (otherwise the ResultSet won't be accessible). The Statement in never closed directly

     

    And that is the problem right there.

     

    Is there any different way of programming the web service to avoid the problem?

     

     

    Fix your code and it will work.  You should be doing the following ALWAYS

    - Open the resources

    - Extract the data into something

    - Close the resources

    - Return the data

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points