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 18.104.22.168 using a jdbc connection pool created in Glassfish with the following properties:
Resource Type: javax.sql.DataSource
Datasource classname: oracle.jdbc.pool.OracleDataSource
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?
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.
> 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