Solved

Servlet threads hang after till the execution of a Stored Procedure

Posted on 2008-10-15
5
1,159 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now