tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adam Myatt <myat...@potsdam.edu>
Subject RE: mysteriously dying connections (Oracle <-> tomcat)
Date Thu, 21 Jun 2001 19:04:38 GMT
I do something extremely similar to the code below but at the end I return
the connection to the connection pool. I do not at any tiem use
conn.close()... should I just close the connection instead of using
connection pooling? Would closing the connection instead of returning them
to the pool free up the cursors?


At 11:21 AM 6/21/2001 -0700, you wrote:
>> Is there a sure fire way to have the cursors close when the servlet is
>done using them. 
>
>I haven't used connection pooling (it's too easy to write your own that does
>what you want).
>
>But the only sure-fire way to close your ResultSets and Statements is,...
>call the close() method.  The safest way to do that is to use try/finally
>blocks, like,
>
>	Statement stmt = ...;
>	try
>	{
>	  ResultSet rs = stmt.executeQuery(...);
>	  try
>	  {
>	    // use the results,...
>	  }
>	  // catch whatever you want here
>	  finally
>	  {
>	    rs.close();
>	  }
>	}
>	// catch whatever you want here
>	finally
>	{
>	  stmt.close();
>	}
>
>                                        -- Bill K. 
>
>-----Original Message-----
>From: Adam Myatt [mailto:myatt83@potsdam.edu]
>Sent: Thursday, June 21, 2001 9:51 AM
>To: tomcat-user@jakarta.apache.org
>Subject: RE: mysteriously dying connections (Oracle <-> tomcat)
>
>
>
>All right. I found the sql_text listing in the open cursor view. Is there a
>sure fire way to have the cursors close when the servlet is done using
>them. (And if so is using connection pooling and closing the recordset and
>statement one way?)
>
>
>At 07:33 PM 6/21/2001 +0300, you wrote:
>>     you can use v$parameter view for getting OPEN_CURSORS paremeter value.
>
>>or in sql plus or svrmgrl type 
>>show parameter OPEN_CURSORS  
>>  -----Original Message----- 
>>From: William Kaufman [mailto:WKaufman@viquity.com] 
>>Sent: Thursday, June 21, 2001 7:12 PM 
>>To: 'tomcat-user@jakarta.apache.org' 
>><> tomcat)  
>>  Eh,... It might have a different name on your version of Oracle, or be
>under 
>>  Try running,           SELECT OWNER, VIEW_NAME 
>>         FROM ALL_VIEWS 
>>         WHERE VIEW_NAME LIKE '%OPEN%CURSOR%';   and see what pops up.     
>>                                     -- Bill K.    -----Original
>Message----- 
>>From: Adam Myatt [mailto:myatt83@potsdam.edu] 
>>Sent: Thursday, June 21, 2001 8:54 AM 
>>To: tomcat-user@jakarta.apache.org 
>><> tomcat)  
>>  Bill,   What if the V_$OPEN_CURSOR is not present in the SYS schema.
>>Using the DBA 
>>Studio, I looked through all the schemas and users and V_$OPEN_CURSOR 
>>doesn't exist anywhere. Would it be under an alternative name?    - Adam  
>>At 08:32 AM 6/21/2001 -0700, you wrote: 
>>>These look like two different issues: 
>>> 
>>>1) Oracle is running out of cursors. 
>>> 
>>>  a) Make sure you close every ResultSet, Statement and Connection when 
>>>  One trick you can use on Oracle is to log into the 
>>>database in SQL*Plus as SYS while your application is running, and do, 
>>>       SELECT SQL_TEXT FROM V_$OPEN_CURSOR; 
>>>to see the SQL for the cursors you have open. 
>>> 
>>>  b) Try reusing connections and (prepared) statements wherever possible 
>>>(assuming you use a common Oracle logon for all your accesses). 
>>> 
>>>  c) Up the number of allowed open cursors, by putting 
>>>       open_cursors = 500 
>>>or so in your database's init.ora file. 
>>> 
>>>2) Tomcat is running out of threads. 
>>> 
>>>  Are you actually getting dozens of people connecting at the same time? 
>>>"" parameter for PoolTcpConnector in your 
>>>server.xml. 
>>> 
>>>                                        -- Bill K.  
>>> 
>>>-----Original Message----- 
>>>From: Jan M. STANKOVSKY [mailto:jan@ifs.univie.ac.at] 
>>>Sent: Thursday, June 21, 2001 8:16 AM 
>>>To: tomcat-user@jakarta.apache.org 
>>><> tomcat) 
>>> 
>>> 
>>>Same here but we have the stopping of the tomcatserver very frequently. 
>>>At the end of the term when the students (aprox 50 working at the same 
>>time) 
>>>got very busy the tomcatserver 
>>>stoped replying frequently. We have a configuration with 
>>>& 3.2.2 and one 
>>Oracle817 
>>>Server on Soalris 7  
>>> 
>>>Once I traped this error: 
>>>-------------------------- 
>>>2001-06-19 07:41:23 - ThreadPool: Pool exhausted with 100 threads. 
>>>2001-06-19 09:02:25 - ThreadPool: Caught exception executing 
>>>org.apache.tomcat.service.TcpWorkerThread@3598c3, terminating thread -  
>>>java.lang.IllegalStateException 
>>>        at org.apache.tomcat.util.ThreadPool.runIt(ThreadPool.java:227) 
>>>        at 
>>>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:405) 
>>>        at 
>>>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
>
>>>        at java.lang.Thread.run(Thread.java:484) 
>>>--------------------------- 
>>> 
>>>& 09:02:25 (the shutdown of the tomcat 
>>>server) there was no activity except angry students (times are pm). 
>>> 
>>>And this: 
>>>------------------------- 
>>>2001-06-20 04:51:10 - Ctx( /a9303541 ): Exception in: R( /a9303541 + 
>>>/servlet/shop1 + null) - java.lang.NullPointerException 
>>>       <>(shopmanager.java:16) 
>>>       <>(shop1.java:14) 
>>>        at java.lang.Class.newInstance0(Native Method) 
>>>        at java.lang.Class.newInstance(Class.java:237) 
>>>        at 
>>>org.apache.tomcat.core.ServletWrapper.loadServlet(ServletWrapper.java:268)
>
>>>        at 
>>>org.apache.tomcat.core.ServletWrapper.init(ServletWrapper.java:289) 
>>>        at org.apache.tomcat.core.Handler.service(Handler.java:254) 
>>>        at 
>>>org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372) 
>>>        at 
>>>org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:
>7 
>>9 
>>>7) 
>>>        at 
>>>org.apache.tomcat.core.ContextManager.service(ContextManager.java:743) 
>>>        at 
>>>org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(Htt
>p 
>>C 
>>>onnectionHandler.java:210) 
>>>        at 
>>>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416) 
>>>        at 
>>>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
>
>>>        at java.lang.Thread.run(Thread.java:484) 
>>> 
>>>2001-06-20 05:01:12 - ThreadPool: Caught exception executing 
>>>org.apache.tomcat.service.TcpWorkerThread@2c7887, terminating thread -  
>>>java.lang.IllegalStateException 
>>>        at org.apache.tomcat.util.ThreadPool.runIt(ThreadPool.java:224) 
>>>        at 
>>>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:405) 
>>>        at 
>>>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
>
>>>        at java.lang.Thread.run(Thread.java:484) 
>>> 
>>>2001-06-20 05:01:12 - ContextManager: Removing context Ctx( /examples ) 
>>>------------ 
>>> 
>>>5:01:12 was the time where I shutdowned the tomcat server.... 
>>> 
>>> 
>>>I now shutdown/restart the server hourly and it seems we don't have 
>>>unsheduled stoppings. 
>>> 
>>>Another thing is, that the servlets dont get refreshed when they are 
>>>changed/deleted (I have reload in server.xml).. 
>>> 
>>> 
>>>Thanks 
>>> 
>>>jan 
>>> 
>>> 
>>> 
>>>>| 
>>>>|problem: 
>>>>|-------- 
>>>>| 
>>>>|database access is managed via a java class that is 
>>>>|instantiated and loaded into each clients session. every 
>>>>|PL/SQL function and/or SQL statement a client needs is 
>>>>|called in a method of this class. 
>>>>|first, a connection is opened, the statement is called, 
>>>>|all resultsets and statements are closed and finally the 
>>>>|connection is closed. (or returned back to the 
>>>>|connection pool if one is used) 
>>>>| 
>>>>|the driver we use is the Oracle jdbc ThinDriver (jdbc 
>>>>|driver type 4). after running the system for several 
>>>>|days, dead connections (and as we just discovered, tons 
>>>>|of open cursors) pile up. 
>>>>|eventually the webserver(tomcat) will crash or just 
>>>>|hang. 
>>>>|in the process, access gets slower and slower, there may 
>>>>|be delays of 3 minutes until the webserver (or, perhaps 
>>>>|the database, in which case the webserver would be just 
>>>>|waiting for the db..) gives a response. 
>>>>|after a restart of the webserver or the database or 
>>>>|both, the system runns very well again. 
>>>>| 
>>>>|we tried several connection pools. with these, there 
>>>>|were no delays for getting a connection, but the pool 
>>>>|threw timeoutExceptions (i.e. made it visible that there 
>>>>|are hanging connections) and after a few hundred 
>>>>|timeouts the VM crashed with a stack overflow. 
>>>>| 
>>>>|the connections seem to get killed/kill themselfes 
>>>>|randomly no matter which function was called. we log the 
>>>>|methods and statements that were called for the 
>>>>|connections that die, but there is just no pattern in 
>>>>|it. thats why we dont find the root of the problem.. is 
>>>>|it the driver ? the database ? the tomcat ? 
>>>>| 
>>>>| 
>>>>| 
>>>>|environment: 
>>>>|---------------- 
>>>>| 
>>>>|Web Server: 
>>>>|Suse Linux 7.2 
>>>>|Apache Webserver /w Tomcat JSP Engine 3.2 /w SSL 
>>>>| 
>>>>|DB Server: 
>>>>|Suse Linux 7.2 
>>>>|Oracle 8i 8.1.7 /w multi-threaded server (MTS) 
>>>>| 
>>>>|Backup Server: 
>>>>|Oracle Shadow server 
>>>>| 
>>>>| 
>>>>| 
>>>>|things done: 
>>>>|------------ 
>>>>| 
>>>>|- linux update 
>>>>|- apache update 
>>>>|- tomcat update 
>>>>|- jdbc thin driver update 
>>>>|(everywhere the newest version) 
>>>>|- tried several connection pools 
>>>>| 
>>>>| 
>>>>| 
>>>>|possible changes: 
>>>>|----------------- 
>>>>| 
>>>>|- use OCI drivers instead of thin driver 
>>>>|- use J-serv with GNU_jsp instead of tomcat 
>>>>| 
>>>>| 
>>>>| 
>>>>| 
>>>>|every help is welcome, we're frustrated to no ends. 
>>>>|thanks very much in advance!! 
>>>>| 
>>>>|l.s├Ągesser 
>>>>| 
>>> 
>>>  __________________________________ 
>>>      ][ +1 4277 38438  
>>>        ][ +1 4277 38449  
>>>Dep. of Informationssystems ][ jan@ifs.univie.ac.at 
>>>                 ][ http://www.ifs.univie.ac.at/~jan 
>>>____________________________][__________________________________ 
>>> 
>>>    
>
>


Mime
View raw message