Hi James, Thank you for this answer. Interesting approach to the problem. It appeared that previous version of Oracle10g JDBC thin driver ver.10.2.0.2.0 has some problems when facing network issues like no DB server available. We've downloaded the last Oracle JDBC driver ver.10.2.0.3.0 and everithing worked like it should (no stalling during processing record set when DB in not available and removing of abandoned connections is actually working).
P.S. Your "HACK" approach is cool, but we could not do it that way, because, you know, our customer policy is no new threads should be created within our code! Personally, I do not quite understand that since our web apps natural environment is a multithreaded world. Maybe I'm wrong, but anyhow, customer is the king! :) BR, Drazen -----Original Message----- From: James McIntosh [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 00:35 To: Drazen Nikolic Cc: 'Tomcat Users List' Subject: RE: Connections in tomcat resource "in busy" state forever 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] > __________ NOD32 2144 (20070325) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com --------------------------------------------------------------------- To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
