Hi all,
I've a huge problem with my servers hanging because of a jdbc deadlock.
I'm using the connectionpool.java by Karl Moss (I can provide the code if necessary). In the ModelDataDB.java, I make sure:
1. The preparedstatement is closed before closing the Connection Object.
2. I use a new preparedstatement Object in each method. In the finalize block, I make sure I close the preparedStatement always.
Here's the thread dump that shows the deadlock:
Found one Java-level deadlock:
=============================
"jcp-658":
waiting to lock monitor 0x985b1c (object 0xda13030, a oracle.jdbc.driver.OraclePreparedStatement),
which is held by "jcp-604"
"jcp-604":
waiting to lock monitor 0x985adc (object 0xda0b7d0, a oracle.jdbc.driver.OracleConnection),
which is held by "jcp-658"
Java stack information for the threads listed above:
===================================================
"jcp-658":
at oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatement.java:236)
- waiting to lock <0DA13030> (a oracle.jdbc.driver.OraclePreparedStatement)
at oracle.jdbc.driver.OracleConnection.close_statements(OracleConnection.java:1141)
- locked <0DA0B7D0> (a oracle.jdbc.driver.OracleConnection)
at oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:537)
- locked <0DA0B7D0> (a oracle.jdbc.driver.OracleConnection)
at com.boatventures.onlinebrochure.db.ConnectionPool.close(ConnectionPool.java:314)
at com.boatventures.onlinebrochure.db.ConnectionPool.removeFromPool(ConnectionPool.java:294)
- locked <053D08E0> (a com.boatventures.onlinebrochure.db.ConnectionPool)
at com.boatventures.onlinebrochure.db.ConnectionPool.close(ConnectionPool.java:252)
- locked <053D08E0> (a com.boatventures.onlinebrochure.db.ConnectionPool)
at com.boatventures.onlinebrochure.backend.ModelDataDB.getDealerParent(ModelDataDB.java:1422)
at com.boatventures.onlinebrochure.servlet.ModelDataServlet.getDealerParent(ModelDataServlet.java:237)
at jrun__index2ejspa._jspService(jrun__index2ejspa.java:159)
at allaire.jrun.jsp.HttpJSPServlet.service(HttpJSPServlet.java:39)
at allaire.jrun.jsp.JSPServlet.service(JSPServlet.java:228)
at allaire.jrun.jsp.JSPServlet.service(JSPServlet.java:196)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1417)
at allaire.jrun.session.JRunSessionService.service(JRunSessionService.java:1088)
at allaire.jrun.servlet.JRunSE.runServlet(JRunSE.java:1271)
at allaire.jrun.servlet.JRunRequestDispatcher.forward(JRunRequestDispatcher.java:89)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1557)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1547)
at allaire.jrun.servlet.JvmContext.dispatch(JvmContext.java:364)
at allaire.jrun.jrpp.ProxyEndpoint.run(ProxyEndpoint.java:388)
at allaire.jrun.ThreadPool.run(ThreadPool.java:272)
at allaire.jrun.WorkerThread.run(WorkerThread.java:75)
"jcp-604":
at oracle.jdbc.driver.OracleConnection.needLine(OracleConnection.java:1150)
- waiting to lock <0DA0B7D0> (a oracle.jdbc.driver.OracleConnection)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1558)
at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1758)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1805)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:320)
- locked <0DA13030> (a oracle.jdbc.driver.OraclePreparedStatement)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:278)
- locked <0DA13030> (a oracle.jdbc.driver.OraclePreparedStatement)
at com.boatventures.onlinebrochure.backend.ModelDataDB.getReviews(ModelDataDB.java:1823)
at com.boatventures.onlinebrochure.servlet.ModelDataServlet.getReviews(ModelDataServlet.java:530)
at jrun__cgi2dbin__OnlineBrochure__jsp__xml_brochure2ejsp2c.getNavigation(jrun__cgi2dbin__OnlineBrochure__jsp__xml_brochure2ejsp2c.java:363)
at jrun__cgi2dbin__OnlineBrochure__jsp__xml_brochure2ejsp2c._jspService(jrun__cgi2dbin__OnlineBrochure__jsp__xml_brochure2ejsp2c.java:319)
at allaire.jrun.jsp.HttpJSPServlet.service(HttpJSPServlet.java:39)
at allaire.jrun.jsp.JSPServlet.service(JSPServlet.java:228)
at allaire.jrun.jsp.JSPServlet.service(JSPServlet.java:196)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1417)
at allaire.jrun.session.JRunSessionService.service(JRunSessionService.java:1088)
at allaire.jrun.servlet.JRunSE.runServlet(JRunSE.java:1271)
at allaire.jrun.servlet.JRunRequestDispatcher.forward(JRunRequestDispatcher.java:89)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1557)
at allaire.jrun.servlet.JRunSE.service(JRunSE.java:1547)
at allaire.jrun.servlet.JvmContext.dispatch(JvmContext.java:364)
at allaire.jrun.jrpp.ProxyEndpoint.run(ProxyEndpoint.java:388)
at allaire.jrun.ThreadPool.run(ThreadPool.java:272)
at allaire.jrun.WorkerThread.run(WorkerThread.java:75)
===================================================================
Here's the code that uses the connectionpool:
package com.boatventures.onlinebrochure.backend;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.*;
//import com.boatventures.onlinebrochure.db.DBConnection;
import com.boatventures.onlinebrochure.db.ConnectionPool;
/**
* ModelDataDB class id used for querying the database.
*/
public class ModelDataDB {
//DBConnection dbConnection = new DBConnection();
// Our connection pool.
ConnectionPool m_connectionPool;
public ModelDataDB() {
System.out.println("ModelDataDB Servlet Started");
init();
}
/**
* <p>Initialize the servlet. This is called once when the
* servlet is loaded. It is guaranteed to complete before any
* requests are made to the servlet
*
* @param cfg Servlet configuration information
*/
public void init()
{
// Create our connection pool
m_connectionPool = new com.boatventures.onlinebrochure.db.ConnectionPool();
// Initialize the connection pool. This will start all
// of the connections as specified in the connection
// pool configuration file
try {
m_connectionPool.initialize();
}
catch (Exception ex) {
// Convert the exception
ex.printStackTrace();
}
}
/**
* <p>Destroy the servlet. This is called once when the servlet
* is unloaded.
*/
public void destroy()
{
// Tear down our connection pool if it was created
if (m_connectionPool != null) {
m_connectionPool.destroy();
}
}
/**
* Gets the Parent of a dealer from the database
* @param dealer dealer whose parent is to be returned
*/
public Vector getDealerParent(String dealerID){
/* String sql = "SELECT bvdea_bvdea_id from dealer_inv_share_rule where bvdea_bvdea_id_giving_dealer="+dealerID;*/
String sql = "select level, bvdea_bvdea_id_owned_by_dealer "+
"from bv_dealer_system_map "+
"start with bvdea_bvdea_id = "+ dealerID+
" connect by prior bvdea_bvdea_id_owned_by_dealer = bvdea_bvdea_id "+
"order by level";
Vector parentDealerIDs = new Vector();
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs = null;
try {
conn=m_connectionPool.getConnection();
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next()) {
parentDealerIDs.add(rs.getString(2));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("BoatDataDB.getDealerParent Oracle Error:" + e.getMessage() );
}
finally{
try{
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
m_connectionPool.close(conn);
}
catch (Exception e){
System.out.println(e);
}
}
return parentDealerIDs;
}
/** gets the links for third party testimonials (Reviews)
* @param modelid modelid
* returns Vector of Review objects
*/
public Vector getReviews(int modelid){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rset = null;
Vector reviews=new Vector();
try{
conn=m_connectionPool.getConnection();
stmt = conn.prepareStatement("select tst_id,link_title,link_href from "+
"testimonial pmt "+
"where pmt.PM_PM_ID=:1 "+
"and pmt.STATUS_INDICATOR='A'");
stmt.setInt(1, modelid);
rset=stmt.executeQuery();
while(rset.next()){
Review review = new Review();
review.tst_id = rset.getInt("tst_id");
review.title=rset.getString("link_title");
review.href=rset.getString("link_href");
reviews.addElement(review);
}
return reviews;
}catch(Exception e){
System.out.println("ModelDataDB.getReviews() exception occued for modelid: "+modelid);
e.printStackTrace();
return null;
}finally{
try{
if(rset!=null)
rset.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
m_connectionPool.close(conn);
}catch(Exception ex){
System.out.println("ModelDataDB.getReviews() fianlly: exception occued for modelid: "+modelid);
ex.printStackTrace();
}
}
}
I read in another query that this could be a bug in Oracle 8.1.x. Could someone please help me know what could be wrong here.....The deadlock occurs once in a day or once in 2 days.
We are using JRun 3.1 and Oracle 8.1.x
Thanks,