?
Solved

Servlet threads hang after till the execution of a Stored Procedure

Posted on 2008-10-15
5
Medium Priority
?
1,237 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
[X]
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
  • 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 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