Hi Drazen,
Looking at the Oracle site there is a new JDBC driver available which
may address this problem but I haven't had time to investigate. Here is
their bug fixes list:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_jdbc_10203.html
But if it doesn't solve it you could implement a HACK :)
You must put this method in a class with the package
"oracle.jdbc.driver" this allows you access to the
PhysicalConnection.abort() method which is package protected.
You must also override the "secure" in meta-inf/manifest.mf within the
oracle jar to allow your own class to work along side their jar.
public void abort(Connection conn) {
try {
if (conn instanceof PhysicalConnection) {
System.out.println("Attempting to abort connection");
PhysicalConnection pcon = (PhysicalConnection)conn;
pcon.abort();
System.out.println("Connection aborted");
}
} catch (Exception e) {
System.out.println("Exception aborting connection: " + e);
}
}
Once you have done this wrap your query in a thread and if it takes too
long call your abort method.
Another thing which I have noticed with Oracle's JDBC driver is a
TimeOut Thread is started by the first query which the driver executes.
This gets a reference to the webapp class loader which calls it, when
you redeploy your application the webappclassloader is never garbaged
because of this reference.
To work round this you can call a query such as "select * from dual"
before the webapps are loaded using something like a
org.apache.catalina.LifecycleListener referenced in the "Server" tag
within the conf/server.xml. This gives the ownership of the thread to
the common class loader.
Kind regards
James
On Fri, 2007-03-23 at 13:30 +0100, Drazen Nikolic wrote:
> Yes, David, you were right. The same behavior we got with direct JDBC
> access.
> Our database server is Oracle 10.2.0.2.0 and we use thin JDBC driver with
> the same version. Our JAVA environment is 1.5. Tomcat version 5.5.17. There
> are no firewalls between Tomcat and Oracle (direct LAN connection).
>
> Currently we do not have a right solution to deal with the connections which
> were in processing (getting the result set) when database goes down. Those
> connections stays forever busy in the pool, and could not be reused, as well
> as Tomcat thread which executed the statement to a connection.
> Note that when database is not accessable, before executing the statement,
> will not produce any "busy" connection in the pool and never-ending Tomcat
> thread, since they will be auto removed by validatonQuery (DBCP parameter).
>
> We performed the similar tests with Oracle8i (8.1.7) and thin JDBC driver
> for this Oracle version and got the same results.
>
> Customer does not anymore claim this problem as a big issue, since other
> applications installed there (developed by different companies) share the
> same problem. We hope that DB server will be always awailable... and that
> our application will not be on a heavy load in the same time when DB server
> "suddenly" goes down.
>
> Thank you for your answers.
>
> BR,
> Drazen
>
> -----Original Message-----
> From: David Smith [mailto:[EMAIL PROTECTED]
> Sent: 22 March 2007 18:01
> To: Tomcat Users List
> Subject: Re: Connections in tomcat resource "in busy" state forever
>
> At the point you say this is stalling, we aren't dealing with tomcat code
> (or even DBCP code). The issue appears to be between your jdbc driver and
> the database server. You'd probably experience the exact same behavior with
> the same query in a console java app w/o database pooling. Have you checked
> with the db vendor for any known issues in your jdbc driver or database
> service? Also what is the database server doing at the moment this occurs?
> Are there any firewalls or other network issues that might bring this on
> between tomcat and the db server?
>
> --David
>
> Drazen Nikolic wrote:
>
> >Thank you Mark for answering.
> >
> >We are still experiencing the problems: when database becomes
> >unavailabe, in exact time after CallableStatement.execute() is finished
> >(executed) and when we start retrieving the records sets from called
> >stored procedure, thread is never passing the line statement.getObject()
> (we saw that in debuger).
> >Here is the part of the source code:
> >
> >for (int i = 1; i <= outParametersCount; i++) {
> > resultSet = (ResultSet) statement.getObject(inputParameterCount +
> i);
> > resultSetList.add(resultSet);
> > resultVector.add(packResultData(resultSet, true)); }
> >
> >Do anyone have a clue why this happen?
> >
> >NOTE: We're using tomcat resouce datasouce to access database.
> >
> >BR,
> >Drazen
> >
> >-----Original Message-----
> >From: Mark Thomas [mailto:[EMAIL PROTECTED]
> >Sent: 22 March 2007 00:48
> >To: Tomcat Users List
> >Subject: Re: Connections in tomcat resource "in busy" state forever
> >
> >Drazen Nikolic wrote:
> >
> >
> >>Is there a possibility to remove those connection, or to threat them
> >>somehow not be become busy forever?
> >>
> >>
> >
> >Configuring a time-out (to close them if they are open too long) and a
> >validation query (to fix any connections broken when the db goes down)
> >should help.
> >
> >Mark
> >
> >---------------------------------------------------------------------
> >To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe,
> >e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
