java.sql.SQLException: No available resource. Wait-time expired.
Hi All,
I am using Sun Application Server 7 and backend is Oracle 9i.
In our project we are getting Exception as follows:
--
java.sql.SQLException: No available resource. Wait-time expired. at com.sun.enterprise.resource.JdbcDataSource.internalGetConnection(JdbcDataSource.java:251)at com.sun.enterprise.resource.JdbcDataSource.getConnection(JdbcDataSource.java:98) at com.idealake.sun.common.DBManager.getConnection(DBManager.java:36)
--
(i.e there are may be connections/cursors not closing at Database side
even if i am closing the connection as follows :
--
finally{try{if(res !=null){res.close();}if(stmt !=null){stmt.close();}if(conn !=null){conn.close();}}catch(SQLException se){}}
--
Is there any other way to close the connction?
thanks and regards
# 2
There is a related bug in Oracle: Bug 4420032
PROBLEM STATEMENT:
The Oracle client file ojdbc_14.jar is not handling exceptions correctly. The connection pool slowly runs out of connections and hits the oracle db limit of max_sessions. Increasing the db max_sessions does not help as the number of sessions continue to increase.
The vendor developers of ct's application have identified a code problem with the Oracle client file ojdbc_14.jar. Their description follows.
The following is a technical description of the Oracle JDBC client library issue. The jar file in question is: ojdbc_14.jar. This issue is also present in the 9.2.05 and 9.2.06 versions of the client code.
The socket.close() call in TcpNTAdapter.disconnect() does not properly handlenetwork exceptions. Where the exception should be handled within the code segment calling socket.close(), the class instead throws an exception withoutensuring the socket is closed. The socket is not subsequently closed by clients of the class. This ultimately results in a connection leak. Overtime, the maximum number of Oracle database sessions is reached. The only work around for this issue in production, is to restart the effected processes.
The code segment:
public void disconnect()
throws IOException {
socket.close();
socket = null; }
should be along the lines of:
public void disconnect()
throws IOException {
try{ socket.close(); }
catch(IOException ioe){ throw ioe; }
finally{ socket = null; } }
The original code segment will not execute the statement "socket = null" when . an exception occurs. Given the fact that the exception is not subsequently processed properly, the object is never dereferenced, and the socket remains open for the life of the process. Adding this statement in the finally block . ensures the object will ultimately be destroyed by the garbage collector. . ct tested this change in their lab and found that the recommended modification successfully resolved the issue.
This problem has occurred with the previous ct's application releases, but the vendor has only recently been able to isolate the root cause. The JDBC connection pool slowly runs out of connections and hits the oracle db limit of max_sessions.
Increasing the db max_sessions does not help as the number of sessions continue to increase. This problem has been occuring in Production over the past few releases of the application, but usually the connections leak slowly. This problem can be duplicated under heavy load in just a few minutes . in the Loadtest environment.
