Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1293
  • Last Modified:

Servlet threads hang after till the execution of a Stored Procedure

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
p_vishnu7
Asked:
p_vishnu7
  • 2
1 Solution
 
amodmulayCommented:
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
 
p_vishnu7Author Commented:
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
 
p_vishnu7Author Commented:
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
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now