Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Servlet threads hang after till the execution of a Stored Procedure

Posted on 2008-10-15
5
1,201 Views
Last Modified: 2013-12-07
Hi Experts,
Am doing an Export DataPump procedure call from Java Servlet which executes the PL SQL spanning a new Thread. After this thread starts up, until it finishes executing the StoredProcedure, the servlet doesnot accept any calls. When i click on any links from my jsp, it just hangs until the Thread finishes completion of the Export Operation.

I checked if its a limitation of Oracle, by executing the Stored Procedure from command prompt and trying to access different links from jsp. It works perfectly fine. The issue is only when calling the StoredProcedure from java.

The below are the threads i tried but found no help.
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4193059
https://support.bea.com/application_content/product_portlets/support_patterns/wls/JDBCCausesServerHangPattern.html
http://forums.oracle.com/forums/thread.jspa?threadID=162390

I have implemented Connection Pooling. Iam running Tomcat as the server and ORacle as the database. Both Tomcat 6.0 and Oracle 11i are in the same windows box. It works fine for other queries and procedures except the Export and Import Procedures which takes a longer time.

Iam using the latest ojdbc5.jar as jdbc driver.
Please help on this issue.
public void run() {
	  startExport();
  }
 
private synchronized void startExport(){
 
// forming the query
// getting connection
 
java.sql.CallableStatement cs;
cs = con.prepareCall(query);
System.out.println(" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ START ARCHIVE ");
           cs.execute();
System.out.println(" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ END ARCHIVE ");

Open in new window

0
Comment
Question by:p_vishnu7
  • 2
5 Comments
 
LVL 3

Expert Comment

by:amodmulay
ID: 22729029
from the main thread of your business class
if you are calling this thread then this should work.....
Do you want it as a backend process?
Do you want to show the ststus of the execution on the status bar?
Export import take long time as the amount of data is huge. and the time required has nothing to do with connection pooling
0
 

Author Comment

by:p_vishnu7
ID: 22752273
Thanks for your reply.
Yes, am calling from a main thread. As you had said, i want to run it as a background process(Thread) as Export and Import DataPump will take more time. For now iam not displaying the progress. Over time i want to do it. But what i want to achieve is, when the Export is started, i want this to go as a background process meanwhile the servlet should allow the user to click on other pages in the application. But even this does not happen. When i put some trace statements, i could see the thread calls the execute method of the DataPump and hangs there. When i click on other pages any lines of code before there is an execute statements (rather any database calls) prints and waits before trying to run the execute statement. I strongly feel the socket between the Tomcat (or what so ever) to Oracle connection puts a lock so that any other concurrent database operations are not allowed. Please correct me if am wrong.
I appreciate your time to answer this issue. Thanks.
0
 

Author Comment

by:p_vishnu7
ID: 22947451
Hi,
Thanks to amodmulay: for effort made to answer this question.  We were using the book example (or sun java example) of Connection Pooling, please see site:- http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html

The book example of public synchronized void reapConnections() has the below code
 if((conn.inUse()) && (stale >conn.getLastUse()) &&  (!conn.validate())) {
      removeConnection(conn);

but in our code it was changed to

if (!conn.validate() || stale > conn.getLastUse()){
  removeConnection(conn);
}

Changing back to the Sun's code solved this issue.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22971016
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question